Subject Re: [ib-support] Queries tuning... how to do it
Author Svein Erling Tysvaer
Hello from a fellow cancer registry!

The key to query optimization is indexes. I simply started out by putting a
query into IB_SQL (a free tool from, prepare it and then
try to understand the resulting plan starting by the first table. I found
plans to be quite easy to read, but then I only use very simple queries
involving just a few tables. A tool I haven't tried, but which I've read is
supposed to be good, is IB Planalyzer.

Unfortunately, I do not know any documents able to help you, but here's a
few things I can think of:

Make your queries as restrictive as possible, i.e. only return one or a few

Don't index low selectivity fields (fields with only a few possible values).

Use atomic keys with your tables.

Consider whether your indexes should be ASCending, DESCending or whether
you need both.

A plan using too many indexes can be as bad as one using no indexes.

When reading generated plans, note that tables towards the end of the plan
will suffer more from being accessed in NATURAL order or using too many
indexes. NATURAL or lots of indexes cause less trouble if they appear in
the first table of your plan.

Outer joins are more demanding than inner joins.

I don't think you can easily see what is the best plan for a query when
comparing two decent plans. However, if you know your data and indexes,
then reading the plan should give you a fair idea of whether it executes
quickly or if you should return to your computer after lunch!

Svein Erling Tysvaer
IT Advisor
Cancer Registry of Norway

At 11:58 26.03.2003 +0100, you wrote:
>Hello listers,
>I wonder where I could find any documents referred to query optimization
>(tuning, improve speed). I don't know it this should be Firebird-specific or
>they're just common SQL (or relational database) rules. The fact is that I
>would like to know this "golden rules" that all SQL queries should take into
>account. I've looked at Firebird website but haven't found any docs about
>Thanks for your help,
>Xavier Solé - x.sole@...
>Cancer Epidemiology
>Catalan Institute of Oncology
>Av. Gran Via s/n, km. 2,7
>08907 L'Hospitalet de Llobregat,
>Barcelona, Catalonia.
>Tel: +34 93 260 74 01
>Fax:+34 93 260 77 87