Thanks for the explanation. I suppose my solution of always specifying a final sort order of ID will work in this situation.
-Will On Tue, Oct 7, 2008 at 8:13 AM, Ken Egozi <[EMAIL PROTECTED]> wrote: > it seems VERY odd to me that having different columns in the select could >> yeild entirely different results >> > not at all. > > changing the columns might have major effect on the execution plans. > > simple example: > People (Id, FirstName, LastName, CityName) > with a clustered index on Id, > and a non-clustered index on (FirstName) which includes (LastName) > > to fulfil > > SELECT TOP 10 > FirstName, > LastName, > CityName > FROM > People > > SQL server would have to access the table to fetch CityName, as it's the > only place where it's stored. > > on the other hand, for > > SELECT TOP 10 > FirstName, > LastName > FROM > People > ORDER BY > FirstName > > the server can decide to read all the data from the index (as it's covering > all of the columns) if it thinks it would incur less pages being fetched > from the DB. Assuming there are more columns on People table it would make > this option more probable, as record sized will grow which means less > records per page -> more pages to read when scanning the table. > > > I've been reading Inside T-SQL Querying (by Itzik Ben Gan) lately, and > there's a lot to learn from this book about the way query plans works in SQL > Server, and on query tuning etc. > > link to the book (highly recommended read): > http://astore.amazon.com/kenegoziswebl-20/detail/0735623139 > > > > > > On Tue, Oct 7, 2008 at 4:39 PM, Will Shaver <[EMAIL PROTECTED]> wrote: > >> Ken - The strange thing is that the results are consistently >> inconsistent. I have a query: >> >> SELECT TOP 10 Id15_1_, Version15_1_, Id16_0_ FROM (SELECT ROW_NUMBER() >> OVER(ORDER BY __hibernate_sort_expr_0__) as row, query.Id15_1_, >> query.Version15_1_, query.Id16_0_, query.__hibernate_sort_expr_0__ FROM >> (SELECT this_.Id as Id15_1_, this_.Version as Version15_1_, root_tempo1_.Id >> as Id16_0_, root_tempo1_.LotNumber as __hibernate_sort_expr_0__ FROM >> SelectTest.dbo.Inventory this_ inner join SelectTest.dbo.Inventory_Data >> root_tempo1_ on this_.Id=root_tempo1_.InventoryId and '11/3/2008 2:22:59 PM' >> >= root_tempo1_.TransactionStart and '11/3/2008 2:22:59 PM' < >> root_tempo1_.TransactionStop WHERE (Select count(*) from Inventory_Data data >> WHERE data.InventoryId = this_.Id AND '11/3/2008 2:22:59 PM' >= >> data.TransactionStart and '11/3/2008 2:22:59 PM' < data.TransactionStop) > 0 >> AND root_tempo1_.EquipmentId = 'd7930b93-bc00-4cce-9f13-9b2d00e192eb' and >> root_tempo1_.Quantity > 0) query ) page WHERE page.row > 20 ORDER BY >> __hibernate_sort_expr_0__; >> >> and it returns 10 items with this being the final row: >> D6D9DD42-1D3A-4743-B342-9B2D00E19302 1 >> 0C25FC7C-76C6-43EF-AE96-9B2D00E19302 >> >> then if I alter the query by removing the #2 and #3 items in the SELECT >> statement I get this as the final item >> 0F2C067B-BA21-4156-AAD9-9B2D00E19385 >> >> All other items are the same. Im able to reproduce it with a different >> auto-gen guid set. I've already sent Oren a sample db with this. So do you >> still think that this isn't a bug? It seems VERY odd to me that having >> different columns in the select could yeild entirely different results. I >> suppose that perhaps this is because they are in different tables? >> >> >> >> >> >> >> On Tue, Oct 7, 2008 at 12:12 AM, Ken Egozi <[EMAIL PROTECTED]> wrote: >> >>> this is not a bug. >>> >>> when you select with no ORDER BY clause, then the order of the results is >>> non deterministic, and is up to the execution plan. The order will be down >>> to the order of reads from memory and disk. >>> >>> when you sort on a non unique field, the order of records with the same >>> sorted values is again non deterministic, and again will be up to the >>> execution plan. >>> >>> Now it's possible that SQL Server will choose different query plans for >>> the two queries in your scenario, as after the first one, the engine knows >>> more about the query, and has other stuff in cache. So I find it non >>> surprising at all that you'd get different paged results when the sort order >>> is non deterministic. >>> >>> The solution of adding a deterministic column to the sort expression will >>> solve the determinism problem, ensuring the same exact results. >>> >>> >>> On Mon, Oct 6, 2008 at 9:04 PM, Ayende Rahien <[EMAIL PROTECTED]> wrote: >>> >>>> I think that this is an issue with SQL Server if you are doing sorts on >>>> multi proc machine. Can you give more details about the issue? >>>> >>>> >>>> On Mon, Oct 6, 2008 at 10:01 PM, Will Shaver <[EMAIL PROTECTED]>wrote: >>>> >>>>> Here's two days of my life solved that was solved with by adding a >>>>> single line to my project. I'm posting it here for the benefit of others. >>>>> It's an odd enough case that I don't know that it should go into a FAQ. >>>>> >>>>> It appears as though for some queries involving sql server 2005 and >>>>> ROW_NUMBER() the sort order is not always deterministic. I have the >>>>> following pieces which all lined up against me: >>>>> >>>>> * One to Many relationship mapped with lazy=false, fetch=subselect >>>>> * Guid IDs for both entities >>>>> * Query with limits for selecting 10 out of the middle >>>>> * Filters affecting the mapped relationship >>>>> * Sorting on a non-unique field such as quantity ordered >>>>> * Using Criteria query >>>>> >>>>> NH does this: >>>>> >>>>> 1. Grab results using the highly convoluted ROW_NUMBER() OVER(... >>>>> 2. Determine that filters are in place and relation needs to be grabbed >>>>> 3. Grab the relation with a sub-select as specified in mapping via >>>>> ROW_NUMBER() ... >>>>> 4. Error! >>>>> >>>>> What was occuring is that the results of the subselect for the second >>>>> query are in a different order than for the first select. This is NOT an >>>>> NH >>>>> bug and is a bug with Sql Server. (I'm using 2005 express.) >>>>> >>>>> More details can be provided on the bug if anyone wants them. Luckly >>>>> the solution is simple: >>>>> >>>>> Always sort by ID at the end of your list of "Order By"s. Note that if >>>>> you're not using filters and fetch=subselect this probably won't affect >>>>> you. >>>>> >>>>> >>>>> if(!string.IsNullOrEmpty(sort)) >>>>> { >>>>> criteria.AddOrder(new Order(sort, dir)); >>>>> } >>>>> criteria.AddOrder(new Order("Id", true)); >>>>> >>>>> >>>>> >>>>> -Will >>>>> >>>>> >>>>> >>>> >>>> >>>> >>> >>> >>> -- >>> Ken Egozi. >>> http://www.kenegozi.com/blog >>> http://www.musicglue.com >>> http://www.castleproject.org >>> http://www.gotfriends.co.il >>> >>> > > > -- > Ken Egozi. > http://www.kenegozi.com/blog > http://www.musicglue.com > http://www.castleproject.org > http://www.gotfriends.co.il > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
