Search code examples
sql-serverwhere-clauseresponse-time

Query performance differs with just date change in where clause


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.


Solution

  • 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.

    .