Как быстрее получить данные JSON из SQL в WPF datagrid?

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;
        }
    }