Slowness to show few records

Hi,
this code:

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.

Can I optimize it?

You can optimize it by not recreating and repopulating the values() array for the columns in each loop iteration. You only need to do it once.

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)

I tried:

        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

but it’s the same.

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.

Hope this helps.

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

Nothing.

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.

Setting visible to false seems that nothing is changed, but setting scrollbars to false, the performance is increased.
So I do:

  lb_data.ScrollBarVertical = False
  lb_data.ScrollBarHorizontal = false

and at the end I set this two properties to True.

Now the app spends 2 seconds to display 2000 records and 6 seconds to display 20000 records.

Can we improve more?

How long does the SQLSelect take? Did you try using the profiler?

SQLSelect is immediate.
About profiler: I don’t know what do you mean

  1. Under BUILD SETTINGS > Shared… set Profile Code to ON.
  2. Now run your program.

When you exit the program, you’ll get a summary of the processing times of your method calls.

This the results:

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).

2,000 or 20,000 records?

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.

Following the link: Download

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.

https://sourceforge.net/projects/litex/