sp_executesql n'utilise pas l'index?

J'utilise nHibnerate dans mon application Web et j'ai un problème avec les index générés dans sp_execute. Ma table a 210 millions d'loggings et la requête est très lente.

Tout d'abord, il y avait un problème avec le type de colonne généré 'kolumna1'. Dans la database j'ai une colonne de varchar mais nHibernate a généré nvarchar. J'ai travaillé sur ceci en plaçant l'atsortingbut spécial dans le code qui a forcé l'utilisation de varchar. Après cette astuce, sp_executed a commencé à utiliser des index et tout était correct. Maintenant, le problème est de return sp_executesql prend 10 minutes pour terminer. Quand j'ai vérifié la requête normale (sans sp_executesql) cela a pris seulement 1s. J'ai vérifié les plans d'exécution pour les deux: sp_executesql n'utilisait pas l'index et la requête normale utilisait l'index. Sans changer d'index j'ai modifié varchar en nvarchar et sp_execute fini en 1s (index utilisé). Quelqu'un a-t-il eu une idée d'où j'ai fait une erreur? pourquoi le plan d'exécution est différent pour de si petits changements? Et comment le réparer?

Ici j'ai joint plus de code. Juste au cas où quelqu'un en aurait besoin.

sp_executesql avec varchar (8000)

• Pourquoi NHibernate Session.Save verrouille-t-il la database?
• Comment save un file PDF Utilisation de NHibernate et SQL Server 2005
• Comment récupérer des index existants sur des tables dans SQL Server à l'aide de code?
• Optimisation de SQL généré par NHibernate
• Comment puis-je empêcher deux applications de s'affronter, car elles utilisent une database?

exec sp_executesql N'SELECT count(*) as y0_ FROM tabela1 this_ WHERE ((this_.kolumna2 >= @p0 and this_.kolumna2 <= @p1)) and (this_.kolumna3 in (@p2, @p3) and this_.kolumna1 like @p4)',N'@p0 datetime,@p1 datetime,@p2 int,@p3 int,@p4 varchar(8000)', @p0='2013-01-08 14:38:00' ,@p1='2013-02-08 14:38:00',@p2=341,@p3=342,@p4='%501096109%' 

sp_executesql avec nvarchar (4000)

 exec sp_executesql N'SELECT count(*) as y0_ FROM tabela1 this_ WHERE ((this_.kolumna2 >= @p0 and this_.kolumna2 <= @p1)) and (this_.kolumna3 in (@p2, @p3) and this_.kolumna1 like @p4)',N'@p0 datetime,@p1 datetime,@p2 int,@p3 int,@p4 nvarchar(4000)', @p0='2013-01-08 14:38:00' ,@p1='2013-02-08 14:38:00',@p2=341,@p3=342,@p4='%501096109%' 

La partie amusante est que dans sql profiler les deux requêtes donnent le même résultat:

 exec sp_executesql N'SELECT count(*) as y0_ FROM tabela1 this_ WHERE this_.kolumna3 in (@p2, @p3) and ((this_.kolumna2 >= @p0 and this_.kolumna2 <= @p1)) and ( this_.kolumna1 like @p4)',N'@p0 datetime,@p1 datetime,@p2 int,@p3 int,@p4 varchar(8000)', @p0='2013-01-08 14:38:00' ,@p1='2013-02-08 14:38:00',@p2=341,@p3=342,@p4='%501096109%' --Declare @p0 datetime --set @p0 = '2013-01-08 14:38:00' --Declare @p1 datetime --set @p1 = '2013-02-08 14:38:00' --Declare @p2 int --set @p2 = 341 --Declare @p3 int --set @p3 = 342 --Declare @p4 varchar(8000) --set @p4 = '%501096109%' --SELECT count(*) as y0_ --FROM tabela1 this_ --WHERE ((this_.kolumna2 >= @p0 and --this_.kolumna2 <= @p1)) and --(this_.kolumna3 in (@p2, @p3) and this_.kolumna1 like @p4) 

Voici les index:

 CREATE TABLE [dbo].[tabela1]( [id] [bigint] NOT NULL, [kolumna1] [varchar](128) NOT NULL, [kolumna2] [datetime] NOT NULL, [kolumna3] [int] NOT NULL, CONSTRAINT [PK__tabela1__4F7CD00D] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE NONCLUSTERED INDEX [ind_tabela1_ kolumna2] ON [dbo].[tabela1] ( [kolumna2] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [ind_ tabela1_ kolumna3] ON [dbo].[ tabela1] ( [kolumna3] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_ tabela1_ kolumna1] ON [dbo].[ tabela1] ( [kolumna1] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_ tabela1_ kolumna2_ kolumna3] ON [dbo].[ tabela1] ( [kolumna2] ASC, [kolumna3] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_ tabela1_ kolumna3_ kolumna2_id_ kolumna1] ON [dbo].[ tabela1] ( [kolumna3] ASC, [kolumna2] ASC, [id] ASC, [kolumna1] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO 

Sous le plan d'exécution de la requête: select count (*) depuis [dbo]. [Tabela1] où [kolumna1] comme N '% 501096109%'

• GUID généré par NHibernate – type de champ
• Feuille de route pour un meilleur access aux données pour l'application asp.net MVC
• NHibernate – Comment contourner la limitation du nombre de parameters dans SQL Server
• Sous-requête NHibernate Aggregate
• Amélioration de la requête SQL – Sélectionnez avec max et groupby

 select * from sys.dm_exec_cached_plans cross apply sys.dm_exec_sql_text(plan_handle) t 

 dbcc freeproccache (<your_plan_handle>)