Dim rs2 As RecordSet = App.db.SQLSelect("SELECT * FROM '" + table + "' ORDER BY '" + columns(0) +"' ASC")
If App.db.Error Then
MsgBox("Error: " + App.db.ErrorMessage)
else
if rs2 <> nil then
while not rs2.EOF
i = 0
Dim values() As String
while i <= columns.Ubound
values.Append(rs2.Field(columns(i)).StringValue)
i = i+1
wend
lb_data.AddRow(values)
rs2.MoveNext
wend
lbl_records.Text = Str(rs2.RecordCount) + " records loaded"
end if
End If
spend 6 seconds to display 2400 records (with 64 columns) in a listbox.
I verified that the slowness is not in query execution but in listbox population.
You could optimize by loading all the data in one step (lb_data.cell(-1,-1)=join(v,endofline)
where v() is an array built with values() joined with chr(9)
if rs2 <> nil then
Dim values() As String
Dim val_tmp() As String
while not rs2.EOF
i = 0
redim val_tmp(-1)
while i <= columns.Ubound
val_tmp.Append(rs2.Field(columns(i)).StringValue)
i = i+1
wend
values.Append(join(val_tmp, chr(9)))
rs2.MoveNext
wend
lb_data.cell(-1,-1) = join(values, endofline)
lbl_records.Text = Str(rs2.RecordCount) + " records loaded"
end if
Strange…
Cell(-1,-1) should perform better since you don’t force the list to update for every row
You should divide the operation in two step
FillData (where you read the data: till join(values,endofline)
FillList (where you fill the list with cell(-1,-1)
And profile the operation.
However the more data you will write in the list the slower it will perform
What’s the size of the join(values,endofline) string?
[quote=20022:@Antonio Rinaldi]
What’s the size of the join(values,endofline) string?[/quote]
About 517000 bytes (Obtained with LenB() function)
Tried:
if rs2 <> nil then
Dim values() As String
Dim val_tmp() As String
Dim columnsCount As Integer = columns.Ubound
while not rs2.EOF
i = 0
redim val_tmp(-1)
while i <= columnsCount
val_tmp.Append(rs2.Field(columns(i)).StringValue)
i = i+1
wend
values.Append(join(val_tmp, chr(9)))
rs2.MoveNext
wend
Dim t As string = join(values, endofline)
lb_data.cell(-1,-1) = t
lbl_records.Text = Str(rs2.RecordCount) + " records loaded"
end if
Nothing changed
You can also stop redimming your values array during each iteration since you will be replacing the values anyway. Instead redim the array once to the number of columns and simply assign the values to the correct index. This prevents a lot of resizing and appending to the array.
Currently you are taking your data that is split into columns and combining them into a new data structure (the string), so that you can pass it into the ListBox.AddRow method which will then split it out again. Try saving yourself some extra steps and just call AddRow then assign the values using ListBox.Cell and see if that is quicker. You are then just putting the data directly into the listbox without a conversion to an array, then to flat string, and back to an array again.
I’ve done a quick test: the number of rows and columns is the same and the total size is the almost the same (mine is a little bigger)
It’s fast in loading : faster with addRow, a little slower with cell and a little more slower with cells, but this can change based on what’s on cellBackgroundPaint and cellTextPaint
What make the app real slow is scrolling (or resizing) the listbox.
As I wrote profile! Your solution is quick enough even on a slow machine like mine. The trouble is that you are showing too much data (columns) for the listbox
[quote=20227:@Antonio Rinaldi]I’ve done a quick test: the number of rows and columns is the same and the total size is the almost the same (mine is a little bigger)
It’s fast in loading : faster with addRow, a little slower with cell and a little more slower with cells, but this can change based on what’s on cellBackgroundPaint and cellTextPaint
What make the app real slow is scrolling (or resizing) the listbox.
As I wrote profile! Your solution is quick enough even on a slow machine like mine. The trouble is that you are showing too much data (columns) for the listbox[/quote]
The trouble is that there are others apps (maybe developed in others languages) that make the same thing in less time.
So are you saying that is it a xojo limit?
[quote=20215:@Mike Bailey]You can also stop redimming your values array during each iteration since you will be replacing the values anyway. Instead redim the array once to the number of columns and simply assign the values to the correct index. This prevents a lot of resizing and appending to the array.
Currently you are taking your data that is split into columns and combining them into a new data structure (the string), so that you can pass it into the ListBox.AddRow method which will then split it out again. Try saving yourself some extra steps and just call AddRow then assign the values using ListBox.Cell and see if that is quicker. You are then just putting the data directly into the listbox without a conversion to an array, then to flat string, and back to an array again.
Hope this helps.[/quote]
Tried:
if rs2 <> nil then
Dim val_tmp(64) As String
while not rs2.EOF
i = 0
while i <= columnsCount
val_tmp(i) = rs2.Field(columns(i)).StringValue
i = i+1
wend
lb_data.AddRow()
lb_data.Cell(lb_data.LastIndex, -1) = join(val_tmp,chr(9))
rs2.MoveNext
wend
lbl_records.Text = Str(rs2.RecordCount) + " records loaded"
end if
Also try setting the visible property of the listbox to false before starting to fill the listbox and then to true once the listbox has been filled. I have filled much larger listboxes than what you are describing almost instantaneously doing this. When the visible property is false the process never attempts to do any updating of the display of the listbox as it is being filled which would slow things down. See if this doesn’t help your case.
Main Thread App.Close|1|12,00|00,00%
Main Thread home.lb.structure.CellBackgroundPaint|372|11,00|00,00%
Main Thread home.Open|1|247,00|00,00%
Main Thread home.Open->home.lb.structure.CellBackgroundPaint|168|7,00|00,00%
Main Thread home.Open->home.tables.CellBackgroundPaint|45|2,00|00,00%
Main Thread home.OpenDatabaseOpen|1|3784,00|00,01%
Main Thread home.tables.CellBackgroundPaint|125|2,00|00,00%
Main Thread home.tables.CellClick|1|20,00|00,00%
Main Thread home.tables.CellClick->home.refreshTable|1|19,00|00,00%
Main Thread home.tables.CellClick->home.refreshTable->home.displayListbox|2|0|00,00%
Main Thread home.tables.CellClick->home.refreshTable->w_loading.Open|1|2,00|00,00%
Main Thread home.timer.loading.Action|1|4815,00|00,01%
Main Thread home.timer.loading.Action->home.displayListbox|1|6,00|00,00%
home.timer.loading.Action and home.OpenDatabaseOpen are the methods that spends more time.
In home.timer.loading.Action there is the code that I posted. (timer is been added today, for others reasons, so is not the problem).
May I suggest you modify your code to load much smaller pages (200 records perhaps), and page them in only if the user actually goes there? Studies have shown that a user RARELY examines more that a few dozen records in the result of an online query, so why waste time loading 20,000?
I work for a HealthCare company and we have a web system (no written in XOJO) that allows a user to query a database containing 6 million providers, and any query could return from 0 to all 6 million… So it was designed with paging behind the scenes. The user sees a “listbox” and scrollbar… but when they scroll to a certain point, an new “page” of 100 records is cached.
Yes the code is more complex… but the speed problem vanishes
This is kind of the native way of some other frameworks. Server side recordsets just bringing to the client side portions being manipulated, but this job is transparent for the developer.
I noticed that on mac is faster than windows. Also the UI responds more quickly.
I give you a windows version with a test db (called “db”). If you want, try to open db and view tables called “articoli” and “prz_alt”, so you can see if the app works normally.
I put the project on sourceforge.
This is my first public xojo job, so I would be glad if anyone would download the source and give me any suggestion to improve my xojo code.