You can speed up the code

federico · 20 December 2024 17:23

Good evening group, I’m trying to understand how to improve my code: At the moment in the database I have two tables, IntestazioneFatturaAcquesti and CorpoFatturaAcquesti, in the first there are some data connected to the second table. The second table refers to the first through an ID. So in my search I display the invoice data and based on the ID the reference headers are inserted. As if that were not enough I can filter from the various data, an article within the invoice through 2 fields (even a partial search) and based on the Company Name.

So I wrote the following code, which works, but I think it is very slow. Is there a way to improve it?

'I choose what to look for
var Scelta As Integer
scelta=0
if Campo1="" and campo2="" then
  scelta=0
end if
if Campo1<>"" and campo2="" then
  scelta=1
end if
if Campo1="" and campo2<>"" then
  scelta=2
end if
if Campo1<>"" and campo2<>"" then
  scelta=3
end if

select case Scelta
Case 0
  rows = db.SelectSQL("SELECT * FROM CorpoFatturaAcquisti")
Case 1
  rows = db.SelectSQL("SELECT * FROM CorpoFatturaAcquisti Where Materiale_Spedito like '%" + Campo1 +"%' order by Materiale_Spedito")
Case 2
  rows = db.SelectSQL("SELECT * FROM CorpoFatturaAcquisti Where Materiale_Spedito like '%" + Campo2 +"%' order by Materiale_Spedito")
Case 3
  rows = db.SelectSQL("SELECT * FROM CorpoFatturaAcquisti Where Materiale_Spedito like '%" +Campo1 +"%' and Materiale_Spedito like '%" +Campo2 +"%' order by Materiale_Spedito")
End Select






While Not Rows.AfterLastRow
  rows2 = db.SelectSQL("SELECT * FROM IntestazioneFatturaAcquisti Where ID="+ rows.Column("Id_IntestazioneFattura").StringValue)
  if PopupMenu1.SelectedRowValue="" then  ' NOT filter by company name
    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))
  else ' filter by company name
    if PopupMenu1.SelectedRowValue=rows2.Column("Ditta").StringValue then
      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))
    end if
  end if
  rows.MoveToNextRow
Wend
rows2.close
rows.Close

P.S:I know my code is very basic, but I like programming as a hobby

TomasJ · 20 December 2024 18:01

Firstly, some basic priciples:

Use Preperated Statements which will improve not only your speed but security aswell.
Don’t make joins within loops or mix two queries. If you need Joins in multiple tables, make proper SQL Statements and let do the work on Serverside.
Lastly I see you are searching for scelta=numeric value but you are using Strings. Normalize your data properly and use indices. Searches over strings takes longer than using numeric values

These are the three showstoppers in your little script.

Lastly the speed Xojo often depends, if a control is visible or not when you flood it with data. Make it invisible, fill it, and make it vissible when finished.

npalardy · 20 December 2024 18:30

when there is user input data 100%
if its a query generated entirely in your code (like this) I wouldnt say its a requirement

Prepared stmts will, in some db’s, be reusable so you can alter parameters to them and they wont need to be recompiled so you can gain some speed
But for a single shot usage there’s no appreciable difference in my experience (YMMV)

EDIT : a single query to get all the data in one query would probably speed things up
It MAY complicate loading it into the table but overall should be faster than looping through rows and performing additional queries per row

I’d expect something like

SELECT * 
FROM CorpoFatturaAcquisti, IntestazioneFatturaAcquisti
where IntestazioneFatturaAcquisti.ID = CorpoFatturaAcquisti.Id_IntestazioneFattura

Then add in the extra criteria from the SELECT CASE Scelta to filter the CorpoFatturaAcquisti table further

and

federico · 21 December 2024 09:01

I understand the concept, but I can’t apply it, I can get to the result by always writing a lot of code (from my point of view). I realize that. I would like to avoid writing a lot of code for once and have the same result. I attach the two tables in question. When the user does not filter anything, it must display all the invoices (line by line) of all the companies, when instead I choose to filter either by the name of the company that has an ID (PopupMenu1.SelectedRowValue) or with two text boxes to also partially select the object searched for (Materiale_Spedito LIKE ‘%" + Campo1+ "%’ … or … Materiale_Spedito LIKE ‘%" + Campo2 + "%’ … or Materiale_Spedito LIKE ‘%" + Campo1+ "%’ and Materiale_Spedito LIKE ‘%" + Campo2+ "%’)

Result example:

I tried to tidy up the code a bit, rewriting it better… basically if there are no filters, I display everything, if there are filters, it displays based on the company or partial search.

me.ListBoxRicerca.Visible=False
if PopupMenu1.SelectedRowValue="" then  ' NAME of DITTA
  'VISUALIZAZTION COMPLETE
  rows2 = db.SelectSQL("SELECT * FROM IntestazioneFatturaAcquisti")
  While Not Rows2.AfterLastRow
    rows = db.SelectSQL("SELECT * 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
else
  'VISUALIZATION WITH FILTERED
  rows2 = db.SelectSQL("SELECT * FROM IntestazioneFatturaAcquisti Where DITTA='"+PopupMenu1.SelectedRowValue +"'")
  While Not Rows2.AfterLastRow
    rows = db.SelectSQL("SELECT * 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
  rows2.Close
end if
ListBoxRicerca.Visible=True

JeffT · 21 December 2024 13:34

Maybe…


dim theQuery as string = "SELECT * FROM CorpoFatturaAcquisti where true "

if PopupMenu1.SelectedRowValue <> -1 then 
theQuery = theQuery + " and ID_IntestazioneFattura=" +rows2.column("ID").StringValue + " " 

end if

IF  trim(Campo2 + campo1) > ""  then
//filter on materials

theQuery = theQuery + " and ( Materiale_Spedito like '%" + +Campo1 + "%' and Materiale_Spedito like '%" +Campo2 +"%' order by Materiale_Spedito)"

//if Campo1 or Campo2 are empty, it still works...
end if


//now use theQuery to do the select

federico · 23 December 2024 06:52

JeffT:

dim theQuery as string = "SELECT * FROM CorpoFatturaAcquisti where true "

if PopupMenu1.SelectedRowValue <> -1 then 
theQuery = theQuery + " and ID_IntestazioneFattura=" +rows2.column("ID").StringValue + " " 

end if

IF  trim(Campo2 + campo1) > ""  then
//filter on materials

theQuery = theQuery + " and ( Materiale_Spedito like '%" + +Campo1 + "%' and Materiale_Spedito like '%" +Campo2 +"%' order by Materiale_Spedito)"

//if Campo1 or Campo2 are empty, it still works...
end if


//now use theQuery to do the select

I don’t understand the example… what should I replace with my code? Sorry, I know I’m very limited on the code. Yours seems much cleaner and more functional, but I don’t know where in the code to insert it.

federico · 23 December 2024 07:33

I tried this but everything freezes.

dim theQuery as string 
rows2 = db.SelectSQL("SELECT * FROM IntestazioneFatturaAcquisti")
While Not Rows2.AfterLastRow
  theQuery= "SELECT * FROM CorpoFatturaAcquisti where true "
  if PopupMenu1.SelectedRowValue <> "" then 
    theQuery = theQuery + " and ID_IntestazioneFattura=" +rows2.column("ID").StringValue + " " 
  end if
  IF  trim(Campo2 + campo1) > ""  then
    //filter on materials
    theQuery = theQuery + " and ( Materiale_Spedito like '%" + Campo1 + "%' and Materiale_Spedito like '%" +Campo2 +"%' order by Materiale_Spedito)"
    //if Campo1 or Campo2 are empty, it still works...
  end if
  //now use theQuery to do the select
  rows = db.SelectSQL(theQuery)
  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
rows.Close
rows2.Close

JeffT · 23 December 2024 18:32

I tried this but everything freezes.

Looks like there are two sets of While…wend
This is the kind of problem that arises when you just copy and paste into existing code

I cannot follow which table is which, or why you have two recordsets here.

I expect you to select ONE record from rows2 , then use this code to get associated records in some other table.
Maybe the selection value should be coming from PopupMenu1.SelectedRowValue ?

There is no sense in doing it over and over for every record in rows2

YOU must first understand how it builds the query, then swap the variable parts for the correct sources.

What it does:

1/ Create a select statement that returns all values (select * from table where true )
2/ if there is a selected ID, add that

select * from table where true and ID_IntestazioneFattura=6

3/ if there are any material filters, add these to the end of the query. (Note that LIKE ‘*’ gets all values)
by this point , the query could look like

select * from table where true and ID_IntestazioneFattura=6 and (Materiale_Spedito like 'ABC*' and Materiale_Spedito like '*' ) order by Materiale_Spedito

or this, if there was no ID

select * from table where true and (Materiale_Spedito like 'ABC*' and Materiale_Spedito like '*' ) order by Materiale_Spedito

this set of code will get you one recordset.
Check all the variables and ensure they are the correct ones


theQuery= "SELECT * FROM CorpoFatturaAcquisti where true "
  if PopupMenu1.SelectedRowValue <> "" then 
    theQuery = theQuery + " and ID_IntestazioneFattura=" +rows2.column("ID").StringValue + " " 
  end if
  IF  trim(Campo2 + campo1) > ""  then
    //filter on materials
    theQuery = theQuery + " and ( Materiale_Spedito like '%" + Campo1 + "%' and Materiale_Spedito like '%" +Campo2 +"%' ) order by Materiale_Spedito"
    //if Campo1 or Campo2 are empty, it still works...
  end if
  //now use theQuery to do the select
  rows = db.SelectSQL(theQuery)
  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