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
>