Another speed problem

So, the database I currently use is Microsoft Access … I have a business and there are 20 years of data on the database. Now, during the Christmas period I wanted to migrate the data to a Sqlite database. I did some tests and I managed to migrate the data without data loss (apparently). I thought I would take advantage of the Christmas holidays to fix the sqlite database and convert my code to read the database. I noticed that I will have to do a lot of work regarding the way to manage the dates, but I managed to do it in an example, I think I will be able to do it on the whole program.
I understand what you say in creating a class … but it is a bit out of my reach. I know, I should study, but believe me between work, family, dog and various commitments, I can’t find time to dedicate. Now I tried to follow Sascha S’s suggestions, breaking down the code into 3 parts, it goes a little better. I would like to load the first 100 results on the listbox and then finish loading the others … I have to figure out how to do it. In the end, it’s just a matter of initial impact, which gives the user the impression of having to wait, then in the search it goes very well.

Thank you for not misunderstanding my comment. When I read my post again later, I was afraid that you might misunderstand it.

If I can give you another recommendation, then you should definitely look into JOINTs in SQL because even without knowing the structure of your database, your code strongly suggests that you can get the information you want using just one query. This means that you would only have to run through one While…Wend loop, which would speed up your code enormously.

Sascha S. I followed the advice you gave me before. Now I open the window and in the Load event I load only the PopUp. Then once the window is opened with a timer, I start loading the data. And I broke down the code … one part in the open event and the other in the timer … so I have the “illusion” that the data is loaded first … now I’m understanding how to load the first 100 results immediately as suggested by TimStreater and then load the rest to give the “illusion” of speed :slight_smile:

My actual code is:

if PopupMenu1.SelectedRowValue="" then  ' NAME of DITTA
  rows2 = db.SelectSQL("SELECT ID,DITTA,NumeroFattura,Data FROM IntestazioneFatturaAcquisti")
else
  rows2 = db.SelectSQL("SELECT ID,DITTA,NumeroFattura,Data FROM IntestazioneFatturaAcquisti Where DITTA='"+PopupMenu1.SelectedRowValue.ReplaceAll("'","''") +"'")
end if


While Not Rows2.AfterLastRow
  rows = db.SelectSQL("SELECT ID,Materiale_Spedito FROM CorpoFatturaAcquisti Where ID_IntestazioneFattura="+rows2.column("ID").StringValue+" and  Materiale_Spedito like '%" +Campo1 +"%' and Materiale_Spedito like '%" +Campo2 +"%' order by Materiale_Spedito")
  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

Yes I understand, but I don’t know how to do it, given that there are 2 tables in question, and that the data displayed depends on the IDs of the IntestazioneFatturaAcquisti table.

I don’t think that reducing 3400 entries to 100 would make a massive difference. For example, if I fill a list of 10,000 entries in my app from a mySQL database that I access via a tunnel in the company network, it only takes a few moments.

First concentrate on making the SQL query as efficient as possible and then on retrieving the data in the code as lean as possible.

@TimStreater tip of using CellTags and drawing the text yourself in the PaintCellText event is worth its weight in gold. I have a ListBox in which I display data with different font sizes and styles and I do it as Tim described. This ListBox is no slower than a ListBox that simply lists the text.

Could you provide an extract of your database (possibly with example data) as a .sql file here?


Do you understand? Above are the two tables that I use, on one (purchase invoice header) I store the name of the company with an ID (the same company can have different IDs, the name is always the same). The other table (CorpoFatturaAcquisti) displays all the data associated with the ID of the date ball IntestazioneFatturaAcquisti. So, to display a complete list I load the names of the companies with the relative ID, and for each of these I print the associated article. I think the speed depends on this cycle.

Since you are referencing an ID in the 1st table in the 2nd table (and that’s how you should do it) you can easily load entries from the 2nd table that reference an entry in the 1st table with just one query.

Here is a simplified example:

The INNER JOIN establishes the relation between both tables.

BTW: Image and querry created by using Valentina Studio.

Yes, I understand what you mean, but I don’t know how to write it in code. (That’s my problem). I’ll try to see what INNER JOIN is.

I’m a little short on time today. I hope the following hint will help you achieve your goal. :slight_smile:

Select ID, ...
  FROM CorpoFatturaAcquisti
  INNER Join CorpoFatturaAcquisti ON IntestazioneFatturaAcquisti.ID = CorpoFatturaAcquisti.ID
  Where ...

If you connect the two tables using a JOIN, you can retrieve the data with just one query.

OK my code is wrong !!! Not work. i don’t understand.

rows2 = db.SelectSQL("SELECT ID,Materiale_Spedito FROM CorpoFatturaAcquisti INNER JOIN CorpoFatturaAcquisti ON IntestazioneFatturaAcquisti Where CorpoFatturaAcqusti.ID_IntestazioneFattura=IntestazioneFatturaAcquisti.ID  and Materiale_Spedito like '%" +Campo1 +"%' and Materiale_Spedito like '%" +Campo2 +"%' order by Materiale_Spedito")
While Not Rows2.AfterLastRow
  ListBoxRicerca.AddRow(CorpoFatturaAcquisti.Column("ID").StringValue,IntestazioneFatturaAcquisti.Column("ID").StringValue,CorpoFatturaAcquisti.Column("Ditta").StringValue.ConvertEncoding(Encodings.WindowsANSI),CorpoFatturaAcquisti.Column("Data").DateTimeValue.SQLDate,CorpoFatturaAcquisti.Column("NumeroFattura").StringValue, IntestazioneFatturaAcquisti.Column("Materiale_Spedito").StringValue.ConvertEncoding(Encodings.WindowsANSI))
  rows2.MoveToNextRow
wend

I tried another approach, at least it works. But it’s still slow in loading data.
Meanwhile I discovered this thing about the INNER JOIN that at least simplifies the code.

Var rows As RowSet
 rows = db.SelectSQL("SELECT * FROM CorpoFatturaAcquisti INNER JOIN IntestazioneFatturaAcquisti ON CorpoFatturaAcquisti.ID_IntestazioneFattura = IntestazioneFatturaAcquisti.ID;")

For Each row As DatabaseRow In rows
  ListBoxRicerca.AddRow(rows.Column("ID").StringValue,rows.Column("ID").StringValue,rows.Column("Ditta").StringValue.ConvertEncoding(Encodings.WindowsANSI),rows.Column("Data").DateTimeValue.SQLDate,rows.Column("NumeroFattura").StringValue, rows.Column("Materiale_Spedito").StringValue.ConvertEncoding(Encodings.WindowsANSI))
Next

The two tables have an ID field, how do I distinguish them?
rows.Column(“ID”) ??

I smell a Design flaw here. :slight_smile:

  • Each company should have only one unique ID.
  • Each order should have a unique ID.
  • And each product should have a unique ID.
  • Then in a table, products with ID X can be entered for an order with ID X for company with ID X.

And when an order is to be listed, one would retrieve all products from the last table that were entered with order with ID X and/or company with ID X.

An example:

In your SELECT statement you can select Columns using a unique identifier.
Like so: SELECT ID As ID_Stuff FROM Stuff_Table As Table_with_Stuff

In fact it works like this … the displayed data (CorpoFatturaAcquisti) refers to an ID that identifies a single company in the IntestazioneFatturaAquisti table. So for each item of a given order, the ID belonging to the IntestazioneFatturaAcquisti table is unique. In the image I sent you can see that 4 arrows start from an ID, because that ID purchased 4 items. I always hope I’m not making a mistake and that I can make myself understood. I tried the code with Join IN, but it seems as fast as or worse than the one I wrote, is it possible? What is certain is that it is more streamlined.

Please excuse me for only scratching the surface at the moment. I’m in the office working on a project of my own so I can’t pay attention to the details and I’m only in the office for a short time before the Christmas holidays.

Perhaps you could save a copy of the database, empty the tables enter some fake Data and then post that file here so that I (or someone else in the forum) can create a query?

I believe, if you find a way to search only once by using the Company Name instead of the various Id’s of such Company, you can simplfy things.

BTW: I think this is a main problem here. :slight_smile:

This looks wrong, because it’s missing a parameter:
INNER JOIN <TABLE_TO_JOIN_IN> ON <COLUMN_OF_TABLE_TO_JOIN_IN> = <COLUM_WITH_IDENTICAL_IDENTIFIER_IN_SEARCHED_TABLE>

It should be somewhat like this:
INNER JOIN CorpoFatturaAcquisti ON IntestazioneFatturaAcquisti.ID = CorpoFatturaAcquisti.ID

Because i will leave the office soon and be back next year, i’d like to recommend trying Valentina Studio for building your querry. It has a Designer which can help you visually construct such querrys and you can use it even in the free version: Valentina Studio Free Version Database Management Software

Sasha, in fact I have a badly designed “IntestazioneFatturaAcquisti” table, so the COMPANY IDs are not unique … and everything is managed with the company name + invoice date + invoice number, in this way the data searched becomes “unique”, but in fact the search is very elaborate. I will try to follow your suggestions as soon as possible.

1 Like