The use of ACCESS database, query about 6 w records, then the query results are bound to the VSFLEXGRID, check out results only 30 of them
But time was unacceptable, please directly!
The code is as follows:
Dim tmpRS As New ADODB. You
SqlStr="SELECT * FROM [tbDATA] WHERE ID IN (SELECT MAX (ID) FROM [tbDATA] GROUP BY Process_No) ORDER BY ID ASC" 'time: 2219 ms
SqlStr="SELECT * FROM [tbDATA] WHERE ID IN (SELECT MAX (ID) FROM [tbDATA] GROUP BY Process_No)" 'time: 115 ms
SqlStr="SELECT * FROM [tbDATA] WHERE ID IN (SELECT MAX (ID) FROM [tbDATA] GROUP BY Process_No) ORDER BY ID, Process_No ASC" 'time: 2219 ms
SqlStr="SELECT * FROM [tbDATA] WHERE ID IN (SELECT MAX (ID) FROM [tbDATA] GROUP BY Process_No) ORDER BY Process_No ASC" 'time: 2729 ms
SqlStr="SELECT * INTO TempTB FROM [tbDATA] WHERE ID IN (SELECT MAX (ID) FROM [tbDATA] GROUP BY Process_No)" 'time: 2687 ms to the query results IN a temporary table first, and then sort binding
TmpRS. Open sqlStr tmpCN (ch), 1, 1
The Set VSFlexGrid3 (ch). The DataSource=tmpRS

The above code, quick query, binding is slow, slow query, binding is slow
Supplement: use ADO to query the VB environment, ACCESS database, ID primary key, there is an index, the other columns without index

CodePudding user response:

Quick query, binding is slow, slow query, binding is fast

CodePudding user response:

The create index on tbDATA XXX (Process_No, ID);

CodePudding user response:

SqlStr="the CREATE INDEX index_tt ON tbDATA (Process_No, ID)"
'tmpCN (ch). The Execute sqlStr
After set up the index, query takes again:
2253 ms, there is no effectThe recordset sorting before binding, binding soon, around 70 ms to complete the binding
But if the query without sorting, binding, will take more than 2000 ms