Indicizzazione dei vincoli FOREIGN KEY

Abbiamo utilizzato la stored procedure usp_create_index_on_foreign_key per determinare e creare gli indici mancanti per i vincoli FOREIGN KEY in essere su una determinata tabella o su un determinato schema.

Lo scenario illustrato di seguito permetterà di osservare il comportamento dell’ottimizzatore durante la fase di ricerca del piano di esecuzione migliore per risolvere una query che faccia uso di JOIN per l’estrazione dei dati di due tabelle relazionate tra loro attraverso una chiave primaria (Primary Key) ed una chiave esterna (Foreign Key).

Confronteremo i piani di esecuzione utilizzati ed il numero di accessi di I/O con e senza indici.

Scenario

La nostra azienda ha un database SQL Server 2005 che contiene le tabelle dbo.OrderHeader e dbo.OrderDetail utilizzate rispettivamente per memorizzare testate e righe degli ordini di vendita ricevuti dai propri clienti. Gli utenti utilizzano una query di estrazione dati applicando un filtro per numero ordine.

Utilizziamo il seguente script T-SQL per la generazione delle tabelle dbo.OrderHeader e dbo.OrderDetail:

-- 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)
)

GO

-- Drop table Order
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)
)

GO

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

Inseriamo alcuni dati di prova:

DECLARE @i AS int,
        @scope_id AS int

SET @i = 0
SET @scope_id = 0

WHILE (@i < 20000)
BEGIN
  SET NOCOUNT ON

  INSERT INTO dbo.OrderHeader DEFAULT VALUES

  SELECT @scope_id = SCOPE_IDENTITY()

  -- 1
  INSERT INTO dbo.OrderDetail
  (OrderHeaderID,
   ProductID,
   UnitPrice)   
  SELECT
    @scope_id,
    (@i * 2)+1,
    ((@i * 2)+1)/2

  -- 2
  INSERT INTO dbo.OrderDetail
  (OrderHeaderID,
   ProductID,
   UnitPrice)
  SELECT
    @scope_id,
    (@i * 3)+1,
    ((@i * 3)+1)/3

  -- 3
  INSERT INTO dbo.OrderDetail
  (OrderHeaderID,
   ProductID,
   UnitPrice)
  SELECT
    @scope_id,
    (@i * 4)+1,
    ((@i * 4)+1)/4
 
  SET @i = (@i + 1)

  SET NOCOUNT OFF
END

Utilizziamo ad esempio la seguente query (Q1) per consultare alcune informazioni relative all’ordine numero 'SO9000':

select
  OH.OrderDATE,
  OH.OrderNUMBER,
  OH.CustomerID,
  OD.ProductID
from
  dbo.OrderHeader OH
join
  dbo.OrderDetail OD ON OD.OrderHeaderID = OH.OrderID
where
  OH.OrderNUMBER = 'SO9000'

La Foreign Key FK_OrderHeaderID non è indicizzata, il piano di esecuzione utilizzato per soddisfare la query (Q1) è il seguente:

click to enlarge

Si osservano i due Clustered Index Scan rispettivamente sulle tabelle dbo.OrderHeader e dbo.OrderDetail con le seguenti statistiche di I/O:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 50 ms.

(3 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OrderDetail'. Scan count 1, logical reads 218, physical reads 0, read-ahead reads 224, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OrderHeader'. Scan count 1, logical reads 211, physical reads 1, read-ahead reads 212, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 90 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Utilizziamo ora la stored procedure usp_create_index_on_foreign_key per generare l’indice (NONCLUSTERED) mancante sul campo OrderHeaderID della tabella dbo.OrderDetail:

EXEC dbo.usp_create_index_on_foreign_key 1, N'dbo', N'OrderDetail', 0;

usp_create_index_on_foreign_key : Generazione indici...
 
Esecuzione di: CREATE NONCLUSTERED INDEX [IDX__OrderDetail_OrderHeaderID] ON dbo.OrderDetail(OrderHeaderID) completata correttamente.

usp_create_index_on_foreign_key : Generazione indici completata.

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

La Foreign Key FK_OrderHeaderID è ora indicizzata, eseguiamo di nuovo la query (Q1) e confrontiamo il nuovo piano di esecuzione e le statistiche di I/O:

click to enlarge

Osserviamo ora che il Clustered Index Scan sulla tabella dbo.OrderDetail è stato sostituito con un Index Seek sull’indice IDX__OrderDetail_OrderHeaderID individuato e creato dalla stored procedure. Le statistiche di I/O denotano una sensibile diminuzione delle letture sulla tabella di dettaglio ordini i cui dati vengono acceduti attraverso l’indice:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 5 ms.

(3 row(s) affected)
Table 'OrderDetail'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 18 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

 

Conclusioni 

Creare un indice su una chiave esterna rappresenta spesso un'operazione utile in quanto:

  • Le modifiche apportate ai vincoli PRIMARY KEY vengono confrontate con i vincoli FOREIGN KEY nelle tabelle correlate
  • Le colonne chiave primaria vengono citate nei criteri di JOIN quando per riunire i dati di tabelle diverse si confrontano rispettivamente le colonne nel vincolo FOREIGN KEY con le colonne nel vincolo PRIMARY KEY

In questi casi la presenza di un indice consente all’engine di SQL Server di trovare rapidamente i dati correlati nella tabella che contiene la chiave esterna.

 

Published mercoledì 26 novembre 2008 23.46 by sgovoni

Comments

No Comments