Some days ago, did a property charge system, CS mode, using the LINQ to SQL technology, this is my first time to use this Dongdong write program Access database, vaguely made a mess, there was a classmate they find a good paging component, and then write the call method, because the time is more urgent, and the first time, So there is no way to study directly according to the comments they write the pagination method, but the development process has always been the way they write doubts, will it be a kind of opportunistic, fabrications? Then I did some simple analysis, and I found that the program reads all the data from the database every time in the business logic layer, and then loops the data into a specific list, traversing the entire collection of data, then paging the list in the display layer and finally putting it into DataGridView. Where the column name is automatically set to the property value of the class, just at the beginning I also always thought that this method is good convenient ah, all of a sudden, until the program is nearing the end, I found that the operation of the program log record has reached 4,500, each time the log management interface, the program must load half to come out, I was completely skeptical of LINQ, rather than a question of LINQ, but a paging approach and procedural algorithms.
Pagination Code section of the display layer: Datagridview.datasource = data. Take (Pagercontrol1.pagesize * pagercontrol1.pageindex). Skip (Pagercontrol1.pagesize * (pagercontrol1.pageindex-1)). ToList (), where data is the business logic layer gets to the business logic layer: var data = from d in jx. logtable orderby d.OperTime descending where tflag.equals ("All") | | (d.opertime >= t1 && d. OPERTIME&NBSP;<=&NBSP;T2) && (d.opername.contains (content) &NBsp;| | d.userid.contains (content)) select d; List< Logtable> items = new list<logtable> (); foreach (Var d in data) { LogTable Item = new logtable (); item. Ref. = trim (d.id); &Nbsp; item. Event = trim (d.OperName); item. Operator = trim (D.userid); item. Flag = trim (D.mark); Item. Operating time = trim (D.opertime); items. ADD (item); } return items; here not only all the data are read out, but also traversed, when the data reached 4,500, there is a lag phenomenon, the situation is so bad, so ugly code ah, and the program involvedTo this place is a lot of deadly, if you want to change, it must be a disaster!
Having been busy before, having no time to test the problems that existed in the program and how to use LINQ to develop the application more efficiently, I took the time to do a little test today, which made me re interested in LINQ.
The database tables in the test still do not change, or the Operation log table, the difference is that I added more data in the table, the total number of records reached almost 1.1 million, and then the 1.1 million data to test. The specific tests are as follows:
When you click the total number of data, the total number of records of the database Operation log table, click Load Data, the 1.1 million data page, 10 per page, take the third page of 10 data, the point of emptying data, the data loaded in the table is emptied: The total number of entries is as follows: First run, click Load Data, A total of 9100 milliseconds: then click to erase the data, click to load the data again, a total of 2899 milliseconds: After repeated testing, the time has been kept at about 3000 milliseconds, that is 3 seconds, the second click after the discovery time significantly faster, 3 times times more than, but I want to say that the speed is very very slow, Why is it so slow because the data columns in the table store values that are small and have no big data? Look at the code:
Stopwatch SW = new Stopwatch (); Sw. Start (); Start timing var data = (from D in DB. Log Select D). ToList ();//query all data out, and ToList () Datagridview1.datasource = data. Skip (20). Take (10). ToList ();//Paging SW. Stop (); Timed out MessageBox.Show ("time-consuming:" +SW. Elapsedmilliseconds.tostring () + "MS");
Then I made a change to the code as follows:
Stopwatch SW = new Stopwatch (); Sw. Start (); Start timing var data = (from D in DB. Log select d);//Just write the query condition, note here Datagridview1.datasource = data. Skip (20). Take (10). ToList ();//Paging SW. Stop (); Timed out MessageBox.Show ("time-consuming:" +SW. Elapsedmilliseconds.tostring () + "MS");
In fact, just the top of the red place is deleted, that is ToList () part, the use of control variable method, all other data and conditions do not make any changes, see the effect:
It takes 185 milliseconds for the first click to load data: After emptying, the second click loads the data for a total of 39 milliseconds: After that, the test remains at around 40 milliseconds, why is the gap so big?
In fact, an important feature of the LINQ execution process is the lazy loading, which is calculated only when you know that you want to get the data. You may think that you are done with var data = from db. Log Select D statement, and then at the beginning of the Skip,take function paging, all values will be stored in data, in fact, this statement will be deferred to a foreach or ToList () call to execute. and var data = (from DB. Log Select D) Skip (20). Take (10); After the statement is executed, the program simply generates a perfect SQL statement waiting to be executed until ToList () or foreach appears, that is, waiting for the program to fetch the data before starting the database query, which explains why the gap is so big. It also shows that the efficiency of paging in LINQ is very good, and it takes about 200 milliseconds to page up to 1.1 million records, up to about 40 milliseconds.
Be sure to note: The first page in the acquisition, instead of first get paged! So, in order to test efficiency, I appended the data to 2.2 million, re-test: found the first query, a total of 195 milliseconds: After emptying, query again, a total of 37 milliseconds: done, after testing, in the LINQ page before paging can not call the Read method, should be paged after the query ... It seems that this project development can boldly continue to use LINQ to operate the database. Of course, before the property management system, and so busy this period of time, I have to help change, a person responsible AH!!!
No culture is really scary, so simple problem now only know!
Simple test LINQ to SQL performance
