Sort calculated field? Performance Hit? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Sort calculated field? Performance Hit?

Author

Topic

swhook52
Starting Member

4 Posts

Posted - 2008-12-08 : 13:30:56

I have a query that I need to do very efficiently. Consider a table of OrderDetails that has an auto incremented ID and a BaseID for grouping Order Details with other Order Details. So you could have the following in the table.

Item 1 (ID=1 BaseID=1)
Item 2 (ID=2 BaseID=2)
Item 3 (ID=3 BaseID=3)
Item 1a (ID=4 BaseID=1)
Item 1b (ID=5 BaseID=1)
Item 2a (ID=6 BaseID=2)

I need to sort it so that it looks like this:

Item 1
Item 1a
Item 1b
Item 2
Item 2a
Item 3

I came up with a solution that works but it is messy. I am looking for ideas on how to implement it cleaner.

SELECT [Description],
(CASE CONVERT(VARCHAR, BaseID) WHEN '0' THEN '' ELSE CONVERT(VARCHAR, BaseID) END) + CONVERT(VARCHAR, OrderLineID) AS SortOrder
FROM OrderDetails
WHERE OrderID = @OrderID
ORDER BY SortOrder

Also, what sort of index would I write to cover a query like this?

Thanks,
-Steve

swhook52
Starting Member

4 Posts

Posted - 2008-12-08 : 13:38:08

I guess I should say that there will be items that do not have a BaseID and I must keep the original order that it came in. I could have something like this:

Item 1 (ID=1 BaseID=1)
Item 2 (ID=2 BaseID=2)
Item 3 (ID=3 BaseID=3)
Item 1a (ID=4 BaseID=1)
Item 1b (ID=5 BaseID=1)
Item 2a (ID=6 BaseID=2)
Payment 1 (ID=7 BaseID=0)
Item 4 (ID=8 BaseID=8)
Payment 2 (ID=9 BaseID=0)

In this case I ordered items 1 through 3 and made a payment. Then I came back and ordered item 4 and paid for it. I need to keep the order that they payment came then I order more and paid for those. That was the reasoning for the case statement in the select.

-Steve

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-08 : 14:57:10

[code]
select description from OrderDetails
order by case when baseid <> 0 then baseid else id end, id asc
[/code]

swhook52
Starting Member

4 Posts

Posted - 2008-12-08 : 15:19:29

Thanks hanbingl. I figured there was a simpler way to do that.
So considering the revised query:

SELECT [Description]
FROM [OrderDetails]
WHERE [OrderID] = @OrderID
ORDER BY CASE WHEN [BaseID] <> 0 THEN [BaseID] ELSE [ID] END, [ID] ASC

What would be a covering index for this?
(OrderID, BaseID, ID) ?

-Steve

swhook52
Starting Member

4 Posts

Posted - 2008-12-09 : 10:14:47

I'm thinking more deeply into this...

SELECT [Description]
FROM [OrderDetails]
WHERE [OrderID] = @OrderID
ORDER BY CASE WHEN [BaseID] <> 0 THEN [BaseID] ELSE [ID] END, [ID] ASC

Wouldn't this query return an order by clause of:

ORDER BY ID, ID

in certain scenarios? That seems bad. Does that affect the sorting performance?
-Steve

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-09 : 11:55:10

It will not return an order by clause of ORDER BY ID, ID. CASE WHEN will generate its own list to order by. Since SQL 2005 can not index on a computation, you can create a computed column then index on that column and ID.

ALTER TABLE [ORDERDETAILS]
ADD SortOrder as CASE WHEN [BaseID] <> 0 THEN [BaseID] ELSE [ID] END

CREATE INDEX OrderByIndex on ORDERDETAILS(SortOrder,id)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources