help, can't optimize this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help, can't optimize this query

Author

Topic

photogranger
Starting Member

2 Posts

Posted - 2010-01-26 : 19:50:43

Help, can't do this in the right ways it take too long

select ClienteID
from transaccion
Where ClienteID in ( select ClienteID from Transaccion where TransaccionFechaGuia between @SemanaActualInicio and @semanaActualFin and transaccionPais=@Pais) -- Semana Actual
and ClienteID in ( select ClienteID from Transaccion where TransaccionFechaGuia >= @SemanaInicioUlt13 and TransaccionFechaGuia <=dateadd(day,-1,@SemanaActualInicio) and transaccionPais=@Pais) -- Ultimas 13 semanas
and ClienteID not in ( select ClienteID from Transaccion where TransaccionFechaGuia <= dateadd(day,-1,@SemanaInicioUlt13) and transaccionPais=@Pais) -- Ultimas 13 semanas

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 20:22:41

Try this. Please post back current time and the time of this suggestion

SELECT		ClienteID
FROM		Transaccion
WHERE		TransaccionPais = @Pais
GROUP BY	ClienteID
HAVING		MAX(CASE WHEN TransaccionFechaGuia BETWEEN @SemanaActualInicio AND @SemanaActualFin THEN 1 ELSE 0 END) = 1
		AND MAX(CASE WHEN TransaccionFechaGuia BETWEEN @SemanaInicioUlt13 AND DATEADD(DAY, -1, @SemanaActualInicio) THEN 1 ELSE 0 END) = 1
		AND MAX(CASE WHEN TransaccionFechaGuia <= DATEADD(DAY, -1, @SemanaInicioUlt13) THEN 1 ELSE 0 END) = 0

N 56°04'39.26"
E 12°55'05.63"

photogranger
Starting Member

2 Posts

Posted - 2010-01-27 : 09:28:12

Thank you Peso, execution time goes down from 5-10 minutes to 17 seconds

thanks a lot, now i'm trying to solve if the result is correct.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-27 : 09:55:30

30 times faster? Not bad at all...

Make sure you have a NONCLUSTERED INDEX over TransaccionPais column.
Are you using SQL Server 2005 or newer?

CREATE NONCLUSTERED INDEX IX_Pais ON Transaccion (TransaccionPais, ClienteID)
INCLUDE (TransaccionFechaGuia)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources