With your last suggestion I was able to speed up the sorting for dates, I also understood how RowComparision works… something I didn’t understand before, and I also understood how to best use PaintCellText. Now I have another problem related to the speed in opening the window. I have this window with a listbox where the data related to the complete invoice are loaded, which however is broken down into two tables (IntestazioneFatturaAcquisti and CorpoFatturaAcquisti). The code is the following:
Var rows As RowSet
Var rows2 As RowSet
Dim StringaConnessione as string
rows=nil
rows2=nil
'If rows <> Nil Then
ListBoxRicerca.HasHeader = True
ListBoxRicerca.HeaderAt(0) = "ID Mittente"
ListBoxRicerca.HeaderAt(1) = "ID CorpoF."
ListBoxRicerca.HeaderAt(2) = "Mittente"
ListBoxRicerca.HeaderAt(3) = "Data Spedizione"
ListBoxRicerca.HeaderAt(4) = "Numero Fattura"
ListBoxRicerca.HeaderAt(5) = "Articolo"
ListBoxRicerca.ColumnCount=6
ListBoxRicerca.ColumnWidths="60,60,150,150,90,200"
if PopupMenu1.RowCount<=0 then
rows2 = db.SelectSQL("SELECT Ditta FROM IntestazioneFatturaAcquisti GROUP BY Ditta order by Ditta")
While Not Rows2.AfterLastRow
PopupMenu1.AddRow(rows2.Column("Ditta").StringValue)
rows2.MoveToNextRow
Wend
rows2.Close
end if
Try
if PopupMenu1.SelectedRowValue="" then ' NAME of DITTA
rows2 = db.SelectSQL("SELECT * FROM IntestazioneFatturaAcquisti")
else
rows2 = db.SelectSQL("SELECT * FROM IntestazioneFatturaAcquisti Where DITTA='"+PopupMenu1.SelectedRowValue +"'")
end if
While Not Rows2.AfterLastRow
StringaConnessione="SELECT * FROM CorpoFatturaAcquisti Where ID_IntestazioneFattura="+rows2.column("ID").StringValue+" and Materiale_Spedito like '%" +Campo1 +"%' and Materiale_Spedito like '%" +Campo2 +"%' order by Materiale_Spedito"
rows = db.SelectSQL(StringaConnessione)
While Not Rows.AfterLastRow
ListBoxRicerca.AddRow(rows2.Column("ID").StringValue,rows.Column("ID").StringValue,rows2.Column("Ditta").StringValue.ConvertEncoding(Encodings.WindowsANSI),rows2.Column("Data").DateTimeValue.SQLDate,rows2.Column("NumeroFattura").StringValue, rows.Column("Materiale_Spedito").StringValue.ConvertEncoding(Encodings.WindowsANSI))
rows.MoveToNextRow
wend
rows2.MoveToNextRow
Wend
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try
me.ListBoxRicerca.Visible=True
Since from my point of view I managed to do the best I could, I wanted to understand if there is a way to speed up the loading of the listbox, because the window takes about 3 seconds to open.
First things first. Generally, you should avoid running computationally/time-intensive tasks in the Open Event.
For example, by putting these tasks in a timer that is executed XXX milliseconds after the window is opened.
In such a timer, you can then, for example, inform the user by using a visual signal that the data is being prepared, then the data is delivered and finally controls, that are used by the user and interact with the data, are activated.
Once these steps have been implemented, start optimizing data preparation.
does it take time to run the select statement, or time to fill the listbox?
if it is time to run the select statement, you might do that in the app opening event, so that the database is ready to deliver data as soon as a window opens.
Filling the list box: well, I always advise against loading thousands of rows, but people insist they must do it.
Turn off the scrollbars while filling the listbox,
and maybe even make the listbox invisible while it fills.
That stops superfluous painting from happening while rows are added.
Both NSTableview from the MBS plugin and DataView from Pidog are “data aware”. They load the SQL and then access only the data for the visible cells. I can easily show 100k records in an NSTableview.
I`d always prefer a lazy loading strategy (f.e. by using a timer like i described above). Because such things in the open event of any control slow down the start of your app or the opening of a window. This leads then to a bad user experience.
And if the data is retrieved much later in the program or there are long pauses between data retrievals, a timeout could occur.
Interesting.
Off topic: Workarounds like this tend to be used and left in forever, (by me) unless specific testing (by me) is done later to see if things get any better.
I probably have workarounds for issues going back to 2008 still in my code as I’ve
“got a workaround, moving on, forget about it”
NO PROBLEM !!! The important thing is to be able to solve the problem in one way or another For me everything you suggest is gold, to apply now or in some other project
I need to have a list of data in this listbox… I have to keep it. The user can then do a search based on 3 different fields, obtaining filtered results, and then click on the line of interest. I have the data loading when the window opens, do you suggest doing it later or at what point?
Exactly. Open the Window, avoid doing time wasting stuff in Open Events and instead start a timer while the Window opens. The timer waits for a few milliseconds (250 f.e.) and then it will start doing the time wasting stuff. This will lead to a fast presentation of the Window and the user will just have to wait a few milliseconds longer, but it just “feels better” because it does not look like the app “hangs” (if you also try to move time wasting and cpu intensive stuff into Threads, so that the UI of your App can stay responsive).
I have to leave my Computer now and hope i could help a bit.
The problem with this ListBox is that there are years of data storage, so there are many rows, about 3400 rows at the moment. I wanted to understand if there was a faster way to load this data. Following your advice I broke down the code into several parts, eliminated the superfluous code, eliminated * and set the right fields, but still the opening takes 2 seconds, it’s not much, but I’d like to understand if I can improve. From what I understand (tell me if I’m wrong) .AddAllRows would avoid me doing the cycle While Not Rows2.AfterLastRow ...... rows2.MoveToNextRow Wend
I do not know your Database Structure, but i’m pretty sure you could combine the 2 querrys into 1 and tho speed things up dramatically. Or in other words, your While…Wend method will for sure kill time.
And this is why you probably need to use the ‘data on demand’ listboxes referred to above.
But you can also trick things by …
Loading 100 rows to begin with, then using a timer on a short period to add the rest over the next few seconds, while the user thinks about what they want to do next.
or by getting the number of rows, adding that number of blank rows to the listbox, and using a timer to change each row into ‘real’ data over the next few seconds.
eg - (assuming listbox1 only had one column)
this pseudocode is written straight into the forum, not tested in a real app.
It is an idea, not working code
for x as integer = 1 to 3400
listbox1.addrow ""
next
followed by a timer which operates on a dataset which is a property of the window, not one that only exists in an event
Each time the timer fires, it will change perhaps 5 rows of the listbox to real data, until the end of the recordset.
suggested timer code… thedb would be a recordset property of the window
dim c as integer
while not thedb.AfterLastRow and c < 4
listbox1.celltextat(recordnumber,0) = valuefromdb
//thedb.movetonextrow
wend
if thedb.AfterLastRow then me.enabled = false
Now, I should say that these suggestions are scraping the barrel a little. They are techniques I have used in the past for speed, but the ‘better way’ as @Beatrix_Willius says, is to consider NSTableview from the MBS plugin and DataView.
Before he invests money just to speed things up, he should really try to squeeze out more speed from his code. It looks inefficient and here is potential to learn from. Once he hit’s a wall, he can still throw more money at it…
I am assuming your database is SQLite, is that so?
3400 rows is nothing. I load 20k rows into a listbox also using while/wend and it takes much less than 1 second. However, all I do in the while/wend is to create an empty row and then add a rowtag and two cell tags to each new row. The real work is done in the PaintCellText event, which uses the rowtag and celltags to decide how to show the data in each row. And as I said before, if I click on a litsbox header to sort on that column, I reload the entire listbox and let SQLite do the sorting. On macOS/Linux that is all fast, but is quite slow under Windows.
Edit: so I suspect yours is slow because you write something into each row, and the framework has to render that each time (or something). If you put the data into a rowtag then you can let the PaintCellText event paint ONLY the rows that are needed.
Best way to do that is to make a class (myClass, see below) with a property for each column. Then your while/wend becomes like:
Var row as Integer, rd as myClass
While Not Rows2.AfterLastRow
ListBoxRicerca.AddRow ("")
row = ListBoxRicerca.LastAddedRowIndex
rd = new myClass
rd.col1 = Rows2.Column("Col1").StringValue // replace col1 with name of first column etc
rd.col2 = Rows2.Column("Col2").StringValue
// etc for all columns
ListBoxRicerca.RowTagAt(row) = rd
rows2.MoveToNextRow
wend
.
Then you need to write the PaintCellText event handler to get the data from the rowtag and write into each column.
You can speed things up a bit by avoiding to access the RowSet columns over and over again, but instead use Strings to “cache” the values.
While Not Rows2.AfterLastRow
Var idIntestazione As String = Rows2.Column("ID").StringValue
Var ditta As String = Rows2.Column("Ditta").StringValue.ConvertEncoding(Encodings.WindowsANSI)
Var data As String = Rows2.Column("Data").DateTimeValue.SQLDate
Var numeroFattura As String = Rows2.Column("NumeroFattura").StringValue
Var sql As String = "SELECT * FROM CorpoFatturaAcquisti WHERE ID_IntestazioneFattura = ? AND Materiale_Spedito LIKE ? AND Materiale_Spedito LIKE ? ORDER BY Materiale_Spedito"
Var rows As RowSet = db.SelectSQL(sql, idIntestazione, "%" + Campo1 + "%", "%" + Campo2 + "%")
While Not Rows.AfterLastRow
ListBoxRicerca.AddRow(
idIntestazione,
Rows.Column("ID").StringValue,
ditta,
data,
numeroFattura,
Rows.Column("Materiale_Spedito").StringValue.ConvertEncoding(Encodings.WindowsANSI)
)
Rows.MoveToNextRow
Wend
Rows2.MoveToNextRow
Wend
I agree I have a lot to learn and I am sure that my code is not optimized. That’s why I always ask how to improve it. When I am sure that I write a good code, then, I can choose a paid plugin
