La fiducia delle constraints e come questo puo’ impattare i vostri piani d’esecuzione
Tutti noi sappiamo che i constraint sono un ottimo modo per assicurarci l’aderenza del nostro schema alle business rules. Quello che e’ meno noto e’ che SQL Server, dietro le quinte, utilizza i nostri constraints durante la scelta del piano d’esecuzione ottimale.
Per vederlo all’opera creiamo tre tabelle con i relativi constaints:
CREATE DATABASE Demo;
GO
USE Demo;
GO
--2000
CREATE TABLE Demo2000(id INT IDENTITY(1,1) PRIMARY KEY, txt NVARCHAR(255), dt DATETIME)
GO
ALTER TABLE [dbo].[Demo2000] WITH CHECK
ADD CONSTRAINT [CK_Demo2000_dt] CHECK (([dt]>='2000-01-01' AND [dt]<'2001-01-01'))
GO
--2001
CREATE TABLE Demo2001(id INT IDENTITY(1,1) PRIMARY KEY, txt NVARCHAR(255), dt DATETIME)
GO
ALTER TABLE [dbo].[Demo2001] WITH CHECK
ADD CONSTRAINT [CK_Demo2001_dt] CHECK (([dt]>='2001-01-01' AND [dt]<'2002-01-01'))
GO
--2002
CREATE TABLE Demo2002(id INT IDENTITY(1,1) PRIMARY KEY, txt NVARCHAR(255), dt DATETIME)
GO
ALTER TABLE [dbo].[Demo2002] WITH CHECK
ADD CONSTRAINT [CK_Demo2002_dt] CHECK (([dt]>='2002-01-01' AND [dt]<'2003-01-01'))
GO
Ciascuna tabella rappresenta un anno di record. Inseriamo alcuni valori nelle tabelle (ovviamente tenendo conto dei constraints):
INSERT INTO Demo2000(txt, dt) VALUES('a', '2000-03-30');
GO 100
INSERT INTO Demo2000(txt, dt) VALUES('b', '2000-05-10');
GO 100
INSERT INTO Demo2001(txt, dt) VALUES('a', '2001-03-30');
GO 100
INSERT INTO Demo2002(txt, dt) VALUES('a', '2002-03-30');
GO 100
Ora supponiamo che per comodita’ venga creata questa vista riepilogativa:
CREATE VIEW DemoView
AS
SELECT id, txt, dt FROM Demo2000
UNION ALL
SELECT id, txt, dt FROM Demo2001
UNION ALL
SELECT id, txt, dt FROM Demo2002
Le query di interrogazione utilizzeranno i constraints creati? Per controllarlo basta effettuare una semplice query guardando il piano d’esecuzione:
SELECT * FROM DemoView WHERE dt = '2001-03-30'
Come si vede, SQL Server sa che il risultato della query sara’ soddisfatto solo dalla tabella Demo2001 e quindi non effettua scansione delle altre due rimanenti (Demo2000 e Demo2002). Questa operazione riduce il carico di IO necessario per soddisfare la query. Quindi SQL Server utilizza attivamente le nostre constraints per ottimizzare il piano d’esecuzione.
Ora, pero’, supponiamo che per risparmiare spazio sul DB decidiamo di svuotare la tabella del 2000. Prima di farlo esportiamo i dati con BCP in modo da portere, in seguito, reimportare i dati in caso diventasse necessario. La sintassi e’ la seguente:
bcp Demo.dbo.Demo2000 out Demo2000.dat -S <your instance> -T –N
Ove al posto di <your instance> dovete ovviamente mettere la vostra instanza. Ora svuotare la tabella del 2000 e’ semplice e veloce:
TRUNCATE TABLE Demo2000
Ma se ora vi servissero di nuovo i dati del 2000? Semplice, basta reimportare il file esportato precedentemente:
bcp Demo.dbo.Demo2000 in Demo2000.dat -S <your instance> -T –N
Eseguendo la query:
SELECT * FROM DemoView WHERE dt = '2000-03-30'
Otteniamo il risultato atteso:
I dati sono stati reimportati correttamente.
Se pero’ eseguiamo la query del 2001:
SELECT * FROM DemoView WHERE dt = '2001-03-30'
Il risultato e’ corretto. Notiamo pero' che il piano d’esecuzione e’ cambiato:
Perche’ ora SQL accede sia alla tabella del 2001 che a quella del 2000? Non ci sono le constraints? La risposta e’ si, ma non sono piu’ trusted (fidate). Il tool BCP, per efficienza, importa i dati disattivando di default il controllo delle constraints. Cio’ vuol dire che – potenzialmente – e’ possibile importare dati che violino la constraint (cioe’, ad esempio, inserire record del 2001 nella tabella 2000). Dato che la constraint e’ stata potenzialmente violata, SQL non si fida piu’ e crea un piano d’esecuzione che riflette questa sua sfiducia. Cio’ e’ deleterio per le performance (pensate se la tabella del 2000 avesse miloni di record!). Come si fa a sapere quindi se una constraint e’ fidata (trusted)?
Con questa query:
SELECT is_not_trusted, * FROM sys.check_constraints WHERE name='CK_Demo2000_dt'
Il risultato, come ci aspettiamo, e’ il seguente:
Come facciamo a riguadagnare fiducia in una constraint? Bisogna dire a SQL Server di analizzare tutti i record della tabella e di assicurarsi che siano conformi alla constraint. Nel nostro caso cio’ equivale a:
ALTER TABLE Demo2000 WITH CHECK CHECK CONSTRAINT [CK_Demo2000_dt]
Che riporta la situazione in questo modo:
Questo da la garanzia necessaria a SQL Server per ottimizzare il piano d’esecuzione della nostra query originaria:
SELECT * FROM DemoView WHERE dt = '2001-03-30'
Morale: controllate periodicamente che le vostre constraints siano trusted. Ci sono tante operazioni che possono rendere le constraints insicure e l’effetto lo avete visto: i piani d’esecuzione sono peggiori.
Happy coding,
Francesco Cogno