Well, Antonio, if the plans are different, then B might be quicker than A.

Unfortunately, I'm still confused, I'm used to the index first to be used to be first in the plan, but as far as I can see, there's no constant that you're comparing AX to, so if it is first in the plan, then it cannot use an index! I guess it might just be how our tools show the plan, I'm more used to it being shown as something like (just using B in this example):

PLAN JOIN(A ORDER L_AGE_06, AU INDEX (PK_L_AGUS), U INDEX (PK_B_USU), S INDEX (PK_L_SAL), FS INDEX (LI_FS_01))

Can your tool show the plan similar to this? It just helps to see things in the correct order, your reporting of plans does not say which table is accessed first.

Moreover, the plan doesn't show what is used for A except for ordering. In Firebird 1.5, if an index is used for ordering, then indexes used for finding the records are not shown - even though they're used. So, change your queries slightly by using

order by a.codigo_posto+0,a.codigo_sala,a.data,a.de,a.ate,a.sequencial_sala,a.encaixe

so that no index is used for the ordering. It will probably not affect the speed your queries use, but it should reveal more of the underlying plan.

Report back the new plans.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Antonio Carlos Ribeiro
Sent: 15. april 2009 03:57
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Strangely Faster

Set,

I'm so sorry, they confuse you because while copying and pasting I've messed the numbers and the extracts:

I'm using Firebird 1.54.

Query A
11:13 - Time to process: 157ms
11:18 - Time to process: 140ms

Query B
11:13 - Time to process: 719ms
11:18 - Time to process: 719ms

I was testing both queries almost at the same time. IMHO query A (indirect) could never be that faster.

In this process time they are both returning a page of results, not just the first record.

Here are the plans:

Plan for Query A

PLAN (AX INDEX (L_AGE_CA))
PLAN (AU INDEX (PK_L_AGUS))
PLAN (U INDEX (PK_B_USU))
PLAN (S INDEX (PK_L_SAL))
PLAN (FS INDEX (LI_FS_01))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (AX INDEX (L_AGE_CA))
PLAN (A ORDER L_AGE_06)


Plan for Query B

PLAN (AU INDEX (PK_L_AGUS))
PLAN (U INDEX (PK_B_USU))
PLAN (S INDEX (PK_L_SAL))
PLAN (FS INDEX (LI_FS_01))
PLAN (A ORDER L_AGE_06)



Thank you very much,

Antonio

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> Antonio,
> your queries and question confuse me. For one thing, query A seems a lot slower 11:13, but a lot quicker five minutes later. Try running both queries several times and in different order. Another thing that doesn't make sense, is that your extract from query A access the fields directly, whereas the full query A uses 'indirect access'. For query B it is vice versa. Hence, it is impossible to see which query is quick and which is slow.
>
> There are two more important things that you should supply in your question: Which Firebird version do you use, and what plan Firebird choose to use. Also, I assume the time you measure, is for the entire query to return, not for only the first record to appear?
>
> Unless something is visible from the plan, it is unlikely that I might help in understanding your reported slowness from direct select when compared to indirect select, but others may know things that I've never heard about.
>
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Antonio Carlos Ribeiro
> Sent: 13. april 2009 16:40
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Strangely Faster
>
> Hi!
>
> I would like to understand why "Query A" is 4.57 times faster than "Query B". Here are the timings to get a cursor for both:
>
> Query A
> 11:13 - Time to process: 00:00:00:719
> 11:18 - Time to process: 00:00:00:140
>
> Query B
> 11:13 - Time to process: 00:00:00:157
> 11:18 - Time to process: 00:00:00:719
>
> The difference between those 2 queries is basically reading fields indirectly with selects in the faster one, like this:
>
> // ----------------------------------------
> // extract from Query A
> // ----------------------------------------
> select
> a.codigo_posto
> from
> L_AGENDA a
> where a.CODIGO_POSTO = '01'
> and a.DATA >= '04/14/2009 00:00:00'
> and a.DATA <= '04/14/2009 23:59:59'
> and a.codigo_sala = '01'
> order by a.codigo_posto,a.codigo_sala,a.data,a.de,a.ate,a.sequencial_sala,a.encaixe
>
> // ----------------------------------------
> // extract from Query B
> // ----------------------------------------
> select
> (select ax.codigo_posto from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) codigo_posto
> from
> L_AGENDA a
> where a.CODIGO_POSTO = '01'
> and a.DATA >= '04/14/2009 00:00:00'
> and a.DATA <= '04/14/2009 23:59:59'
> and a.codigo_sala = '01'
> order by a.codigo_posto,a.codigo_sala,a.data,a.de,a.ate,a.sequencial_sala,a.encaixe
>
>
> But it doesn't make any sense to me, because in the slower one Firebird is reading the table once for *each record* and in the faster it is reading one time for *each column*, that makes it search and read *several times the same record*.
>
> Look at the full queries:
>
> /// -------> Query A
>
> select
> (select SubStr(StrRepeat('0',4-StrLen(Trim(cast(ax.de as char(4)))))||Trim(cast(ax.de as char(4))),1,2) || ':' ||
> SubStr(StrRepeat('0',4-StrLen(Trim(cast(ax.de as char(4)))))||Trim(cast(ax.de as char(4))),3,2) || '-' ||
> SubStr(StrRepeat('0',4-StrLen(Trim(cast(ax.ate as char(4)))))||Trim(cast(ax.ate as char(4))),1,2) || ':' ||
> SubStr(StrRepeat('0',4-StrLen(Trim(cast(ax.ate as char(4)))))||Trim(cast(ax.ate as char(4))),3,2)
> from L_AGENDA ax
> where ax.codigo_agendamento = a.codigo_agendamento
> and ax.encaixe = a.encaixe) HORA,
>
>
> (select
> StrRepeat('0',2-StrLen(Trim( cast(extractday(ax.data) as char(10)) )))||Trim( cast(extractday(ax.data) as char(10)) ) || ' ' ||
>
> SubStr ( ( case ExtractMonth(ax.data)
> when 1 then 'Janeiro'
> when 2 then 'Fevereiro'
> when 3 then 'Março'
> when 4 then 'Abril'
> when 5 then 'Maio'
> when 6 then 'Junho'
> when 7 then 'Julho'
> when 8 then 'Agosto'
> when 9 then 'Setembro'
> when 10 then 'Outubro'
> when 11 then 'Novembro'
> when 12 then 'Dezembro'
> end ), 1, 3 ) || ' ' ||
>
> '(' || SubStr ( ( case ExtractWeekDay(ax.data)
> when 0 then 'Domingo'
> when 1 then 'Segunda'
> when 2 then 'Terça'
> when 3 then 'Quarta'
> when 4 then 'Quinta'
> when 5 then 'Sexta'
> when 6 then 'Sábado'
> end ), 1, 3 ) || ')'
> from L_AGENDA ax
> where ax.codigo_agendamento = a.codigo_agendamento
> and ax.encaixe = a.encaixe) data_view,
>
> (select ax.data from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) data,
> (select ax.codigo_posto from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) codigo_posto,
> (select ax.codigo_sala from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) codigo_sala,
> (select ax.de from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) de,
> (select ax.ate from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) ate,
> (select ax.flag_status from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) flag_status,
> (select ax.codigo_funcionario from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) codigo_funcionario,
> (select ax.codigo_exame from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) codigo_exame,
> (select ax.codigo_convenio from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) codigo_convenio,
> (select ax.nome from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) nome,
> (select ax.sequencial_sala from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) sequencial_sala,
> (select ax.encaixe from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) encaixe,
> (select ax.observacao from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) observacao,
> (select ax.telefone from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) telefone,
> (select ax.codigo_agendamento from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) codigo_agendamento,
> (select ax.matricula from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) matricula,
>
> (select fs.descricao from L_I_FLAG_STATUS fs where fs.codigo = a.flag_status ) status,
> (select s.descricao from L_SALA s where s.codigo_sala = a.codigo_sala) nome_sala,
> (select u.nome from B_USUARIO u where u.codigo_usuario = a.codigo_funcionario) nome_funcionario,
> (select au.codigo_usuario from L_AGENDA_USO au where au.codigo_agendamento = a.codigo_agendamento and au.encaixe = a.encaixe) em_uso,
> (select ax.RESPOSTA_ESPECIAL from L_AGENDA ax where ax.codigo_agendamento = a.codigo_agendamento and ax.encaixe = a.encaixe) RESPOSTA_ESPECIAL
>
>
> from L_AGENDA a
> where a.CODIGO_POSTO = '01'
> and a.DATA >= '04/14/2009 00:00:00'
> and a.DATA <= '04/14/2009 23:59:59'
> and a.codigo_sala = '01'
>
> order by a.codigo_posto,a.codigo_sala,a.data,a.de,a.ate,a.sequencial_sala,a.encaixe
>
> /// -------> Query B
>
>
> select
>
> SubStr(StrRepeat('0',4-StrLen(Trim(cast(de as char(4)))))||Trim(cast(de as char(4))),1,2) || ':' ||
> SubStr(StrRepeat('0',4-StrLen(Trim(cast(de as char(4)))))||Trim(cast(de as char(4))),3,2) || '-' ||
> SubStr(StrRepeat('0',4-StrLen(Trim(cast(ate as char(4)))))||Trim(cast(ate as char(4))),1,2) || ':' ||
> SubStr(StrRepeat('0',4-StrLen(Trim(cast(ate as char(4)))))||Trim(cast(ate as char(4))),3,2)
> hora,
>
> StrRepeat('0',2-StrLen(Trim( cast(extractday(data) as char(10)) )))||Trim( cast(extractday(data) as char(10)) ) || ' ' ||
>
> SubStr ( ( case ExtractMonth(data)
> when 1 then 'Janeiro'
> when 2 then 'Fevereiro'
> when 3 then 'Março'
> when 4 then 'Abril'
> when 5 then 'Maio'
> when 6 then 'Junho'
> when 7 then 'Julho'
> when 8 then 'Agosto'
> when 9 then 'Setembro'
> when 10 then 'Outubro'
> when 11 then 'Novembro'
> when 12 then 'Dezembro'
> end ), 1, 3 ) || ' ' ||
>
> -- StrRepeat('0',4-StrLen(Trim( cast(extractyear(data) as char(10)) )))||Trim( cast(extractyear(data) as char(10)) ) || ' ' ||
>
> '(' || SubStr ( ( case ExtractWeekDay(data)
> when 0 then 'Domingo'
> when 1 then 'Segunda'
> when 2 then 'Terça'
> when 3 then 'Quarta'
> when 4 then 'Quinta'
> when 5 then 'Sexta'
> when 6 then 'Sábado'
> end ), 1, 3 ) || ')' data_view ,
>
> a.data ,
> a.codigo_posto ,
> a.codigo_sala ,
> a.data ,
> a.de ,
> a.ate ,
> a.flag_status ,
> a.codigo_funcionario ,
> a.codigo_exame ,
> a.codigo_convenio ,
> a.nome ,
> a.sequencial_sala ,
> a.encaixe ,
> a.observacao ,
> a.telefone ,
> a.codigo_agendamento ,
> a.matricula ,
> (select fs.descricao from L_I_FLAG_STATUS fs where fs.codigo = a.flag_status ) status,
> (select s.descricao from L_SALA s where s.codigo_sala = a.codigo_sala) nome_sala,
> (select u.nome from B_USUARIO u where u.codigo_usuario = a.codigo_funcionario) nome_funcionario,
> (select au.codigo_usuario from L_AGENDA_USO au where au.codigo_agendamento = a.codigo_agendamento and au.encaixe = a.encaixe) em_uso,
> a.RESPOSTA_ESPECIAL
>
>
> from L_AGENDA a
>
> where a.CODIGO_POSTO = '01'
> and a.DATA >= '04/14/2009 00:00:00'
> and a.DATA <= '04/14/2009 23:59:59'
> and a.codigo_sala = '01'
>
> order by a.codigo_posto,a.codigo_sala,a.data,a.de,a.ate,a.sequencial_sala,a.encaixe