| Subject | Re: [firebird-support] Optimization Questions (loooong) |
|---|---|
| Author | Alexandre Benson Smith |
| Post date | 2004-05-25T18:42:04Z |
Arno Brinkman wrote:
First, thanks for your patience to read a such long and bad written
message.... :-)
So this explain why I get better performance if I put the tables that
are related with outer joins in the last on the query.
SK_EMPRESA_CREDORDEVEDORNOME is a good index. take a look at the statistics:
RDB$INDEX_NAME RDB$STATISTICS
================================ ========
RDB$PRIMARY292 0.1428571
RDB$PRIMARY357 1.0000000
RDB$FOREIGN305 0.0000362
RDB$PRIMARY296 0.0000334
RDB$FOREIGN298 0.0007955
RDB$PRIMARY295 0.0769230
RDB$PRIMARY294 0.1250000
RDB$PRIMARY41 0.5000000
RDB$PRIMARY26 0.0004235
RDB$PRIMARY17 0.0052083
RDB$PRIMARY16 0.0714285
RDB$PRIMARY5 0.0294117
RDB$PRIMARY4 0.0055865
RDB$PRIMARY1 0.1000000
SK_EMPRESA_CREDORDEVEDOR_NOME 0.0004249
Another thing, on the query that the execution is fast, table
Empresa_CredorDevedor ecd uses the same 2 indices, the diferrence is
that the optimizer put it in the first join, where I think it should
stay on every query. The index SK_Empresa_CredorDevedor_Nome in on
"Name" field, that are "almost" unique. I have 8 duplicated names in
2361 records.
one row from 2391, and TituloReceber are linked to Empresa_CredorDevedor,
There are 81 records on table TituloReceber that are related to table
Empresa_CredorDevedor with ID 941 (given by the search on Name "CISPLA%"
- index SK_EmpresaCredorDevedor_Nome), the total records on table
TituloReceber is 29899, the "Natural on TituloReceber" slows things down...
What I want is that in every of those samples the join should start on
Empresa_CredorDevedor using index SK_EmpresaCredorDevedor_Nome, and then
go on....
The outer join is on the last table (so, should be executed last right ?)
Why not process every inner join (that you be very fast), and after that
process the outer join, with very few select rows.
But a Natural on Table TituloReceber is used instead....
But here are the some info:
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 and ecd.Nome like 'CISPL%') 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 (bo.BorderoId = tr.BorderoID)
PLAN JOIN (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)),BO INDEX (RDB$PRIMARY5))
Returns 79 rows
Execution Time: 16ms
Fetch Time: 20ms
(but know I have some other tasks running that could make this query a bit slow...)
This is the EXACTLY plan I was expecting.... :-)
But I could not use this query.
The select I show are views, and I cannot hardcode something on the on
clause on the view, because the views could be executed in diferent
forms, wih direferent search criteria :-(
I don't expect that put a lot of selects inside de mais select will make
things fast, in the first case, was just one, but on this was 5... I was
surprised...
that have the same value for all rows. (very bad index !!)
for example
select
*
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 = me.MultiEmpresaID)
where
ecd.Nome like 'CISPLA%'
PLAN JOIN (ME NATURAL,TR INDEX (FK_TITULORECEBER_MEMPRESA),TS INDEX
(RDB$PRIMARY292),TT INDEX (RDB$PRIMARY294),ECD INDEX
(SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX (RDB$PRIMARY17),M
INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91))
Returns 81 rows
Execution Time: 300ms
Fetch Time: 2500ms
If I force to disable teh index usage, I got a much better performance:
select
*
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 (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))
note this line:
MultiEmpresa me on (tr.MultiEmpresaID + 0 = me.MultiEmpresaID)
Returns 81 rows
Execution Time: 50ms
Fetch Time: 70ms
got nothing, but without that filter I got a great improvement...
All records on this table have tr.MultiEmpresaID = 1
So in this case with or without that clause I got the same records, but
it is just a case, I have other sites that have more than one recod on
MultiEmpresa. I don't know the correct translation to English, but I
will try to explain. I have some clients that has more then one
enterprise inside the same database, other have just one... When the
user log on my system he could choose to see consolidated data (data
from all enterprises togheter), or filtered for just one enterprise. If
the user chooses to see from just one, all queries are append with
"and MultiEmpresaID = :SomeID".
this query (wich shows data from all Enterprise, i.e. no MultiEmpresaID
filter)
select
*
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 (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))
runs very fast
but if I filter on MultiEmpresaID I got slow response and a Natural scan
on my table
select
*
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 (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))
add the where MultiEmpresaID + 0= 1
the plan for your siggested query is:
PLAN JOIN (JOIN (TR NATURAL,ME INDEX (RDB$PRIMARY357),TT INDEX
(RDB$PRIMARY294),CC INDEX (RDB$PRIMARY17),M INDEX (RDB$PRIMARY41),U
INDEX (RDB$PRIMARY91),TS INDEX (RDB$PRIMARY292)),ECD INDEX
(SK_EMPRESA_CREDORDEVEDOR_NOME))
Returns 81 rows
Execution Time: 4180ms
Fetch Time: 26ms
Take a look at the indices statistics I post above.
If you wish I can make a vnc session for you, or open a temporary
internet access to my server
see you !
--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br
>The optimizer can only order the inner joins, so the fastest way is choosenHi Arno,
>between those. With a left join you force the "optimizer" to execute it in
>the way where you put it.
>
>
>
First, thanks for your patience to read a such long and bad written
message.... :-)
So this explain why I get better performance if I put the tables that
are related with outer joins in the last on the query.
>Don't think so....
>
>>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.
>
>
SK_EMPRESA_CREDORDEVEDORNOME is a good index. take a look at the statistics:
RDB$INDEX_NAME RDB$STATISTICS
================================ ========
RDB$PRIMARY292 0.1428571
RDB$PRIMARY357 1.0000000
RDB$FOREIGN305 0.0000362
RDB$PRIMARY296 0.0000334
RDB$FOREIGN298 0.0007955
RDB$PRIMARY295 0.0769230
RDB$PRIMARY294 0.1250000
RDB$PRIMARY41 0.5000000
RDB$PRIMARY26 0.0004235
RDB$PRIMARY17 0.0052083
RDB$PRIMARY16 0.0714285
RDB$PRIMARY5 0.0294117
RDB$PRIMARY4 0.0055865
RDB$PRIMARY1 0.1000000
SK_EMPRESA_CREDORDEVEDOR_NOME 0.0004249
Another thing, on the query that the execution is fast, table
Empresa_CredorDevedor ecd uses the same 2 indices, the diferrence is
that the optimizer put it in the first join, where I think it should
stay on every query. The index SK_Empresa_CredorDevedor_Nome in on
"Name" field, that are "almost" unique. I have 8 duplicated names in
2361 records.
>Yes. This is very good, the SK_EmpresaCredorDevedor_Nome returns only
>
>>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.
>
>
>
one row from 2391, and TituloReceber are linked to Empresa_CredorDevedor,
There are 81 records on table TituloReceber that are related to table
Empresa_CredorDevedor with ID 941 (given by the search on Name "CISPLA%"
- index SK_EmpresaCredorDevedor_Nome), the total records on table
TituloReceber is 29899, the "Natural on TituloReceber" slows things down...
What I want is that in every of those samples the join should start on
Empresa_CredorDevedor using index SK_EmpresaCredorDevedor_Nome, and then
go on....
>>If I convert the outer join to an inner join (of course will be faster,But why the plan could not be similar, if I use an outer join ?
>>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.
>
>
>
The outer join is on the last table (so, should be executed last right ?)
Why not process every inner join (that you be very fast), and after that
process the outer join, with very few select rows.
But a Natural on Table TituloReceber is used instead....
>>if I remove the last table (the one that have the outer join) I got aNo, I had no tried it...
>>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 ?
>
>
But here are the some info:
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 and ecd.Nome like 'CISPL%') 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 (bo.BorderoId = tr.BorderoID)
PLAN JOIN (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)),BO INDEX (RDB$PRIMARY5))
Returns 79 rows
Execution Time: 16ms
Fetch Time: 20ms
(but know I have some other tasks running that could make this query a bit slow...)
This is the EXACTLY plan I was expecting.... :-)
But I could not use this query.
The select I show are views, and I cannot hardcode something on the on
clause on the view, because the views could be executed in diferent
forms, wih direferent search criteria :-(
>>Case 2:I am using IBPlan Analyzer, that always do a fetch all.
>>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 don't expect that put a lot of selects inside de mais select will make
things fast, in the first case, was just one, but on this was 5... I was
surprised...
>The problem here is causes by use of index FK_TITULORECEBER_MEMPRESA,
>
>>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.
>
>
>
that have the same value for all rows. (very bad index !!)
for example
select
*
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 = me.MultiEmpresaID)
where
ecd.Nome like 'CISPLA%'
PLAN JOIN (ME NATURAL,TR INDEX (FK_TITULORECEBER_MEMPRESA),TS INDEX
(RDB$PRIMARY292),TT INDEX (RDB$PRIMARY294),ECD INDEX
(SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX (RDB$PRIMARY17),M
INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91))
Returns 81 rows
Execution Time: 300ms
Fetch Time: 2500ms
If I force to disable teh index usage, I got a much better performance:
select
*
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 (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))
note this line:
MultiEmpresa me on (tr.MultiEmpresaID + 0 = me.MultiEmpresaID)
Returns 81 rows
Execution Time: 50ms
Fetch Time: 70ms
>>So I think the bad performance is caused by this indexIf I remove the index and still put some filter on the where clause I
>>(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?
>
>
>
got nothing, but without that filter I got a great improvement...
All records on this table have tr.MultiEmpresaID = 1
So in this case with or without that clause I got the same records, but
it is just a case, I have other sites that have more than one recod on
MultiEmpresa. I don't know the correct translation to English, but I
will try to explain. I have some clients that has more then one
enterprise inside the same database, other have just one... When the
user log on my system he could choose to see consolidated data (data
from all enterprises togheter), or filtered for just one enterprise. If
the user chooses to see from just one, all queries are append with
"and MultiEmpresaID = :SomeID".
this query (wich shows data from all Enterprise, i.e. no MultiEmpresaID
filter)
select
*
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 (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))
runs very fast
but if I filter on MultiEmpresaID I got slow response and a Natural scan
on my table
select
*
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 (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))
>Also almost no difference, because i think you're looking at the wrongI don't understand why have a natural plan on TituloReceber (TR) if I
>point. Table ECD is the problem here i guess.
>
>
>
add the where MultiEmpresaID + 0= 1
>STARTING WITH is not taken into consideration for a JOIN ORDER (yet), thusEmpresa_CredorDevedor_Id is a good index
>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 ?
>
>
>
>
the plan for your siggested query is:
PLAN JOIN (JOIN (TR NATURAL,ME INDEX (RDB$PRIMARY357),TT INDEX
(RDB$PRIMARY294),CC INDEX (RDB$PRIMARY17),M INDEX (RDB$PRIMARY41),U
INDEX (RDB$PRIMARY91),TS INDEX (RDB$PRIMARY292)),ECD INDEX
(SK_EMPRESA_CREDORDEVEDOR_NOME))
Returns 81 rows
Execution Time: 4180ms
Fetch Time: 26ms
Take a look at the indices statistics I post above.
If you wish I can make a vnc session for you, or open a temporary
internet access to my server
>Regards,Thank you very much !
>Arno Brinkman
>ABVisie
>
>
>
see you !
--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br