Just after I posted I played with the types: if I Change the first query to this:
exec sp_executesql N'SELECT top 1 this_.Code as y0_ FROM [CS].[dbo]. [Item] this_ WHERE not (this_.Id = @p0) and this_.Code = @p1',N'@p0 int,@p1 varchar(10)',@p0=0,@p1=N'XXXXX1' it runs a lot faster and the query plan is same as my second query, notice the nvarchar being changed to varchar, anyone understand whats going on here or is the quirky MS magic? Is there a way to make Nhibernate use varchar instead of nvarchar? Thanks, Maxus On Dec 3, 5:33 pm, Maxus <[email protected]> wrote: > Hi People, > > Anyone know why these two statements cause a different execution plan? > > exec sp_executesql N'SELECT top 1 this_.Code as y0_ FROM [CS].[dbo]. > [Item] this_ WHERE not (this_.Id = @p0) and this_.Code = @p1',N'@p0 > int,@p1 nvarchar(10)',@p0=0,@p1=N'XXXXX1' > > SELECT top 1 this_.Code as y0_ FROM [CS].[dbo].[Item] this_ WHERE not > (this_.Id = 0) and this_.Code = 'XXXXX1' > > The first query runs @ 100% the second runs @ 0% when compared in the > query analyser together. > > The plan for the first one goes: > > Index Scan 84% > Parallelism 16% > Top 0% > Select 0% > > The second one goes: > > Index Seek 100% > Top 0% > Select 0% > > So it appears the second query is using the correct index, anyone know > why this is? > > Thanks, > Maxus -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
