> > 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 -~----------~----~----~----~------~----~------~--~---
