STARTING WITH and LIKE queries are not treated the same in the optimizer.
Try this query it would be interesting to see what the plan looks like.
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 STARTING 'Alpha Corantes'
Alexandre Benson Smith wrote:
> 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 !
>
|
