Hello Everyone,
I am writing code for an application, which used WPF for Front end, SQL for backend and EF for DAL.
This application has got lot of data, around millions of records are stored in DB. My DB structure is as given below:
Eg:
Col1 Col2 Col3 Col4 Col5 (JSON data is stored in string format)
A11 B11 C11 D11 [{"PPWK":"201601","Vol":10},{"PPWK":"201602","Vol":11}, {"PPWK":"201603","Vol":23}]
A11 B11 C11 D12 [{"PPWK":"201601","Vol":20},{"PPWK":"201602","Vol":22},{"PPWK":"201603","Vol":24}]
A11 B11 C11 D13 [{"PPWK":"201601","Vol":30},{"PPWK":"201602","Vol":33},{"PPWK":"201603","Vol":25}]
A12 B11 C12 D11 [{"PPWK":"201601","Vol":40},{"PPWK":"201602","Vol":44},{"PPWK":"201603","Vol":26}]
A12 B12 C12 D11 [{"PPWK":"201601","Vol":50},{"PPWK":"201602","Vol":55},{"PPWK":"201603","Vol":27}]
If table contains data as shown above. Collection of first four columns together becomes a unique key and column 5 contains data in JSON format which is declared as nvarchar(max) in table creation.
I want to pull this data into my WPF application and show the result as below.
(each unique PPWK should become a column and its correspondance value (Vol) should appear on the respective row.
Expected output in WPF datagrid:
Col1 Col2 Col3 Col4 201601 201602 201603
A11 B11 C11 D11 10 11 23
A11 B11 C11 D12 20 22 24
A11 B11 C11 D13 30 33 25
A12 B11 C12 D11 40 44 26
A12 B12 C12 D11 50 55 27
currently, my table has 11000 rows and in col5 it has got 256 set of values in each row (PPWK and Vol).
For the above requirement I have written a small application using WPF C#, EF(LINQ), SQL.
It takes around 9 secs to pull data into DataGrid in Windows application and 19 secs in WPF application.
I will display the code that I have used to display the data. Please someone suggest me to reduce the time taken to pull the data.
I am expecting the time taken for this operation should be less than one or two second.
please find the code that I have used for my application,
Classes I have used are given below:
public class Newtable
{
public string PPWK { get; set; }
public string Vol { get; set; }
}
public partial class TableName
{
public string Col1 { get; set; }
public string Col2 { get; set; }
public string Col3 { get; set; }
public string Col4 { get; set; }
public string Col5 { get; set; }
public IEnumerable <Newtable> Col5values { get; set; }
}
WPF code for getting the data:
private void Button_Click(object sender, RoutedEventArgs e)
{
ProjectRepository obj = new ProjectRepository();
gvLoadData.ItemsSource = obj.LoadDataToTable().DefaultView; //gvLoadData is a DataGrid name
}
Code written in DAL:
Repository.cs file
private DataTable LoadDataToTable()
{
var dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn("Col1", typeof(string)));
dataTable.Columns.Add(new DataColumn("Col2", typeof(string)));
dataTable.Columns.Add(new DataColumn("Col3", typeof(string)));
dataTable.Columns.Add(new DataColumn("Col4", typeof(string)));
var JSONPOCDataList = (from db in ob.TableName
select db);
//This loop is consuming 90% of the execution time.
foreach (var item in JSONPOCDataList.ToList<TableName>())
{
var dataRow = dataTable.NewRow();
dataRow["Col1"] = item.Col1;
dataRow["Col2"] = item.Col2;
dataRow["Col3"] = item.Col3;
dataRow["Col4"] = item.Col4;
IEnumerable<Newtable> deserializedProduct = Newtonsoft.Json.JsonConvert.DeserializeObject<IEnumerable<Newtable>>(item.Col5);
foreach (var jsonItem in deserializedProduct)
{
if (!dataTable.Columns.Contains(jsonItem.PPWK.ToString()))
{
dataTable.Columns.Add(new DataColumn(jsonItem.PPWK.ToString(), typeof(int)));
}
dataRow[jsonItem.PPWK.ToString()] = jsonItem.Vol;
}
dataTable.Rows.Add(dataRow);
}
return dataTable;
}
}
