La query viene eseguita lentamente con l’espressione della data, ma veloce con la stringa letterale

Sto facendo funzionare una domanda con la sotto condizione in SQL Server 2008.

Where FK.DT = CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) as DATE) 

La query richiede un’eternità per funzionare con le condizioni sopra descritte, ma se basta dirlo

 Where FK.DT = '2013-05-01' 

funziona alla grande in 2 minuti. FK.DT chiave FK.DT contiene i valori dei soli dati di avvio del mese.

Qualsiasi aiuto, sono solo all’oscuro del perché questo sta accadendo.

Questo potrebbe funzionare meglio:

 Where FK.DT = cast(getdate() + 1 - datepart(day, getdate()) as date) 

A meno che non si stia utilizzando il flag di traccia 4199, è presente un bug che influisce sulle stime di cardinalità. Al momento della scrittura

 SELECT DATEADD(m, DATEDIFF(m, getdate(), 0), 0), DATEADD(m, DATEDIFF(m, 0, getdate()), 0) 

ritorna

 +-------------------------+-------------------------+ | 1786-06-01 00:00:00.000 | 2013-08-01 00:00:00.000 | +-------------------------+-------------------------+ 

Il bug è che il predicato nella domanda usa la prima data piuttosto che la seconda quando si ottengono le stime di cardinalità. Quindi per la seguente configurazione.

 CREATE TABLE FK ( ID INT IDENTITY PRIMARY KEY, DT DATE, Filler CHAR(1000) NULL, UNIQUE (DT,ID) ) INSERT INTO FK (DT) SELECT TOP (1000000) DATEADD(m, DATEDIFF(m, getdate(), 0), 0) FROM master..spt_values o1, master..spt_values o2 UNION ALL SELECT DATEADD(m, DATEDIFF(m, 0, getdate()), 0) 

Query 1

 SELECT COUNT(Filler) FROM FK WHERE FK.DT = CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) AS DATE) 

Piano 1

Stima che il numero di righe corrispondenti sarà 100.000. Questo è il numero che corrisponde alla data '1786-06-01' .

Ma entrambe le seguenti domande

 SELECT COUNT(Filler) FROM FK WHERE FK.DT = CAST(GETDATE() + 1 - DATEPART(DAY, GETDATE()) AS DATE) SELECT COUNT(Filler) FROM FK WHERE FK.DT = CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) AS DATE) OPTION (QUERYTRACEON 4199) 

Dare questo piano

Piano 2

A causa delle stime di cardinalità molto più accurate, il piano ora esegue solo una ricerca per indice anziché una scansione completa.

Nella maggior parte dei casi, probabilmente si applica il sottostante. In questo caso specifico , si tratta di un bug di ottimizzazione che coinvolge DATEDIFF . Dettagli qui e qui . Ci scusiamo per aver dubitato di t-clausen.dk, ma la sua risposta semplicemente non era una soluzione intuitiva e logica senza conoscere l’esistenza del bug.

Quindi supponendo che DT sia effettivamente DATE e non qualcosa di stupido come VARCHAR o – peggio ancora – NVARCHAR – questo è probabilmente dovuto al fatto che si ha un piano memorizzato nella cache che ha usato un valore di data molto diverso quando è stato eseguito per la prima volta, quindi ha scelto un piano che si occupava di dati tipici molto diversi distribuzione. Ci sono modi in cui puoi superare questo:

  1. Forza una ricompilazione del piano aggiungendo OPTION (RECOMPILE) . Potrebbe essere necessario farlo solo una volta, ma il piano che si ottiene potrebbe non essere ottimale per altri parametri. Lo svantaggio di lasciare sempre l’opzione è che si paga il costo di compilazione ogni volta che viene eseguita la query. In molti casi questo non è sostanziale e spesso scelgo di pagare un piccolo costo noto piuttosto che a volte una query che viene eseguita leggermente più veloce e altre volte che viene eseguita estremamente lentamente.

     ... WHERE FK.DT = CAST(... AS DATE) OPTION (RECOMPILE); 
  2. Usa prima una variabile (non c’è bisogno di un CONVERT esplicito per DATE qui, e usa MONTH invece di stenografia come m – quell’abitudine può portare a un comportamento davvero divertente se non hai memorizzato ciò che fanno tutte le abbreviazioni, per esempio scommetto y e w non producono i risultati che ti aspetteresti):

     DECLARE @dt DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0); ... WHERE FK.DT = @dt; 

    Tuttavia in questo caso potrebbe accadere la stessa cosa: lo sniffing dei parametri potrebbe forzare un piano non ottimale da utilizzare per diversi parametri che rappresentano diversi dati di inclinazione.

  3. È anche ansible sperimentare OPTION (OPTIMIZE FOR (@dt = '2013-08-01')) , che costringerebbe SQL Server a considerare questo valore anziché quello che è stato utilizzato per compilare il piano memorizzato nella cache, ma ciò richiederebbe un stringa letterale con hard-coded, che ti aiuterà solo per il resto di agosto, a quel punto dovresti aggiornare il valore. Potresti anche prendere in considerazione OPTION (OPTIMIZE FOR UNKNOWN) .