Supponiamo di avere una “classica” tabella di comuni, qualcosa come:
Nell’esempio in questione, poichè i dati verranno utilizzati al 95% con interrogazioni sulla descrizione del comune, è stato impostato l’indice cluster proprio su questa colonna, definita come VARCHAR(50):
create table municipalities
(
id smallint identity(1,1),
idDistrict smallint foreign key references districts(id),
[description] varchar(50),
code char(5),
constraint pkMunicipalities primary key nonclustered (id)
)
go
create clustered index idxMunicipalities on municipalities ([description])
go
A questo punto proviamo a fare una ricerca su una parte di testo, ad esempio:
set statistics io on
exec sp_executesql N'select Description
from municipalities where (Description like @p0 )
order by Description',N'@p0 varchar(50)',@p0=N'la s%'
set statistics io off
Questo il piano di esecuzione prodotto, con una precisa “Index Seek” sull’indice clustered:
Queste le statistiche:
Table 'municipalities'. Scan count 1, logical reads 2
Adesso proviamo ad analizzare la stessa interrogazione leggermente diversa.
Ovvero a definire il parametro non più VARCHAR(50) ma NVARCHAR(5), ovvero un NVARCHAR dimensionato secondo la lunghezza del testo passato (se avessi richiesto “ancona%” il parametro sarebbe stato dimensionato a 7 caratteri):
set statistics io on
exec sp_executesql N'select Description
from municipalities where (Description like @p0 )
order by Description',N'@p0 nvarchar(5)',@p0=N'la s%'
set statistics io off
Il piano di esecuzione e le statistiche sono drammaticamente diversi:
Table 'municipalities'. Scan count 1, logical reads 41
Siamo passati, di fatto, ad un Clustered Index Scan che, nel nostro esempio, equivale ad un Table Scan (infatti una “select * from municipalities” esegue proprio 41 letture).
Tra le due istruzioni questa è l’unica differenza:
- @p0 varchar(50)
- @p0 nvarchar(5)
Qual è il motivo che spinge l’ottimizzatore a scegliere una strada completamente diversa?
La differenza nella seconda istruzione, analizzando il piano di esecuzione, sta tutta nel predicato, ovvero:
CONVERT_IMPLICIT(nvarchar(50),[lxkMonitoring].[dbo].[municipalities].[description],0) like [@p0]
La funzione di conversione (CONVER_IMPLICIT), che deve essere applicata alla colonna e non al parametro, fa si che l’indice costruito su questa stessa non possa essere utilizzato.
Cosa succederebbe se fossi io a forzare la conversione del parametro nel tipo corretto ( …cast( @p0 as varchar(50))… )?
set statistics io on
exec sp_executesql N'select Description
from municipalities where (Description like cast( @p0 as varchar(50)) )
order by Description',N'@p0 nvarchar(5)',@p0=N'la s%'
set statistics io off
L’istruzione sarebbe nuovamente scritta nel modo corretto, infatti:
Table 'municipalities'. Scan count 1, logical reads 2
Morale: l’unica strada corretta è quella di utilizzare in maniera giusta i parametri, definendoli come devono essere defniti.