如何更快地从SQL到WPF数据网格获取JSON数据？ [英] How to get JSON data from SQL to WPF datagrid faster?

问题描述

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.

< b>我尝试了什么：

What I have tried:

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

推荐答案

不要一次尝试拉11,000行数据。查看分页，一次返回100行。考虑一下您的用户他们如何一次看到，使用和处理11,000行数据。

Don't try pull 11,000 rows of data at once. Looking into paging, returning say 100 rows at a time. Think about your users how could they possibly see, use, and work with 11,000 rows of data at once.

这篇关于如何更快地从SQL到WPF数据网格获取JSON数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！