What's the trick to...
 
Notifications
Clear all

What's the trick to get 100,000 rows loaded quickly?

   RSS

0
Topic starter

I'm using a for loop in XOJO to get data from a rowSet obtained from sqlite.

This is the loop

For each row as DatabaseRow in rs
    ListboxPathToFiles.AddRow(row.ColumnAt(0).StringValue, Row.ColumnAt(1).StringValue)
Next

sql = Select col1, col2 from table1;  <-- has about 75,000 to 80,000 rows

Here are the timings

1:56:09 AM : Start query .. rs = App.p_db.SelectSQL(sql)  <-- Pushbutton start
                    End query .. rs = App.p_db.SelectSQL(sql)    <-- xojo returns set in less than a second

                    Start Query Records into an array .. For each row as DatabaseRow in rs  <-- same loop into array
                    End Query Records into an array .. For each row as DatabaseRow in rs    <-- both loops finish subsecond

                    Start loading records into piDog listbox .. For each row as DatabaseRow in rs  <-- same loop piDogLB  target
1:56:22 AM : End loading records into piDog listbox .. For each row as DatabaseRow in rs   <-- 13 seconds later (spinning beach ball)

The demo can load several hundred thousand in less than a second.  I must be doing something wrong.

 

 

 

haha 'I'm using a for loop in XOJO to DISPLAY data' not get data. sorry for the typo. I was up very late composing the question.

1 Answer
0

The simplest way to see a speed up would be to set ListboxPathToFiles.count to the rowCount of the rowSet. Then you can loop through the rows and use ListboxPathToFiles.cell to set each column value.

The less simple way would be to subclass dataSource and only get the database values as needed. However, that may be overkill for a local database.

I was able to get it down to about three to four seconds which is good enough for now. I'll revisit this again later on in the project. thank you.