Order By with Case statement index issue

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

Transact-SQL (2005)

Order By with Case statement index issue

Author

Topic

yaltasource
Starting Member

6 Posts

Posted - 2009-01-29 : 02:39:06

Hi there

I have a basic table storing people records, which has a non-clustered index on the firstname. This is using SQL Server 2005.

I would expect that the two below queries should basically do the same thing (please ignore that one has sort direction seperate and the other does not)

However, according to the execution plan in MS SQL Server Management Studio the second query does not use the firstname non-clustered index so takes up more of the batch cost due to a sort being required.

If I'm not making some silly mistake, that would suggest that this is just a limitation of using case statements in the order part of the statement i.e. they don't use indexs as they perhaps should?

If that is correct, is ther any reason why (security etc.) I wouldn't want to make SPROCs based on the first technique? (of course if I am doing this the SortColumn parameter will be passed in, not set as I am doing in these test queries)

Query 1:

DECLARE @SortColumn nvarchar(100)
DECLARE @SortColumn2 nvarchar(100)
DECLARE @SortDirection nvarchar(100)
DECLARE @SortDirection2 nvarchar(100)
DECLARE @statement VARCHAR(MAX)

SET @SortColumn = N'FirstName';
SET @SortDirection = N'Desc';

SET @SortColumn2 = CASE
WHEN @SortColumn = 'FirstName' THEN ' ORDER BY '+@SortColumn
WHEN @SortColumn = 'LastName' THEN ' ORDER BY '+@SortColumn
WHEN @SortColumn = 'Title' THEN ' ORDER BY '+@SortColumn
ELSE ''
END

SET @SortDirection2 = CASE
WHEN @SortDirection = 'Asc' THEN ' '+@SortDirection
WHEN @SortDirection = 'Desc' THEN ' '+@SortDirection
ELSE ''
END

IF @SortColumn2 = ''
SET @SortDirection2 = ''

SET @statement = 'SELECT ID, FirstName, LastName, MiddleName, Title FROM People '+@SortColumn2+@SortDirection2

EXEC (@statement)
GO

Query 2:

DECLARE @SortColumn nvarchar(100)

SET @SortColumn = N'FirstNameDesc';

SELECT ID, FirstName, LastName, MiddleName, Title FROM People ORDER BY
CASE
WHEN @SortColumn = 'FirstNameAsc' THEN FirstName
WHEN @SortColumn = 'LastNameAsc' THEN LastName
WHEN @SortColumn = 'MiddleNameAsc' THEN MiddleName
WHEN @SortColumn = 'TitleAsc' THEN Title
END ASC,
CASE
WHEN @SortColumn = 'FirstNameDesc' THEN FirstName
WHEN @SortColumn = 'LastNameDesc' THEN LastName
WHEN @SortColumn = 'TitleDesc' THEN Title
END DESC
GO

Cheers
Sam

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-29 : 02:47:37

I have ever heard of indexes having an impact on the ORDER BY before...?

- Lumbago

yaltasource
Starting Member

6 Posts

Posted - 2009-01-29 : 03:06:55

Does that mean you think something strange is happening specific to me, or you're not sure if that is what happens when using case statement in the order by clause?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-29 : 07:25:04

quote:
Originally posted by Lumbago

I have never heard of indexes having an impact on the ORDER BY before...?

- Lumbago

I think there is a little mistake in Lumbagos post...

Because ORDER BY works on the resultset and indexes have impact to the WHERE-clause.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

yaltasource
Starting Member

6 Posts

Posted - 2009-01-29 : 14:23:03

Thanks webfred, but I'm still confused. Neither of the examples in my post have a WHERE clause, but the first one does use the index correctly...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-29 : 14:41:04

I believe the issue due to the first SP using dynamic sql. The statement is executed outside the scope of any cached execution plan so the plan for the assembled statement is derived at runtime. at the time a plan for the second SP is created the optimizer can't know for sure what the ORDER BY column will be.

Be One with the Optimizer
TG

EDIT:
I should say that the dynamic statement may have a cached plan if previous calls resulted in the same statement but it's not the plan cached with the SP.

yaltasource
Starting Member

6 Posts

Posted - 2009-01-29 : 14:59:03

Thanks TG that makes sense.

So would you recommend using the first technique or can you see security or performance issues with that one?

I would like to apply whatever technique I go with to tables and sprocs that may have a largish (up to say 20 or 30) number of columns included in the case statement.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-29 : 15:14:27

I hesitate to make recommendations without knowing all the facts. Generally speaking I try to avoid dynamic sql. If you always return the entire table and just the order changes then you may consider removing the ORDER BY and sort at the client.

You also may try a different approaches - one possibility may be to use your case statement to populate an additional column and sort by that.

Sometimes dynamic sql may be the best answer - as long as your parameters are appropriately typed

Be One with the Optimizer
TG

yaltasource
Starting Member

6 Posts

Posted - 2009-01-29 : 15:33:31

Thanks TG

I am trying to avoid sorting at the client as I am assuming SQL Server can do this faster than something in the .net framework such as a dataview.

I'm not sure I understand your other approach can you provide an example?

Re the parameters in the first query example, i'd be checking them at the client before they are passed and then using the CASE to be doubly sure only values I am expecting can actually be included in the dynamic slq that is executed.

These sprocs will mostly be triggered by users clicking column headings on tables in web pages.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-29 : 16:13:25

>>I'm not sure I understand your other approach can you provide an example?

Read through this topic (as well as the referenced article) for some ideas and examples.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942

Be One with the Optimizer
TG

yaltasource
Starting Member

6 Posts

Posted - 2009-01-29 : 16:44:02

OK thanks TG, I'll read through that and try some of it out (I see Mr Joe Celko has posted an example in there, so his is probably a good place to start)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources