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