Inconsistent order by using CTE on multi CPU serve

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Inconsistent order by using CTE on multi CPU serve

Author

Topic

psaunders
Starting Member

3 Posts

Posted - 2014-08-11 : 20:28:07

I use Code OnTime development tool.
It generates the query below (I removed some fields to reduce post size).

The important part is the order by, strangely, it generates the order by as:
"PurchaseOrder"."purchaseOrderId" desc,"PurchaseOrder"."purchaseOrderId"

This has been sorting correctly by "purchaseOrderId" desc" up until last friday, when we upgraded the server to multi CPU. Now, our users see changing sort results, ie not correctly sorted by "purchaseOrderId" desc".

This has got me stumped. Wondering if anyone has ideas on this?

SQL Server version:
SQL Server Web Edition (64-bit)
10.50.2500
RAM: 3Gb
Processors: 4

Thank you, Patrick.

======================= QUERY =============
exec sp_executesql N'with page_cte__ as (
select
row_number() over (order by "PurchaseOrder"."purchaseOrderId" desc,"PurchaseOrder"."purchaseOrderId") as row_number__
,"PurchaseOrder"."purchaseOrderId" "purchaseOrderId"
// CUT FIELDS
,PurchaseORder.repairType "repairType"
,(select top 1 1
from "dbo"."RepairRequest" , RepairRequestPurchaseOrder rrpo
where 1=1
and PurchaseOrder.purchaseOrderId = rrpo.purchaseOrderId
and rrpo.repairRequestId = "RepairRequest".repairRequestId) "hasOrigRepairRequest"
,(select top 1 1
from
RepairRequestPurchaseOrder rrpo
,RepairRequest rr
,PurchaseOrder testTagPO
,Service testTagService
, ServiceItem testTagServiceItem
where 1=1
and rrpo.purchaseOrderId = PurchaseOrder.purchaseOrderId
and rrpo.repairRequestId = rr.repairRequestId
and rr.sourcePurchaseOrderId = testTagPO.purchaseOrderId
and testTagService.purchaseOrderId = testTagpo.purchaseOrderId
and testTAgservice.serviceId = testTagServiceItem.serviceId) "hasOrigServiceItems"
,"vwPurchaseOrderStatus"."description" "orderStatusDescription"
,"PurchaseOrder"."chargedDeliveryCode" "chargedDeliveryCode"
from
"dbo"."PurchaseOrder"
left join "vwPurchaseOrderStatus" on PurchaseOrder.orderStatusType = vwPurchaseOrderStatus.orderStatusType
left join "Contractor" on PurchaseOrder.contractorId = Contractor.contractorId
left join "DeliveryCodeType" on PurchaseOrder.chargedDeliveryCode = DeliveryCodeType.deliveryCode
left join DVAClient on PurchaseOrder.dvaClientId = DVAClient.dvaClientId
left join HealthProvider on HealthProvider.healthProviderId = PurchaseOrder.healthProviderId
left join OnlineOrderPurchaseOrder on OnlineOrderPurchaseOrder.purchaseOrderId = PurchaseOrder.purchaseORderId

where
(
("PurchaseOrder"."orderStatusType" IN (''READY'',''COMPLETED'',''CLOSED'',''CANCELLED'')
and "PurchaseOrder"."contractorId" = @ContractorIdFilter))
)
select * from page_cte__ where row_number__ > @PageRangeFirstRowNumber and row_number__ <= @PageRangeLastRowNumber',N'@ContractorIdFilter int,@PageRangeFirstRowNumber int,@PageRangeLastRowNumber int',@ContractorIdFilter=35,@PageRangeFirstRowNumber=0,@PageRangeLastRowNumber=10

====================

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-11 : 20:32:25

If this query is being dynamically created, then you'll need to show us the code that builds it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

psaunders
Starting Member

3 Posts

Posted - 2014-08-11 : 21:57:55

quote:
Originally posted by tkizer

If this query is being dynamically created, then you'll need to show us the code that builds it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hi Tara,
The code to do this is in the website, and it's potentially proprietry information.
Either way, I can't see what the source code has to do with the query?

My point is it's the query above being executed that gives strange results on my production server only.
See screenshots of Dev vs Production, the row_number sorts randomly. Same query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-12 : 02:08:49

No, if you correlate the ROW_NUMBER column with PurchaseOrderID column, you can see that the values are correct.
If you refer to that the set is not ordered, you will have to add a final ORDER BY in your code.

The reason is that the two machines are producing different execution plans. If you include a ROW_NUMBER function in your code, there is no guarantee that the final result will be displayed in that order, not even if you have a unique clustered index.

If you want your result to be displayed in a particular order, you must add a ORDER BY to your code.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-12 : 02:10:29

[code]where
(
("PurchaseOrder"."orderStatusType" IN (''READY'',''COMPLETED'',''CLOSED'',''CANCELLED'')
and "PurchaseOrder"."contractorId" = @ContractorIdFilter))
)
select * from page_cte__ where row_number__ > @PageRangeFirstRowNumber and row_number__ <= @PageRangeLastRowNumber ORDER BY [row_number__]'
,N'@ContractorIdFilter int,@PageRangeFirstRowNumber int,@PageRangeLastRowNumber int',@ContractorIdFilter=35,@PageRangeFirstRowNumber=0,@PageRangeLastRowNumber=10;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

psaunders
Starting Member

3 Posts

Posted - 2014-08-12 : 03:46:09

quote:
Originally posted by SwePeso

No, if you correlate the ROW_NUMBER column with PurchaseOrderID column, you can see that the values are correct.
If you refer to that the set is not ordered, you will have to add a final ORDER BY in your code.

The reason is that the two machines are producing different execution plans. If you include a ROW_NUMBER function in your code, there is no guarantee that the final result will be displayed in that order, not even if you have a unique clustered index.

If you want your result to be displayed in a particular order, you must add a ORDER BY to your code.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thanks SwePeso, you are correct of course. The query is generated dynamically by the Code OnTime tool - I have logged the issue with them.
Interestingly this issue only started to occur after I upgraded the server and SQL Instance from 1 to 4 CPUs. I have been using the tool for 3 years and never seen this issue before.
So likely they have not tested on multi-core SQL Instance.

Regards, Pat.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-12 : 05:15:03

I'd say you have been very lucky then. In SQL Server 2000 many people relied in the coincidence that the result was ordered according to the clustered index. It looked like that but it was pure coincidence.
Even with only one core, there is no guarantee that the output will be ordered the same way for every query execution.
The only way to guarantee order, is to add a ORDER BY in outermost query.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources