I have this SQL
SELECT
ABS(a.cantidad - ISNULL(b.cantidad, 0)) as cantidad,
a.codarticulo, a.codalmacen, a.codarticulotallaje
FROM
(SELECT
ABS(ISNULL(SUM(cantidad), 0)) AS cantidad,
movimientos.codarticulo, codalmacen,
movimientos.codarticulotallaje
FROM movimientos
WHERE
(fecha > '12/02/2015') AND (fecha < '12/05/2015 10:29:30')
AND (altabaja = 2) AND
((tipodocumento = 3) OR (tipodocumento = 4)) AND (codalmacen = '3')
GROUP BY
movimientos.codarticulo, movimientos.codalmacen,
movimientos.codarticulotallaje) as a
LEFT OUTER JOIN
(SELECT
ABS(ISNULL(SUM(cantidad),0)) as cantidad,
movimientos.codarticulo, codalmacen, codarticulotallaje
FROM movimientos
WHERE (fecha > '12/02/2015') AND (fecha < '12/05/2015 10:29:30')
AND (codalmacen = '3') AND (altabaja = 1)
AND ((tipodocumento = 5) OR (tipodocumento = 6))
AND (cantidad > 0)
GROUP BY
movimientos.codarticulo,
codalmacen, codarticulotallaje ) as b
on a.codarticulo = b.codarticulo AND
a.codarticulotallaje = b.codarticulotallaje
The date time is dd/MM/yyyy
format the table movimientos have around 450000 rows.
If my date is less than 12/02/2015 in both WHERE (fecha > 12/02/2015)
queries. Or over 05/05/2015 the query takes from 200 ms to 2 secs to answer which is ok.
However if the date is like in the example it takes over a min to answer.
I personally do not know what the reason could be.
Generally, as a rule of thumb, such difference means that SQL Server is selecting different execution plan for every set of criteria. And (again as a rule of thumb) this means that it doesn't have a good index to start with (or there are no indexes at all).
Start with examining execution plans, look for table scans. Those are bad.
And the most obvious choice: it seems there's no index for column fetcha
. Create it and you'll see the difference.
.