Board index » delphi » Question of performance.

Question of performance.


2004-05-19 09:25:16 PM
delphi222
Hi How the folowing query works.
select T1.Cod_Operacao , T1.Cod_Cliente , T1.Nr_Servico,
T1.Ciclo, T1.Nrc,MinMax.MaxVenc,MinMax.MinVenc
into [#Tempo2]
from [#Tempo1] T1
join
(select max(Dt_Vencto) as MaxVenc,min(Dt_Vencto) as MinVenc,
Cod_Operacao , Cod_Cliente , Nr_Servico
from [Faturamento] where Cod_Operacao = @CodOperacao
group by Cod_Operacao,Cod_Cliente,Nr_Servico)
as MinMax
on T1.Cod_Operacao=MinMax.Cod_Operacao
and T1.Cod_Cliente=MinMax.Cod_Cliente and T1.Nr_Servico =MinMax.Nr_Servico
where MaxVenc>=@Hoje
It will calculate all the min and max from the faturamento table
gouped by Cod_Operacao,Cod_Cliente,Nr_Servico
and them join it with the #tempo1 or will calculate only
those records that can be joined with #tempo1.
Regards,
Marcello Dias
 
 

Re:Question of performance.

"Im using SQL SERVER 2000
 

Re:Question of performance.

Marcello Dias writes:
Quote

select T1.Cod_Operacao , T1.Cod_Cliente , T1.Nr_Servico,
T1.Ciclo, T1.Nrc,MinMax.MaxVenc,MinMax.MinVenc
into [#Tempo2]
from [#Tempo1] T1
join
(select max(Dt_Vencto) as MaxVenc,min(Dt_Vencto) as MinVenc,
Cod_Operacao , Cod_Cliente , Nr_Servico
from [Faturamento] where Cod_Operacao = @CodOperacao
group by Cod_Operacao,Cod_Cliente,Nr_Servico)
as MinMax
on T1.Cod_Operacao=MinMax.Cod_Operacao
and T1.Cod_Cliente=MinMax.Cod_Cliente and T1.Nr_Servico
=MinMax.Nr_Servico where MaxVenc>=@Hoje

It will calculate all the min and max from the faturamento table
gouped by Cod_Operacao,Cod_Cliente,Nr_Servico
and them join it with the #tempo1 or will calculate only
those records that can be joined with #tempo1.
Because you didn't specify a qualifier for the Join, it is by default an
INNER join which means you will only get results from faturamento where
there is a match in #Tempo1. Futhermore, your Where clause specifies a
conditon on the MinMax result set (MaxVenc>=@Hoje), and so even if you
changed the join to a LEFT OUTER join, it would still be limited to matches
on #TEMPO1. If you need all records from #TEMPO1 to be selected even when no
match in MinMax, then change to LEFT OUTER join and move the condition
(MaxVenc>=@Hoje) into the join condition instead of the Where clause.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.
 

Re:Question of performance.

"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes:
tHANK yOU.