Regarding the attached Firebird database, running queries namely AccountCodeLevel123456b or AccountCodeLevel123456c takes nearly 50 seconds and going to EOF takes nearly 50 seconds more as well.
Is it possible to make them run faster ?firebird0.odb
Hi
It is possible to set indexes for the fields involved in the joins. Edit the table, click in the toolbar on the Index Design tool and then as many times on New Index as necessary.
On my platform this has allowed me to significantly speed up the requests. See firebird.odb
[UPDATE ]
Thank you for the feedback. I realize I forgot to include the screenshot I had prepared.
In general, an index is created automatically when creating the primary key of a table; similarly, it is customary to create one for each foreign key in order to improve the speed of joins.
To sum up, and in general, I would advise to have indexes for the fields intervening in JOIN, WHERE, ORDER BY and GROUP BY.
This allows the engine to use these indexes for these operations. The engine has several solutions. Among them the “full table scan” and the “Index range scan”. These are “paths”.
The index range scan is faster.
Regards
Dear @PYS,
Thank you so much, queries run only 1 second instead of nearly 50 seconds and point EOF within 1 second as well.
I have never used Index Design before. I have to learn its use.
But reading this, it says mostly of how to set up.
Can you please explain more in brief of why it made my quesries fly so fast ?