sql server query too long

enrique_aeo
enrique_aeo used Ask the Experts™
on
Hi experts, I have a query that is very slow, I attached the code tables and indexes that have created
script.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Take a look at your execution plan, you'll see no index is used.

Why?  Applying a function on your date.  Look at following link for your date-issue
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don't-be-scared-and-do-it-right-the-first-time.html

On your second table you have no index.

Author

Commented:
can you help me with transact sql code
Commented:
select G.Fecha 
,Saldo = (G.SaldoDolares * TC.Valor) + G.SaldoSoles
from 
(select Fecha
,SaldoDolares = sum(case when substring(SD.NroCredito,12,1) = '2' then CS.Saldo else 0 end)
,SaldoSoles = sum(case when substring(SD.NroCredito,12,1) = '2' then 0 else CS.Saldo end)
from dbo.SaldosDiarios SD
-- ** No function on index-column but no problem with time **
where SD.dFecha >= '2012-01-31 00:00:00.000' 
and SD.dFecha <= '2012-01-31 23:59:59.997' 
and SD.NroCredito like '________525%' -- producto en especifico
group by CS.dFecha)G
inner join dbo.TipCamb TC
on datediff(d,TC.Fecha,G.Fecha)=0
-- ** if there will be an index this addition  condition can force using index to limit the reads
where TC.dFecha >= '2012-01-31 00:00:00.000' 
and TC.dFecha <= '2012-01-31 23:59:59.997' 

Open in new window


What I find odd (I think error) is you group by a datetime (group by CS.dFecha ) but the result you get you join with another datetime , but only on the date-part (on datediff(d,TC.Fecha,G.Fecha) = 0 ).

I think your date should be separated from your time-part in your both tables, maybe by a computed column with an index on that http://www.mssqltips.com/sqlservertip/1682/using-computed-columns-in-sql-server-with-persisted-values/

Author

Commented:
is how to create computed columns, but you mean q columns
Commented:
<<but you mean q columns>>
Don't get that sentence

This link give the limitations for indexing a computed column
http://msdn.microsoft.com/en-us/library/ms189292.aspx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial