Handling Large Data Results.

Databases
1

I’m retrieving data from a local SQLite db. Some of my queries can return several thousand rows. The data is then written into a textarea. I have a quad core computer and it even has a hard time handling this much data being returned. What are some strategies I can try for my app to be able to handle several thousand rows of results without the computer freezing until all the data is returned? Thanks!

Here is my sql code:

[code]
Dim dbFile as FolderItem
Dim db as SQLiteDatabase

Dim strLog As String

db=New SQLiteDatabase
dbFile = getDB
db.DatabaseFile=dbFile

db.encryptionKey = cEncryptionKey

If db.Connect() then
Dim rs as RecordSet
rs = db.SQLSelect( "Select strLog " +_
"From tLog " +_
“Where strLogType = '” + strLogSel + “’ and ID > '” + strLastID + “’”)

while not rs.eof 
  
  strLog = rs.Field("strLog").StringValue
  
  ShowLog(strLog)  //This writes the data returned to a textarea
  
  rs.moveNext// move to next record
wend
rs.close// close the recordset

else
if isDebug then
msgBox "The database couldn’t be opened. " + CurrentMethodName
end
writeLog "The database couldn’t be opened. " + CurrentMethodName
end if[/code]

2

We do data paging where we only bring back a limited number of rows using the SQL keywords LIMIT and OFFSET.

We have a training video (with source code) available to subscribers at http://xojo.bkeeney.com/XojoTraining/ along with 200 other Xojo topics. This topic covers data paging for both desktop and web applications.

3
  1. Don’t do that - :slight_smile:
  2. grab it in smaller chunks (say in a timer that runs the query with LIMIT clauses) so the UI stays responsive while data is fetched
  3. dont write it on each fetch of one row to the text area - grab it into an array of lines then move those to the text area after retrieving them all
4

@Norman Palardy Thanks! Would there be any advantage to using a thread in this process somewhere?

5

Where exactly is the bottleneck? For example, if you remove the ShowLog call, does it still lock up in a way you’d notice?

6

I believe it is as Norman said writing it to the textarea on each row fetch is causing the issue.

7

Is there a way I can count the number of rows that will be returned without having to execute two separate queries?

8

I missed that. If the time to fetch the data is acceptable, then consider this strategy:

  • Replace the TextArea with a Listbox.
  • Clear the Listbox and fetch the new RecordSet.
  • Store the RecordSet in a property, then start a Timer whose period is something low, like 20 ms.
  • The Timer.Action will record Ticks when it starts, then cycle through the RecordSet to add rows to the Listbox. If EOF is reached, the RecordSet is nilled and the Timer turns itself off. Otherwise, the Timer will run for no more than 3 Ticks and exit. The next iteration of the the Timer will continue where it left off.
9 
select count(*) from mytable
10

Thanks @Kem Tekinay I’ll also give that some thought. Much appreciated.

11

@Jean-Yves Pochez Thanks but that would require an additional query.

12

Since it’s SQLite, you can use RecordSet.RecordCount.

13

@Kem Tekinay Thanks!

14

@Norman Palardy @Kem Tekinay thank you both I wish I could select both your posts as answers! :slight_smile:

15

If you have MBS SQL this supports thread-aware SQL SELECT and EXECUTE commands. This way the user can move around their app while the list is being populated. BUT I would use the OFFSET in preference with very large RecordSets, using Previous/Next buttons.

16

I use a listbox that is the size of the window
and then use the LIMIT and OFFSET of the sqlselect to get only the visible rows
that way a distant postgres database with thousands of records is displayed very smoothly with quite no lag
even on a slow adsl link.

edit: this works nice ONCE debugged … :wink: