I got it, it doesn't matter for SQL to by using index in the second query so it uses inde scan
as you can see from the execution plan http://blog.brianhartsock.com/wp-content/uploads/2008/12/querycomparison.jpg both of them takes equal time. You can understand from %50 for each query. By the way you can get io statistics by set statistics io on go select * from abc On Thu, Dec 3, 2009 at 12:53 PM, Maxus <[email protected]> wrote: > 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 <[email protected]> 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 <[email protected]> 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 <[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]<nhusers%[email protected]> > <nhusers%[email protected]<nhusers%[email protected]> > > > > > . > > > 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]<nhusers%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/nhusers?hl=en. > > > -- Pamir Erdem -- 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.
