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

Question

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.

Jim · Answer

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.