UGISS Community

Ciao a tutti,

sto sviluppando un'applicazione che (attualmente) utilizza OLE DB per connettersi a SQL Server (2000). Originariamente la stessa applicazione utilizzava DB-Library per connettersi a SQL Server.

Per mantenere compatibilità degli operatori "=", ">=", "<=", ecc... nel confronto con il valore NULL, subito dopo aver stabilito la connessione con SQL Server ho impostato a OFF l'opzione ANSI_NULLS.

Dopo questa impostazione gli utenti lamentano un rallentamento nell'esecuzione delle query. Ho utilizzato SQL Profiler e Query Analyzer per confrontare i plan di esecuzione di una query con ANSI_NULLS rispettivamente a ON e OFF. Ho confrontato anche le letture effettuate su disco, questi i risultati:

Query di esempio:

sp_executesql N'SELECT A.idA idA, Sum(B.Qta) Qta, Max(B.Date) Date FROM Table1 B Left Outer Join Table2 A On B.idA = A.id WHERE (B.idE= @P1) And (A.idA= @P2) GROUP BY A.idA', N'@P1 int, @P2 int', 35, 5624

Con ANSI_NULLS impostato a OFF (impostazione attuale):

Warning: Null value is eliminated by an aggregate or other SET operation.
Table 'Table1'. Scan count 1, logical reads 7071, physical reads 0, read-ahead reads 0.
Table 'Table2'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0.

Con ANSI_NULLS impostato a ON:

Warning: Null value is eliminated by an aggregate or other SET operation.
Table 'Table1'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0.
Table 'Table2'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Con ANSI_NULLS impostato a ON 6 letture logiche invece di 7071.

Con ANSI_NULLS impostato a OFF viene eseguita una scansione completa delle tabelle senza utilizzo degli indici.

Se nella query aggiungo (B.idE IS NOT NULL) and (A.idA IS NOT NULL) osservo l'utilizzo degli indici anche nel caso ANSI_NULLS sia impostato a OFF.

In che modo l'opzione ANSI_NULLS condiziona l'ottimizzatore di SQL Server ? Devo davvero aggiungere IS NOT NULL in tutti i casi simili a questo ?

Grazie

Sergio

Ciao Sergio

mmm...o fatto prove con un 2005, e questo non avviene. Ho fatto anche un prova al volo (su una tabella molto piccola) su un SQL 2000 SP4 build 2178 (quindi con qualche hotfix applicata )

if (object_id('dbo.t1') is not null) drop table dbo.t1
go

CREATE TABLE t1 (a INT NULL)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
INSERT INTO t1 values (2)
INSERT INTO t1 values (3)
INSERT INTO t1 values (4)
INSERT INTO t1 values (5)
INSERT INTO t1 values (6)
INSERT INTO t1 values (7)
INSERT INTO t1 values (8)
INSERT INTO t1 values (9)
GO

create nonclustered index ixnc on t1(a)
go

SET ANSI_NULLS on
go

declare @a int
set @a = 1

exec sp_executesql N'select * from t1 where a = @a', N'@a int', @a
go

SET ANSI_NULLS off
go

declare @a int
set @a = 1

exec sp_executesql N'select * from t1 where a = @a', N'@a int', @a
go

ma non sono riuscito a replicare il problema.

Riesci a creare uno script come quello che ho postato che permetta di fare prove comparabili?

Un'altra cosa: hai svuotato la cache dei piani di esecuzione, per esser sicuro che non ci sia in cache un piano di esecuzione relativo all'impostazione SET ANSI NULL impostata o meno che viene ri-utilizzato e quindi impedendo il ricalcolo dell'execution plan ottimale? 

Ultima cosa: ho visto il tuo post su Sql Server Central. La risposta che ti ha dato SSC Rookie è una ca***a pazzesca. Il numero dei logical reads non varia in funzione di cosa c'è nel buffer pool....quello che varia sono i physical reads, non i logical reads. I logical reads rappresentano il numero di pagine alla quale SQL Server ha dovuto accedere per risolvere la query, e come puoi ben immaginare questo numero non varia in funzione della cache, ma in funzione del piano di esecuzione della frammentazione interna dei dati.

Ciauz!

Davide Ciao,

dmauri:

Riesci a creare uno script come quello che ho postato che permetta di fare prove comparabili?

Sono riuscito a creare uno script con cui riprodurre il problema, è questo:

/*
  Test opzione ANSI_NULLS
*/

-- Drop table OrderDetail
IF (OBJECT_ID('OrderDetail', 'U') IS NOT NULL)
  DROP TABLE dbo.OrderDetail
GO

-- Create table OrderDetail
CREATE TABLE dbo.OrderDetail
(OrderDetailID INT IDENTITY(1, 1) NOT NULL,
 OrderHeaderID INT NOT NULL,
 ProductID INT NOT NULL,
 RigNumber AS (OrderDetailID * 2) ,
 UnitPrice MONEY DEFAULT 0 NOT NULL
 PRIMARY KEY(OrderDetailID)
)

-- Drop table OrderHeader
IF (OBJECT_ID('OrderHeader', 'U') IS NOT NULL)
  DROP TABLE dbo.OrderHeader
GO

-- Create table OrderHeader
CREATE TABLE dbo.OrderHeader
(OrderID INT IDENTITY(1, 1) NOT NULL,
 OrderDATE DATETIME DEFAULT GETDATE() NOT NULL,
 OrderNUMBER AS (ISNULL(N'SO' + CONVERT([nvarchar](23), [OrderID], 0), N'*** ERROR ***')),
 CustomerID INT DEFAULT 1 NOT NULL,
 ShipName VARCHAR(20) DEFAULT 'Name',
 ShipAddress VARCHAR(40) DEFAULT 'Address',
 ShipVia VARCHAR(40) DEFAULT 'Via',
 ShipCity VARCHAR(20) DEFAULT 'City',
 ShipRegion VARCHAR(20) DEFAULT 'Region',
 ShipPostalCode VARCHAR(20) DEFAULT 'Postal code',
 ShipCountry VARCHAR(20) DEFAULT 'Country'
 PRIMARY KEY(OrderID)
)

-- ALTER TABLE ADD CONSTRAINT
ALTER TABLE [dbo].[OrderDetail] WITH CHECK ADD CONSTRAINT [FK_OrderHeaderID]
FOREIGN KEY ([OrderHeaderID]) REFERENCES [dbo].[OrderHeader] ([OrderID])

-- CREATE NONCLUSTERED INDEX
CREATE NONCLUSTERED INDEX [IDX_OrderHeader_CustomerID] ON dbo.OrderHeader
(
 [CustomerID] ASC
)

DECLARE @i AS INT

SET @i = 0
--219131
WHILE (@i < 250000)
BEGIN
  SET NOCOUNT ON

  -- Insert OrderHeader
  INSERT INTO dbo.OrderHeader DEFAULT VALUES

  -- Insert OrderDetail
  INSERT INTO dbo.OrderDetail
  (
    OrderHeaderID,
    ProductID,
    UnitPrice
  )
   
  SELECT
    (
      SELECT
        MAX(OrderID)
      FROM
        dbo.OrderHeader
    ),
    (@i * 2)+1,
    ((@i * 2)+1)/2

  SET @i = (@i + 1)

  SET NOCOUNT ON
END

SET STATISTICS IO ON

-- TEST ANSI_NULLS

SET ANSI_NULLS ON
go

DECLARE @P1 INT,
        @P2 INT

SET @P1 = 1
SET @P2 = 1100

EXEC sp_executesql N'Select OH.OrderID, Max(OH.OrderDATE), OH.OrderNUMBER, OH.CustomerID, OH.ShipName, OH.ShipAddress, OH.ShipVia, OH.ShipCity, OH.ShipRegion, OH.ShipPostalCode, OH.ShipCountry, SUM(OD.UnitPrice) From dbo.OrderHeader OH Left Outer Join dbo.OrderDetail OD ON OD.OrderHeaderID = OH.OrderID Where (OH.CustomerID = @P1) and (OD.OrderHeaderID = @P2) GROUP BY OH.OrderID, OH.OrderNUMBER, OH.CustomerID, OH.ShipName, OH.ShipAddress, OH.ShipVia, OH.ShipCity, OH.ShipRegion, OH.ShipPostalCode, OH.ShipCountry', N'@P1 int, @P2 int', @P1, @P2
go

DBCC DROPCLEANBUFFERS

SET ANSI_NULLS OFF
go

DECLARE @P1 INT,
        @P2 INT

SET @P1 = 1
SET @P2 = 1100

EXEC sp_executesql N'Select OH.OrderID, Max(OH.OrderDATE), OH.OrderNUMBER, OH.CustomerID, OH.ShipName, OH.ShipAddress, OH.ShipVia, OH.ShipCity, OH.ShipRegion, OH.ShipPostalCode, OH.ShipCountry, SUM(OD.UnitPrice) From dbo.OrderHeader OH Left Outer Join dbo.OrderDetail OD ON OD.OrderHeaderID = OH.OrderID Where (OH.CustomerID = @P1) and (OD.OrderHeaderID = @P2) GROUP BY OH.OrderID, OH.OrderNUMBER, OH.CustomerID, OH.ShipName, OH.ShipAddress, OH.ShipVia, OH.ShipCity, OH.ShipRegion, OH.ShipPostalCode, OH.ShipCountry', N'@P1 int, @P2 int', @P1, @P2

dmauri:

Un'altra cosa: hai svuotato la cache dei piani di esecuzione, per esser sicuro che non ci sia in cache un piano di esecuzione relativo all'impostazione SET ANSI NULL impostata o meno che viene ri-utilizzato e quindi impedendo il ricalcolo dell'execution plan ottimale?

Dopo la prima chiamata a sp_executesql ho eseguito DBCC DROPCLEANBUFFERS, ma ho provato anche a ricostruire gli indici delle due tabelle (il rebuild dell'indice dovrebbe forzare la free della cache dei piani di esecuzione. Dico bene ?) con:

DBCC DBREINDEX(OrderHeader)
UPDATE STATISTICS OrderHeader

DBCC DBREINDEX(OrderDetail)
UPDATE STATISTICS OrderDetail

Con ANSI_NULLS impostato a ON ottengo:

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 2, read-ahead reads 0.

Con ANSI_NULLS impostato a OFF ottengo:

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.

dmauri:

Ultima cosa: ho visto il tuo post su Sql Server Central....

Un po' l'avevo immaginato, grazie Davide del chiarimento .

Grazie ancora.

Sergio

Ok perfetto sono riuscito a replicare il problema, anche su SQL Server 2005.

La cosa è piuttosto strana. Da una prima analisi sembra che impostanto l'opzione ANSI_NULL a OFF l'optimizer non sia più un grado di capire che la clausola

OD.OrderHeaderID = @P2

può essere invece sostituita dall'equivalente

OH.OrderId = @P2

e quindi non utilizza l'indice dove invece potrebbe farlo.

Tutto questo non avviene se invece l'opzione ANSI_NULL è impostata ad ON, dove questa sostituzione avviene in automatico.

Per fare un pò di test o semplificato la query, utilizzando questa:

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 ) 

Per rimettere a posto la query in modo che funzioni con entrambi i settaggi, infatti, è sufficiente riscriverla così:

Select  
        count(*)
From    dbo.OrderHeader OH
        Left Outer Join dbo.OrderDetail OD ON OD.OrderHeaderID = OH.OrderID
Where   ( OH.CustomerID = @P1 )
        and  (OH.OrderId = @P2)

Ora sto indagando sul perchè accade ciò....ho chiesto lumi, appena so qualcosa di condivisibile ti aggiorno. 

Davide,

ho trovato alcune considerazioni sulle prestazioni (per le join) scritte da Itzik Ben-Gan (nel libro "Transact SQL" Mondadori Informatica), riporto il testo originale:

Criteri di filtraggio completamente qualificati

"L'ottimizzatore di SQL Server è un componente molto brillante, ma a volte ha bisogno di un aiuto. Può sembrare ovvio che se A=B e B=C, allora A=C, ma non lo è sempre per l'ottimizzatore...."

Itzik consiglia di applicare sempre il criterio a entrambe le tabelle, la query verrà portata a termine con molto meno I/O.

Il nostro caso è un po' diverso in quanto c'è anche l'opzione ANSI_NULLS però hai notato che riscrivendo la query con (OH.OrderId = @P2) l'ottimizzatore sceglie l'indice e non la scansione (in entrambi i casi) ... quindi la query ottimale potrebbe essere:

Select  
        count(*)
From    dbo.OrderHeader OH
        Left Outer Join dbo.OrderDetail OD ON OD.OrderHeaderID = OH.OrderID
Where   ( OH.CustomerID = @P1 )
        and  (OH.OrderId = @P2) and ( OD.OrderHeaderID = @P2 )

in cui i criteri di filtraggio sono completamente qualificati.

Cosa ne pensi ?

Ciao!

Si, ma questo era vero fino alla versione 2000 (stai infatti parlando del libro "Advanced Transact-SQL", non "Inside SQL Server", giusto?. Dalla versione 2005 l'optimizer dovrebbe essere molto più furbo. Ed in effetti lo è perchè nella situazione "normale" è in grado di capire che nel nostro caso c'è un proprietà transivita sulle colonne e quindi la può usare.

La cosa strana è che se si disabilita l'impostazione ANSI_NULL...questo non riescie più a farlo, e considerando che le colonne in gioco sono entrambe dichiarate NOT NULL, questo comportamente non dovrebbe avvenire.

Io invece non capisco la logica che può esserci nella query di partenza.

Perché usare un left join se poi si applica comunque un filtro (che non riguarda un test su null o != null) sulla parte destra del join?

Ciao, Alessandro

Si, mi riferivo al libro "Advanced Transact-SQL" e l'esempio è relativo alla versione 2000.

Come volevasi dimostrare l'optimizer è più furbo di noi in questo caso.

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

Per provare puoi eseguire la query semplificata (quella con il count), cancellando - prima di eseguire la query - la riga con codice 1100 ed utilizzando come valore del parametro @P2 il valore null.

Come puoi ben immaginare con una left join si possono cmq ottenere dei valori null e se l'operatore "=" deve dare un risultato quando applicato su valori null, ne deriva che la clausola su OD.OrderHeaderID non è più interscambiabile con OH.OrderID...e quindi il piano di esecuzione deve essere necessariamente diverso, con il risultato che in questo caso gli indici non vengono più utilizzati.

dmauri:

Per provare puoi eseguire la query semplificata (quella con il count), cancellando - prima di eseguire la query - la riga con codice 1100 ed utilizzando come valore del parametro @P2 il valore null.

Ho provato… ed effettivamente la query restituisce risultati diversi a seconda dell’impostazione di ANSI_NULLS. Con ANSI_NULLS = ON non vengono restituite righe, con ANSI_NULLS = OFF viene restituita una riga perché l’operatore “=” deve dare un risultato anche se confrontato con il valore null. Come volevasi dimostrare.

Dopo la tua spiegazione il comportamento dell’optimizer è chiarissimo , affascinante.

Sergio