[Firebird-test] FB 2.0 Alpha 3 - Doubts in optmizer improvements

[Alexandre B. S. <ib...@th...>]

Guys,

I have played a bit with FB 2.0 alpha 1, but now downloaded the alpha 3 
version. The main focus os my tests will be the new INTL module with 
special attention to the new PT_BR collation.

I have restored a 1.5 database and so far all appears ok !

The other feature I like to look (not to test since I don't have enough 
knowledge to stress test it) is the Optimizer improvements.

I was looking at the Release Notes and found this:
"Better support for STARTING WITH"

I'd like to know a bit more about this feature. I will present an example:
Select
   *
from
   TituloReceber TR join
   TituloReceberBaixa TRB on (TRB.TituloReceberID = TR.TituloReceberID) join
   Empresa_CredorDevedor ECD on (ECD.Empresa_CredorDevedor_ID = 
TR.EmpresaCredorDevedorID) join
   MultiEmpresa ME on (ME.MultiEmpresaID = TR.MultiEmpresaID)
where
   ECD.Nome like 'Alpha Corantes%'

PLAN JOIN (TRB NATURAL, TR INDEX (PK_TITULORECEBER), ME INDEX 
(PK_MULTIEMPRESA), ECD INDEX (PK_EMPRESA_CREDORDEVEDOR))
around 79k reads on mos tables

but when I change the LIKE to "="
Select
   *
from
   TituloReceber TR join
   TituloReceberBaixa TRB on (TRB.TituloReceberID = TR.TituloReceberID) join
   Empresa_CredorDevedor ECD on (ECD.Empresa_CredorDevedor_ID = 
TR.EmpresaCredorDevedorID) join
   MultiEmpresa ME on (ME.MultiEmpresaID = TR.MultiEmpresaID)
where
   ECD.Nome = 'Alpha Corantes'

I got this plan
PLAN JOIN (ECD INDEX (SK_EMPRESA_CREDORDEVEDOR_NOME), TR INDEX 
(FK_TRECEBER_ECD), TRB INDEX (FK_TRECEBERBAIXA_TRECEBER), ME INDEX 
(PK_MULTIEMPRESA))
around 800 reads on most tables.

The later was the expected plan for the first case. the 
SK_Empresa_CredorDevedor_Nome index is very good (RDB$STATISTICS for 
this index is 0,000321336759952828)
    Index SK_EMPRESA_CREDORDEVEDOR_NOME (3)
    Depth: 2, leaf buckets: 5, nodes: 3130
    Average data length: 7.00, total dup: 18, max dup: 1
    Fill distribution:
         0 - 19% = 0
        20 - 39% = 0
        40 - 59% = 0
        60 - 79% = 0
        80 - 99% = 5

AFAIR STARTING WITH has lower precedence (don't know if it's the correct 
term) to specify the join order and index selection, is the improvement 
mentioned above a better handling of this type of query ? If so, why the 
index was not used in the first case ?

I am ready to provide more information if someone need it.

The optimization done in views with agregates was really fantastic ! :-D

Good job guys !

see you !

-- 

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.9.2/55 - Release Date: 21/07/2005