Hi Pamir Erdem, Its due to the field in the db being varchar and the query converting it.
The info is here: http://blog.brianhartsock.com/2008/12/14/nvarchar-vs-varchar-in-sql-server-beware/ Thanks for your help. -Maxus On Dec 3, 5:59 pm, Pamir Erdem <pamir.er...@gmail.com> wrote: > Hi, > > .Most probably, the correct one uses old statistics for index choice, and > the second one uses new statistics or vice verse. > Updating your statistics can make your optimizer to use right index. > > . The second issue that i believe, because of using NVarChar instead of > varchar optimzer can not detect the right index (Index is on a NVarchar > columnd). Would you please exoprt the query exeuction plan of these two > queries. > > > > > > On Thu, Dec 3, 2009 at 11:38 AM, Maxus <rtypestud...@gmail.com> wrote: > > 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 <rtypestud...@gmail.com> 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 nhus...@googlegroups.com. > > To unsubscribe from this group, send email to > > nhusers+unsubscr...@googlegroups.com<nhusers%2bunsubscr...@googlegroups.com> > > . > > For more options, visit this group at > >http://groups.google.com/group/nhusers?hl=en. > > -- > Pamir Erdem- Hide quoted text - > > - Show quoted text - -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to nhus...@googlegroups.com. To unsubscribe from this group, send email to nhusers+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.