| Subject | Re: [ib-support] Queries tuning... how to do it |
|---|---|
| Author | Svein Erling Tysvaer |
| Post date | 2003-03-26T12:05:59Z |
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 www.ibobjects.com), 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
records.
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!
HTH,
Svein Erling Tysvaer
IT Advisor
Cancer Registry of Norway
At 11:58 26.03.2003 +0100, you wrote:
The key to query optimization is indexes. I simply started out by putting a
query into IB_SQL (a free tool from www.ibobjects.com), 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
records.
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!
HTH,
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
>it.
>
>Thanks for your help,
>
>XS.
>
>-------------------------------------------------
>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