Ask Your Question
0

How can query run faster ?

asked 2020-01-07 11:04:50 +0200

lonk gravatar image

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 ?C:\fakepath\firebird0.odb

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2020-01-07 14:01:50 +0200

pierre-yves samyn gravatar image

updated 2020-01-08 08:18:59 +0200

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.

image description

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

edit flag offensive delete link more

Comments

Dear @pierre-yves samyn,

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 ?

lonk gravatar imagelonk ( 2020-01-08 02:32:24 +0200 )edit
m.a.riosv gravatar imagem.a.riosv ( 2020-01-08 22:47:46 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-01-07 11:04:50 +0200

Seen: 91 times

Last updated: Jan 08