Hi group, I’m trying to associate two tables with common ID, and take the fields from both tables. I tried this code, but it’s very very slow… how can I improve it? I need to display all the fields of the two tables based on the ID.
rows2 = db.SelectSQL("SELECT * from IntestazioneFatturaAcquisti INNER JOIN NominativiDitte ON NominativiDitte.ID=IntestazioneFatturaAcquisti.ID_Nominativo")
While Not Rows2.AfterLastRow
rows = db.SelectSQL("SELECT * FROM CorpoFatturaAcquisti Where ID_Nominativo="+rows2.column("ID_Nominativo").StringValue+" and Materiale_Spedito like '%" +Campo1 +"%' and Materiale_Spedito like '%" +Campo2 +"%' order by Materiale_Spedito")
While Not Rows.AfterLastRow
ListBoxRicerca.AddRow(rows2.Column("ID_Nominativo").StringValue,rows2.Column("NomeDitta").StringValue.ConvertEncoding(Encodings.WindowsANSI),ConvertDateSQLToItalian(rows2.Column("Data").StringValue).ShortDate,rows2.Column("NumeroFattura").StringValue, rows.Column("Materiale_Spedito").StringValue.ConvertEncoding(Encodings.WindowsANSI))
rows.MoveToNextRow
wend
rows2.MoveToNextRow
Wend
The ID field in the first table is an INTEGER, but the corresponding field in the second table you are trying to join is TEXT.
SQL is performing a implicit conversion between INTEGER and TEXT during the query, and will cause a negative impact on performance.
Make the datatype of both fields (columns) the same, and ensure you have a foreign key relationship defined in your database or at least indexes for both columns. That should speed things up.
Edited to add: Use of the SQL LIKE operator will also cause a slow down in queries. Is there another way to optimize your data so you don’t have to use LIKE?
you can join multiple tables in one query or using a sub select.
instead of * use only the fields you need.
optional create a view which run server side.
took a snapshot of data to have it local, as example to translate a status id to status text.
use a temporary in memory database.
setup indices so the database can find data faster.
each query via network took few milliseconds, a nested loop with querys could make a long delay.
use the ListBox not for thousands or rows if the user can see only a few at one page.
LIKE clauses disable many fast indexed options forcing sequential inspection of EVERY record in a set. Also check your use of indexes. A friend of mine had a very slow query that I noticed that was using a field not indexed, and that also forced a sequential walk on a large set.
Wow, that aggravates things even more.
The keys must be equivalent, and you must use a “create index” for the foreign key, to speed up the query plan of the engine.
Be careful with using a sub-query or sub-select. If it is part of the query where you are listing the fields then the sub-select executes for each row and if you have a lot of rows…
Like previously mentioned by Scott C & Rick Araujo if you are joining on two fields they should be same data type: either both text or both integer.
Also keep in mind that string functions are expensive so using CAST to convert the integer to text or the text to integer will not perform optimally on large data sets.
Not sure what DBMS this is, but you can see how efficient a query is if you review the explain plan which is basically the query optimizer “explaining” how it will break apart and process the query.
Sometimes you can use ALTER TABLE <TABLE_NAME> ALTER COLUMN…etc
It depends on the DBMS.
The best way is to:
Rename the old table
You will need to drop any foreign key constraints if they exist
Recreate the table with the new/different data types
If the DBMS supports it (most do) you can do a SELECT INTO (google the syntax for your DBMS)
Verify the new table
Apply any foreign key constraints, column constraints, indexes, etc.
Drop the old table you renamed.
You should do this during a maintenance period and not during live use.
You should also test this in a different database until you have the script exactly the way it should be so that the production implementation is as smooth as it can be.
Federico, you should be able to write single query that will produce data set (aka list of rows) without ever doing any looping in Xojo. Try first to come up with sql statement that works in your database (ex: if using Postgres use pgAdmin to test the query). Note, you can use ChatGPT (or something similar) to improve your sql statement (query).
Once you have your sql statement ready (optimized and producing the expected results) then you can use it in Xojo to load data wherever you want.
Good evening. I reset the index from text to integer, but that’s not the problem, the code continues to give me very very slow results. obviously I think it depends on the code is there something in the loop that I can improve or in the writing of the SQL?
Federico, your intention is not clear to me. In your code in your inner loop you have
But in your introduction you say you need to display all fields from both the
IntestazioneFatturaAcquisti- and the NominativiDitte table. If you can clear that up I am sure many people here can help you (as well as any AI).
What is the relationship between IntestazioneFatturaAcquisti and NominativiDitte?
Is it 1-to-1? 1-to-Many? Is one table a parent to the other?
Based on my reading of the query I suspect it is 1-to-1.
I rewrote your query into a single statement so that I could make better sense of things:
WITH cte (ID_Nominativo) AS
(SELECT *
from IntestazioneFatturaAcquisti ifa
INNER JOIN NominativiDitte nd
ON ifa.ID_Nominativo = nd.ID
)
SELECT *
FROM CorpoFatturaAcquisti cfa
LEFT OUTER JOIN cte --- this could just as easily be an INNER JOIN
on cfa.ID_Nominativo = cte.ID_Nominativo
WHERE
Materiale_Spedito like '%" +Campo1 +"%'
and Materiale_Spedito like '%" +Campo2 +"%'
order by Materiale_Spedito
The WITH statement is also called a Common Table Expression…think of it as if it is taking the first query and making a temporary table that allows you to use it in a join in the second query.
See here.
I would also check to make sure you have appropriate indexes on ID fields and the Materiale_Spedito columns.
SELECT
IFA.ID_Nominativo,
ND.NomeDitta,
DATE_FORMAT(IFA.Data, '%d/%m/%Y') AS Data_Italiana,
IFA.NumeroFattura,
CFA.Materiale_Spedito
FROM
IntestazioneFatturaAcquisti AS IFA
INNER JOIN
NominativiDitte AS ND ON ND.ID = IFA.ID_Nominativo
INNER JOIN
CorpoFatturaAcquisti AS CFA ON CFA.ID_Nominativo = IFA.ID_Nominativo
WHERE
CFA.Materiale_Spedito LIKE CONCAT('%', @Campo1, '%')
AND CFA.Materiale_Spedito LIKE CONCAT('%', @Campo2, '%')
ORDER BY
CFA.Materiale_Spedito;
