Hi,

> Take a cup of coffe, make your self confortable and keep reading if you
> have patience....

keep on reading ....

> This weekend I was looking on some views and trying to optimize it, I
> found a way to make it quicker, but don't like the way I get, What I
> would like to know is:
>
> Why the optimizer are change the plan so much when I have outer joins ?

The optimizer can only order the inner joins, so the fastest way is choosen
between those. With a left join you force the "optimizer" to execute it in
the way where you put it.

> Some info:
>
> Firebird 1.5.0.4306 SS on WinXP
>
> Record Count Table
> 192 ContaContabil
> 2361 Empresa_CredorDevedor
> 27796 TituloReceberBaixa
> 29899 TituloReceber
>
> the other tables are even small...
>
> Here are some samples:
>
> I will try to simplify the query, so I will remove some Columns, but
> leave the related Tables (All the envolved tables in the from clause
> have columns used in the original query)
>
> Case 1:
> the bad guy:
> select
> trb.TituloReceberBaixaId,
> tr.BorderoId,
> Bo.Numero
> from
> TituloReceberBaixa trb join
> TituloReceber tr on (trb.TituloReceberId =
> tr.TituloReceberId) join
> TituloTipo tt on (tr.TituloTipoId =
> tt.TituloTipoId) join
> Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
> ecd.Empresa_CredorDevedor_Id) join
> ContaContabil cc on (tr.ContaContabilId =
> cc.ContaContabilId) join
> Moeda m on (tr.MoedaId = m.MoedaId) join
> TituloBaixaTipo tbt on (trb.TituloBaixaTipoId =
> tbt.TituloBaixaTipoId) join
> ContaBancaria cb on (cb.ContaBancariaId =
> trb.ContaBancariaId + 0) join
> AgenciaBancaria a on (cb.AgenciaBancariaId =
> a.AgenciaBancariaId) join
> Banco bb on (a.BancoId = bb.BancoId) join
> TituloStatus ts1 on (tr.StatusId = ts1.TituloStatusId)
> join
> TituloStatus ts2 on (trb.StatusId =
> ts2.TituloStatusId) join
> MultiEmpresa me on (me.MultiEmpresaID =
> tr.MultiEmpresaID + 0) left join
> Bordero bo on (BorderoID = tr.BorderoID)
> where
> ecd.Nome like 'CISPL%'
> PLAN JOIN (JOIN (TRB NATURAL,TS2 INDEX (RDB$PRIMARY292),TR INDEX
> (RDB$PRIMARY296),ME INDEX (RDB$PRIMARY357),TT INDEX (RDB$PRIMARY294),ECD
> INDEX (SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX
> (RDB$PRIMARY17),M INDEX (RDB$PRIMARY41),TBT INDEX (RDB$PRIMARY295),CB
> INDEX (RDB$PRIMARY16),A INDEX (RDB$PRIMARY1),BB INDEX (RDB$PRIMARY4),TS1
> INDEX (RDB$PRIMARY292)),BO INDEX (RDB$PRIMARY5))
>
> Returns 79 rows
> Execution Time: 2284ms
> Fetch Time: 13ms

The thing i notice from your PLAN is that for table Empresa_CredorDevedor
ECD two indexes (SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26) are used.
Becuase this is at the 6th inner join this could really slow down things.

> the good guy:
> select
> trb.TituloReceberBaixaId,
> tr.BorderoId,
> (Select Numero from Bordero where BorderoID = tr.BorderoID)
> from
> TituloReceberBaixa trb join
> TituloReceber tr on (trb.TituloReceberId =
> tr.TituloReceberId) join
> TituloTipo tt on (tr.TituloTipoId =
> tt.TituloTipoId) join
> Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
> ecd.Empresa_CredorDevedor_Id) join
> ContaContabil cc on (tr.ContaContabilId =
> cc.ContaContabilId) join
> Moeda m on (tr.MoedaId = m.MoedaId) join
> TituloBaixaTipo tbt on (trb.TituloBaixaTipoId =
> tbt.TituloBaixaTipoId) join
> ContaBancaria cb on (cb.ContaBancariaId =
> trb.ContaBancariaId + 0) join
> AgenciaBancaria a on (cb.AgenciaBancariaId =
> a.AgenciaBancariaId) join
> Banco bb on (a.BancoId = bb.BancoId) join
> TituloStatus ts1 on (tr.StatusId = ts1.TituloStatusId)
> join
> TituloStatus ts2 on (trb.StatusId =
> ts2.TituloStatusId) join
> MultiEmpresa me on (me.MultiEmpresaID =
> tr.MultiEmpresaID + 0)
> where
> ecd.Nome like 'CISPL%'
>
> PLAN (BORDERO INDEX (RDB$PRIMARY5))
> PLAN JOIN (ECD INDEX (SK_EMPRESA_CREDORDEVEDOR_NOME),TR INDEX
> (RDB$FOREIGN298),ME INDEX (RDB$PRIMARY357),TRB INDEX
> (RDB$FOREIGN305),TS2 INDEX (RDB$PRIMARY292),TT INDEX (RDB$PRIMARY294),CC
> INDEX (RDB$PRIMARY17),M INDEX (RDB$PRIMARY41),TBT INDEX
> (RDB$PRIMARY295),CB INDEX (RDB$PRIMARY16),A INDEX (RDB$PRIMARY1),BB
> INDEX (RDB$PRIMARY4),TS1 INDEX (RDB$PRIMARY292))
>
> Returns 79 rows
> Execution Time: 15ms
> Fetch Time: 13ms

The optimizer chooses a query PLAN which uses a index for _every_ table, so
the query execution starts already with an limited resultset from table
Empresa_CredorDevedor and starts joining from that point.

> If I convert the outer join to an inner join (of course will be faster,
> but I was not expecting a so huge difference, and don't undersant a plan
> so diferent)
>
> select
> trb.TituloReceberBaixaId,
> tr.BorderoId,
> Bo.Numero
> from
> TituloReceberBaixa trb join
> TituloReceber tr on (trb.TituloReceberId =
> tr.TituloReceberId) join
> TituloTipo tt on (tr.TituloTipoId =
> tt.TituloTipoId) join
> Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
> ecd.Empresa_CredorDevedor_Id) join
> ContaContabil cc on (tr.ContaContabilId =
> cc.ContaContabilId) join
> Moeda m on (tr.MoedaId = m.MoedaId) join
> TituloBaixaTipo tbt on (trb.TituloBaixaTipoId =
> tbt.TituloBaixaTipoId) join
> ContaBancaria cb on (cb.ContaBancariaId =
> trb.ContaBancariaId + 0) join
> AgenciaBancaria a on (cb.AgenciaBancariaId =
> a.AgenciaBancariaId) join
> Banco bb on (a.BancoId = bb.BancoId) join
> TituloStatus ts1 on (tr.StatusId = ts1.TituloStatusId)
> join
> TituloStatus ts2 on (trb.StatusId =
> ts2.TituloStatusId) join
> MultiEmpresa me on (me.MultiEmpresaID =
> tr.MultiEmpresaID + 0) join
> Bordero bo on (BorderoID = tr.BorderoID)
> where
> ecd.Nome like 'CISPL%'
>
> PLAN JOIN (ECD INDEX (SK_EMPRESA_CREDORDEVEDOR_NOME),TR INDEX
> (RDB$FOREIGN298),BO INDEX (RDB$PRIMARY5),TRB INDEX (RDB$FOREIGN305),TS2
> INDEX (RDB$PRIMARY292),TT INDEX (RDB$PRIMARY294),CC INDEX
> (RDB$PRIMARY17),M INDEX (RDB$PRIMARY41),TBT INDEX (RDB$PRIMARY295),CB
> INDEX (RDB$PRIMARY16),A INDEX (RDB$PRIMARY1),BB INDEX (RDB$PRIMARY4),TS1
> INDEX (RDB$PRIMARY292),ME INDEX (RDB$PRIMARY357))
>
> Returns 5 rows
> Execution Time: 6ms
> Fetch Time: 12ms

The same as above, a index is used for every table.

> if I remove the last table (the one that have the outer join) I got a
> plan very similar to the "good" one, and something similar to what I was
> expecting:
>
> select
> trb.TituloReceberBaixaId,
> tr.BorderoId
> from
> TituloReceberBaixa trb join
> TituloReceber tr on (trb.TituloReceberId =
> tr.TituloReceberId) join
> TituloTipo tt on (tr.TituloTipoId =
> tt.TituloTipoId) join
> Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
> ecd.Empresa_CredorDevedor_Id) join
> ContaContabil cc on (tr.ContaContabilId =
> cc.ContaContabilId) join
> Moeda m on (tr.MoedaId = m.MoedaId) join
> TituloBaixaTipo tbt on (trb.TituloBaixaTipoId =
> tbt.TituloBaixaTipoId) join
> ContaBancaria cb on (cb.ContaBancariaId =
> trb.ContaBancariaId + 0) join
> AgenciaBancaria a on (cb.AgenciaBancariaId =
> a.AgenciaBancariaId) join
> Banco bb on (a.BancoId = bb.BancoId) join
> TituloStatus ts1 on (tr.StatusId = ts1.TituloStatusId)
> join
> TituloStatus ts2 on (trb.StatusId =
> ts2.TituloStatusId) join
> MultiEmpresa me on (me.MultiEmpresaID =
> tr.MultiEmpresaID + 0)
> where
> ecd.Nome like 'CISPL%'
> PLAN JOIN (ECD INDEX (SK_EMPRESA_CREDORDEVEDOR_NOME),TR INDEX
> (RDB$FOREIGN298),ME INDEX (RDB$PRIMARY357),TRB INDEX
> (RDB$FOREIGN305),TS2 INDEX (RDB$PRIMARY292),TT INDEX (RDB$PRIMARY294),CC
> INDEX (RDB$PRIMARY17),M INDEX (RDB$PRIMARY41),TBT INDEX
> (RDB$PRIMARY295),CB INDEX (RDB$PRIMARY16),A INDEX (RDB$PRIMARY1),BB
> INDEX (RDB$PRIMARY4),TS1 INDEX (RDB$PRIMARY292))
>
> Returns 79 rows
> Execution Time: 13ms
> Fetch Time: 12ms

Have you tried moving "ecd.Nome like 'CISPL%'" to the ON clause ?

> Case 2:
> the bad guy:
> select

<snip>

> from
> TituloReceber tr join
> TituloTipo tt on (tt.TituloTipoId = tr.TituloTipoId)

join

> Empresa_CredorDevedor ecd on (ecd.Empresa_CredorDevedor_Id =
> tr.EmpresaCredorDevedorId) join
> ContaContabil cc on (cc.ContaContabilId =
> tr.ContaContabilId) join
> Moeda m on (m.MoedaId = tr.MoedaId) join
> Usuario u on (u.UsuarioId = tr.UsuarioId) join
> TituloStatus ts on (ts.TituloStatusId = tr.StatusId) join
> MultiEmpresa me on (me.MultiEmpresaID =
> tr.MultiEmpresaID + 0) left join
> Bordero bo on (bo.BorderoID = tr.BorderoID) left join
> ContaBancaria cb on (cb.ContabancariaID =
> bo.ContaBancariaID) left join
> AgenciaBancaria ag on (ag.AgenciaBancariaID =
> cb.AgenciaBancariaID) left join
> Banco ba on (ba.BancoID = ag.BancoID)
> where
> ecd.Nome like 'CISPLA%'
>
> PLAN JOIN (JOIN (JOIN (JOIN (JOIN (TR NATURAL,ME INDEX
> (RDB$PRIMARY357),TT INDEX (RDB$PRIMARY294),ECD INDEX
> (SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX (RDB$PRIMARY17),M
> INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91),TS INDEX
> (RDB$PRIMARY292)),BO INDEX (RDB$PRIMARY5)),CB INDEX (RDB$PRIMARY16)),AG
> INDEX (RDB$PRIMARY1)),BA INDEX (RDB$PRIMARY4))
>
>
> Returns 81 rows
> Execution Time: 1120ms
> Fetch Time: 411ms

Also ECD is using 2 indexes here
(SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26)

> the good guy:
> select
> tr.Numero,
> tr.Parcela,
> tr.EmpresaCredorDevedorId,
> ecd.Nome,
> ecd.Referencia,
> tr.BorderoId,
> (Select bo.Numero from Bordero bo where bo.BorderoID =

tr.BorderoID),

> (Select bo.ContaBancariaID from Bordero bo where bo.BorderoID =
> tr.BorderoID),
> (Select cb.Numero from ContaBancaria cb join Bordero bo on
> (cb.ContaBancariaID = bo.ContabancariaID) where bo.BorderoID =
> tr.BorderoID),
> (Select ag.BancoID from AgenciaBancaria ag join ContaBancaria cb
> on (cb.AgenciaBancariaID = ag.AgenciaBancariaID) join Bordero bo on
> (bo.ContaBancariaID = cb.ContabancariaID) where bo.BorderoID =
> tr.BorderoID),
> (Select ba.Nome from Banco ba join AgenciaBancaria ag on
> (ag.BancoID = ba.BancoID) join ContaBancaria cb on (cb.AgenciaBancariaID
> = ag.AgenciaBancariaID) join Bordero bo on (bo.ContaBancariaID =
> cb.ContabancariaID) where bo.BorderoID = tr.BorderoID)
> from
> TituloReceber tr join
> TituloTipo tt on (tr.TituloTipoId = tt.TituloTipoId)

join

> Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
> ecd.Empresa_CredorDevedor_Id) join
> ContaContabil cc on (tr.ContaContabilId =
> cc.ContaContabilId) join
> Moeda m on (tr.MoedaId = m.MoedaId) join
> Usuario u on (tr.UsuarioId = u.UsuarioId) join
> TituloStatus ts on (tr.StatusId = ts.TituloStatusId) join
> MultiEmpresa me on (tr.MultiEmpresaID + 0 =
> me.MultiEmpresaID)
> where
> ecd.Nome like 'CISPLA%'
>
> PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
> (RDB$PRIMARY1),BA INDEX (RDB$PRIMARY4))
> PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
> (RDB$PRIMARY1))
> PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16))
> PLAN (BO INDEX (RDB$PRIMARY5))
> PLAN (BO INDEX (RDB$PRIMARY5))
> PLAN JOIN (ECD INDEX (SK_EMPRESA_CREDORDEVEDOR_NOME),TR INDEX
> (RDB$FOREIGN298),ME INDEX (RDB$PRIMARY357),TT INDEX (RDB$PRIMARY294),CC
> INDEX (RDB$PRIMARY17),M INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91),TS
> INDEX (RDB$PRIMARY292))
>
>
> Returns 81 rows
> Execution Time: 17ms
> Fetch Time: 16ms

Did you compare with fetching all rows for all queries ?

> I have some of other ones that I have increased the speed in the same
> magnitude as the above samples...
>
> Another case (a bit different), I took the "last good guy" query and add
> another check in the where, this brings the speed down considerabily:
> select
> tr.Numero,
> tr.Parcela,
> tr.EmpresaCredorDevedorId,
> ecd.Nome,
> ecd.Referencia,
> tr.BorderoId,
> (Select bo.Numero from Bordero bo where bo.BorderoID =

tr.BorderoID),

> (Select bo.ContaBancariaID from Bordero bo where bo.BorderoID =
> tr.BorderoID),
> (Select cb.Numero from ContaBancaria cb join Bordero bo on
> (cb.ContaBancariaID = bo.ContabancariaID) where bo.BorderoID =
> tr.BorderoID),
> (Select ag.BancoID from AgenciaBancaria ag join ContaBancaria cb
> on (cb.AgenciaBancariaID = ag.AgenciaBancariaID) join Bordero bo on
> (bo.ContaBancariaID = cb.ContabancariaID) where bo.BorderoID =
> tr.BorderoID),
> (Select ba.Nome from Banco ba join AgenciaBancaria ag on
> (ag.BancoID = ba.BancoID) join ContaBancaria cb on (cb.AgenciaBancariaID
> = ag.AgenciaBancariaID) join Bordero bo on (bo.ContaBancariaID =
> cb.ContabancariaID) where bo.BorderoID = tr.BorderoID)
> from
> TituloReceber tr join
> TituloTipo tt on (tr.TituloTipoId = tt.TituloTipoId)

join

> Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
> ecd.Empresa_CredorDevedor_Id) join
> ContaContabil cc on (tr.ContaContabilId =
> cc.ContaContabilId) join
> Moeda m on (tr.MoedaId = m.MoedaId) join
> Usuario u on (tr.UsuarioId = u.UsuarioId) join
> TituloStatus ts on (tr.StatusId = ts.TituloStatusId) join
> MultiEmpresa me on (tr.MultiEmpresaID + 0 =
> me.MultiEmpresaID)
> where
> ecd.Nome like 'CISPLA%' and
> tr.MultiEmpresaID = 1;
>
> PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
> (RDB$PRIMARY1),BA INDEX (RDB$PRIMARY4))
> PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
> (RDB$PRIMARY1))
> PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16))
> PLAN (BO INDEX (RDB$PRIMARY5))
> PLAN (BO INDEX (RDB$PRIMARY5))
> PLAN JOIN (TR INDEX (FK_TITULORECEBER_MEMPRESA),ME INDEX
> (RDB$PRIMARY357),TT INDEX (RDB$PRIMARY294),ECD INDEX
> (SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX (RDB$PRIMARY17),M
> INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91),TS INDEX (RDB$PRIMARY292))
>
> Returns 81 rows
> Execution Time: 1150ms
> Fetch Time: 422ms

Again you see 2 indexes choosen for ECD.

> tr.MultiEmpresaID is a column that got indexed because of a FK, this is
> a VERY BAD index (I still prefer to have declarative FK constraints on
> create table statment, instead of enforce it via triggers, I tend to
> disable the indexes on views adding +0 to the column). When FB permits
> to create FK's without indexes I will be happy... ;-)

Because this index is used for the very first table i don't think this index
will really hurt in this case.

> So I think the bad performance is caused by this index
> (FK_TITULORECEBER_MEMPRESA) so I changed it to:
> select
> tr.Numero,
> tr.Parcela,
> tr.EmpresaCredorDevedorId,
> ecd.Nome,
> ecd.Referencia,
> tr.BorderoId,
> (Select bo.Numero from Bordero bo where bo.BorderoID =

tr.BorderoID),

> (Select bo.ContaBancariaID from Bordero bo where bo.BorderoID =
> tr.BorderoID),
> (Select cb.Numero from ContaBancaria cb join Bordero bo on
> (cb.ContaBancariaID = bo.ContabancariaID) where bo.BorderoID =
> tr.BorderoID),
> (Select ag.BancoID from AgenciaBancaria ag join ContaBancaria cb
> on (cb.AgenciaBancariaID = ag.AgenciaBancariaID) join Bordero bo on
> (bo.ContaBancariaID = cb.ContabancariaID) where bo.BorderoID =
> tr.BorderoID),
> (Select ba.Nome from Banco ba join AgenciaBancaria ag on
> (ag.BancoID = ba.BancoID) join ContaBancaria cb on (cb.AgenciaBancariaID
> = ag.AgenciaBancariaID) join Bordero bo on (bo.ContaBancariaID =
> cb.ContabancariaID) where bo.BorderoID = tr.BorderoID)
> from
> TituloReceber tr join
> TituloTipo tt on (tr.TituloTipoId = tt.TituloTipoId)

join

> Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
> ecd.Empresa_CredorDevedor_Id) join
> ContaContabil cc on (tr.ContaContabilId =
> cc.ContaContabilId) join
> Moeda m on (tr.MoedaId = m.MoedaId) join
> Usuario u on (tr.UsuarioId = u.UsuarioId) join
> TituloStatus ts on (tr.StatusId = ts.TituloStatusId) join
> MultiEmpresa me on (tr.MultiEmpresaID + 0 =
> me.MultiEmpresaID)
> where
> ecd.Nome like 'CISPLA%' and
> tr.MultiEmpresaID + 0 = 1;
>
> PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
> (RDB$PRIMARY1),BA INDEX (RDB$PRIMARY4))
> PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
> (RDB$PRIMARY1))
> PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16))
> PLAN (BO INDEX (RDB$PRIMARY5))
> PLAN (BO INDEX (RDB$PRIMARY5))
> PLAN JOIN (TR NATURAL,ME INDEX (RDB$PRIMARY357),TT INDEX
> (RDB$PRIMARY294),ECD INDEX
> (SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX (RDB$PRIMARY17),M
> INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91),TS INDEX (RDB$PRIMARY292))
>
> Returns 81 rows
> Execution Time: 1138ms
> Fetch Time: 424ms

So with removing the index you win almost nothing?

> This puzzled me, why even when I disable the optimizer to choose the bad
> index they choose a very diferent plan from the good one, so I create a
> tempo Column called MEmpresaID on Table TituloReceber (the tr on teh
> above statments), did a update TituloReceber set MEmpresaID =
> MultiEmpresaID, and did NOT create any indexes on that column, and did
> the following:
> select
> tr.Numero,
> tr.Parcela,
> tr.EmpresaCredorDevedorId,
> ecd.Nome,
> ecd.Referencia,
> tr.BorderoId,
> (Select bo.Numero from Bordero bo where bo.BorderoID =

tr.BorderoID),

> (Select bo.ContaBancariaID from Bordero bo where bo.BorderoID =
> tr.BorderoID),
> (Select cb.Numero from ContaBancaria cb join Bordero bo on
> (cb.ContaBancariaID = bo.ContabancariaID) where bo.BorderoID =
> tr.BorderoID),
> (Select ag.BancoID from AgenciaBancaria ag join ContaBancaria cb
> on (cb.AgenciaBancariaID = ag.AgenciaBancariaID) join Bordero bo on
> (bo.ContaBancariaID = cb.ContabancariaID) where bo.BorderoID =
> tr.BorderoID),
> (Select ba.Nome from Banco ba join AgenciaBancaria ag on
> (ag.BancoID = ba.BancoID) join ContaBancaria cb on (cb.AgenciaBancariaID
> = ag.AgenciaBancariaID) join Bordero bo on (bo.ContaBancariaID =
> cb.ContabancariaID) where bo.BorderoID = tr.BorderoID)
> from
> TituloReceber tr join
> TituloTipo tt on (tr.TituloTipoId = tt.TituloTipoId)

join

> Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
> ecd.Empresa_CredorDevedor_Id) join
> ContaContabil cc on (tr.ContaContabilId =
> cc.ContaContabilId) join
> Moeda m on (tr.MoedaId = m.MoedaId) join
> Usuario u on (tr.UsuarioId = u.UsuarioId) join
> TituloStatus ts on (tr.StatusId = ts.TituloStatusId) join
> MultiEmpresa me on (tr.MEmpresaID = me.MultiEmpresaID)
> where
> ecd.Nome like 'CISPLA%' and
> tr.MEmpresaID = 1;
>
> PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
> (RDB$PRIMARY1),BA INDEX (RDB$PRIMARY4))
> PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
> (RDB$PRIMARY1))
> PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16))
> PLAN (BO INDEX (RDB$PRIMARY5))
> PLAN (BO INDEX (RDB$PRIMARY5))
> PLAN JOIN (TR NATURAL,ME INDEX (RDB$PRIMARY357),TT INDEX
> (RDB$PRIMARY294),ECD INDEX
> (SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX (RDB$PRIMARY17),M
> INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91),TS INDEX (RDB$PRIMARY292))
>
> Returns 81 rows
> Execution Time: 1108ms
> Fetch Time: 413ms
>
> Note that I have not mention the column with the bad index, and only the
> new one without index...

Also almost no difference, because i think you're looking at the wrong
point. Table ECD is the problem here i guess.

> I have a simple example of the same query:
> select
> tr.Numero,
> tr.Parcela,
> tr.EmpresaCredorDevedorId,
> ecd.Nome,
> ecd.Referencia,
> tr.BorderoId
> from
> TituloReceber tr join
> TituloTipo tt on (tr.TituloTipoId = tt.TituloTipoId)

join

> Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
> ecd.Empresa_CredorDevedor_Id) join
> ContaContabil cc on (tr.ContaContabilId =
> cc.ContaContabilId) join
> Moeda m on (tr.MoedaId = m.MoedaId) join
> Usuario u on (tr.UsuarioId = u.UsuarioId) join
> TituloStatus ts on (tr.StatusId = ts.TituloStatusId) join
> MultiEmpresa me on (tr.MEmpresaID = me.MultiEmpresaID)
> where
> ecd.Nome like 'CISPLA%' and
> tr.MEmpresaID = 1;
>
> PLAN JOIN (TR NATURAL,ME INDEX (RDB$PRIMARY357),TT INDEX
> (RDB$PRIMARY294),ECD INDEX
> (SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX (RDB$PRIMARY17),M
> INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91),TS INDEX (RDB$PRIMARY292))
>
> Returns 81 rows
> Execution Time: 1300ms
> Fetch Time: 193ms

Again same problem...

> Why did the optmizer choose a very diferent approach, and did not start
> from the Empresa_CredorDevedor (ecd) table using index
> SK_EMPRESA_CREDORDEVEDOR_NOME like on the "good guy" case...
>
> I can provide more information (indexes statistics) if you wish (I just
> don't post because this message was already long), or provide a vnc
> session or a temporary FB access trough internet, if someone wish to
> play with this data...
>
> thank you very much for your patience....

STARTING WITH is not taken into consideration for a JOIN ORDER (yet), thus
for special cases you've to workaround that fact.


SELECT
tr.Numero,
tr.Parcela,
tr.EmpresaCredorDevedorId,
ecd.Nome,
<snip>
FROM
TituloReceber tr
JOIN TituloTipo tt ON (tr.TituloTipoId = tt.TituloTipoId)
JOIN Empresa_CredorDevedor ecd ON
(tr.EmpresaCredorDevedorId = ecd.Empresa_CredorDevedor_Id + 0)
JOIN ContaContabil cc ON (tr.ContaContabilId = cc.ContaContabilId)
JOIN Moeda m ON (tr.MoedaId = m.MoedaId)
JOIN Usuario u ON (tr.UsuarioId = u.UsuarioId)
JOIN TituloStatus ts ON (tr.StatusId = ts.TituloStatusId)
JOIN MultiEmpresa me ON (tr.MEmpresaID = me.MultiEmpresaID)
WHERE
ecd.Nome LIKE 'CISPLA%' and
tr.MEmpresaID = 1;


In above query i force the optimizer not use a index for
Empresa_CredorDevedor_Id, so if i'm right the optimizer chooses a JOIN order
where the table ECD will be at the first place. And though a index will be
used on this table, because of the LIKE.
Thus what happens in all mentioned slow queries when you add "+ 0" to
Empresa_CredorDevedor_Id in the ON clause ?


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81