If you query the SQL database with a general select *, then use the RecordSet to make a long process, and it will consume memory. You may feel that it is also fast than SQL with Access.
In fact, we can only take out the number of records that we need to display each page, this speed is amazing, very fast. Here we will use aggregation index to quickly determine the number of records we need to take. As:
IF P> 1 Then 'P is Page Pages
IF n = "next" the next page
SQL = "SELECT TOP 26 * from Song1 WHERE ID>" & PK & "And Contains (Songtitle, '" & SongName & ")" "' PK is the maximum ID of the current page
Elseif n = "prev" Then 'Previous "
SQL = "SELECT TOP 26 * from Song1 Where Id <" & Previd & "and Contain (Songtitle, '" & SongName & ") Order By ID DESC"' previd minimum ID
END IF
Else
SQL = "SELECT TOP 26 * from Song1 Where Contains (Songtitle, '" & SongName & ")" No specified Page Value, the default first page
END IF
Here is a full-text search, the speed is also very fast, I have been tested in 520,000, the fastest 46ms (machine C1.7. DDR 256M), I feel more, I use the LIKE code fuzzy query page to test, in the result There are many more cases more faster than full, but if the results are rare (only a few of the entire table) will be a long process, because the full table is to be scanned!
According to the above method, the results have a total record number. Here we can use Select Count (*) to get the total number of records, the speed page is still going, but it feels slow, especially the record set, but this is It is very small in memory.
The above is my recent SQL query experience.
