Quando l’opzione ANSI_NULLS ha effetto su optimizer e prestazioni

Riprendo un argomento già trattato in precedenza, nel post ANSI_NULLS by default: Differences between OLE DB and BDE, per aggiungere alcune considerazioni sul comportamento dell'optimizer (e quindi sulle prestazioni) quando il valore assegnato all'opzione ANSI_NULLS è diverso dal default.

Ipotizziamo di aver rimosso tutte le dipendenze da DB-Library per sostituire BDE con OLE DB in un'applicazione scritta in Delphi.

Per mantenere la compatibilità degli operatori uguale a (=) e diverso da (<>) nel confronto con il valore NULL, subito dopo aver stabilito la connessione con SQL Server abbiamo impostato a OFF l'opzione ANSI_NULLS (SET ANSI_NULLS OFF).

Dopo questa impostazione gli utenti lamentano un rallentamento nell'esecuzione delle query.

Ipotizziamo il seguente scenario: un utente richiede l'estrazione di alcuni dati relativi agli ordini di vendita, la tabella dbo.OrderHeader memorizza le testate degli ordini cliente, mentre la tabella dbo.OrderDetail memorizza le righe. Disponiamo quindi delle seguenti strutture dati:

  • Tabella dbo.OrderHeader (250.000 righe) con la seguente struttura:

dbo.OrderHeader

  • Tabella dbo.OrderDetail (250.000 righe ) con la seguente struttura:

dbo.OrderDetail

  • Query di estrazione dati (semplificata):
Select count(*) From dbo.OrderHeader OH Left Outer Join dbo.OrderDetail OD ON OD.OrderHeaderID = OH.OrderID

Where ( OH.CustomerID = @P1 ) and ( OD.OrderHeaderID = @P2 ) 

Confrontiamo ora i piani di esecuzione della query quando viene eseguita su una connessione con ANSI_NULLS = ON (default per le connessioni OLE DB - SSMS) e quando viene eseguita su una connessione con ANSI_NULLS = OFF (default per le connessioni DB-Library).

- Piano di esecuzione della query eseguita con ANSI_NULLS = OFF:

Execution Plan ANSI_NULLS = ON

Table 'OrderHeader'. Scan count 1, logical reads 2607, physical reads 0, read-ahead reads 0.

Table 'OrderDetail'. Scan count 1, logical reads 899, physical reads 0, read-ahead reads 0.

 

- Piano di esecuzione della query eseguita con ANSI_NULLS = ON:

Execution Plan ANSI_NULLS = ON

Table 'OrderDetail'. Scan count 1, logical reads 899, physical reads 0, read-ahead reads 0.

Table 'OrderHeader'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0.

 

Nel caso di ANSI_NULLS = OFF non vengono utilizzati gli indici, ma viene eseguita una scansione delle tabelle dbo.OrderHeader e dbo.OrderDetail con un conseguente aumento degli accessi alle pagine di memoria.

Grazie al prezioso contributo di Davide Mauri, osserviamo che:

"Le query eseguite sulle due connessioni non sono più equivalenti se il comportamento dell'operatore di uguaglianza è diverso dal default, quindi l'optimizer non può applicare nel caso di ANSI_NULLS = OFF, le stesse ottimizzazioni che può invece applicare se ANSI_NULL = ON.

Con una Left Outer Join si possono comunque ottenere dei valori NULL e se l'operatore uguale a (=) deve dare un risultato quando applicato su valori NULL, ne deriva che il piano di esecuzione deve essere necessariamente diverso, con il risultato che in questo caso gli indici non vengono più utilizzati.

 

Published giovedì 26 giugno 2008 23.50 by sgovoni

Comments

No Comments