Is there a way to see the cost of a CASE statment

Question

Is there a way to see the cost of a CASE statment

bwilliams-1049831

Mr or Mrs. 500

Points: 532
More actions
November 5, 2009 at 2:03 pm

#206305

The reason I ask is I have a couple of user defined functions. I did not write them!
They are both doing 2 CASE statements inside a select statement into a table variable and passing the table variable back to the calling program.
This can easly be done in one CASE statement.
Normally I would not bother with something this trivial but both functions get exedcuted around 6 million times a week!
The essence of the logic is:
if language_code = 'french' then use french title
else use english title
and a second CASE in the same select
if language_code = 'french' concatenate french subtitle
else concantenate english subtitle
It seems a no-brainer that one CASE statement should be used but I would like to know will it make a difference.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I've never seen having two case statements in a query make a difference in its performance vs one case statement.

I have seen lots of places where UDFs kill performance. Is there any way for you to move the code into the proc that calls the functions? Or is it a "code reuse issue", where multiple procs use the same functions and it needs to be maintained in one place?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

bwilliams-1049831 Mr or Mrs. 500 Points: 532 More actions · Answer 2

No I do not know the calling procedures. This may be a next step for me.

I am new to this company and have installed spotlight for SQL Server reciently.

Spotlight has highlighted many items of interest. These 2 functions are just 2 of them.

One other was a table with over 300 million records in it. This was caused by an insert statement from a select statement that had an "and" and "or" clause with out any brackets:-). After correcting that (Included a truncate table on production) the table is down under 500,000 records.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Definitely find the calling sources for the Functions. I have seen numerous UDFs be outperformed by procs by a factor of 10. Once you find the calling sources, you will also need to evaluate the calling conditions. Is the UDF used as a table in a join or directly from a select, or is it used otherwise.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

bwilliams-1049831 Mr or Mrs. 500 Points: 532 More actions · Answer 4

Yes the UDF,s are called in the where clause of a select statement.

But changing the queries from using the UDF's would be painful as I do not own the code.

The change management process would not make it worth the effort.

Even changing the UDF's, I would need to make a case for it with expected improvements.

But if 2 CASE statements take 1 milli second longer that 1 I am still talking hours with the 2 UDF's executing 6 million times a week.

I know I need to see how many places these UDF are called from.

But as I stated I am new with this company and am still able to find quick wins.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Tuning the UDFs will gain you a little. I would look somewhere else in the UDF to try for performance improvement. Maybe an index has been missed or something. Make sure you check everything out in those functions and don't narrowly focus on the case statements. Maybe a change in logic inside the function could save you more time and effort than changing the case statements.

Though you may be able to get a small win, if you could take the query and call it from a proc and show that it performs x times faster than the function, that should be enough to convince change management. I imagine you will have to do the change management no matter which path you choose (whether function) or outside code. However, it seems that you are also probably dealing with inline sql (sql not in procs) - which lends to problems as well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

bwilliams-1049831 (11/5/2009)
a select statement into a table variable and passing the table variable back to the calling program

You can gain quite a bit of performance if you make sure they're written as "inline table valued functions" instead of "multi-line table valued functions". Lookup CREATE FUNCTION in Books Online (the "help" documentation that comes with SQL Server) to see the difference in constructs.

I agree with the others, though... determining why someone thinks this needs to be a UDF and possibly eliminating it by replacing it with proper set based programming will likely be of better performance help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

Also have a look at one of the previous headlines of the daily newsletter:

"T-SQL Best Practices – Don’t Use Scalar Value Functions in Column List or WHERE Clauses"

http://www.sqlservercentral.com/redirect/articles/68642/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 8

Or my blog has a simple example

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/10/15/udf-overhead-a-simple-example.aspx

Clear Sky SQL
My Blog[/url]

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 9

Dave Ballantyne (11/6/2009)
Or my blog has a simple example
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/10/15/udf-overhead-a-simple-example.aspx

Very nice example, Dave !

Straight forward and very to the point !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

bwilliams-1049831 Mr or Mrs. 500 Points: 532 More actions · Answer 10

Yes Dave the article was awesome.

It was almost exactly what I was hoping for when I stated this topic.

It is more on UDF's than on the 2 CASE statements. But I can use the same logic for my issue plus I have more to think about now.

2 CASE statements vs 1 won't gain much but executing them over 6 million times may.

But ultimetely removing the UDF's would gain far more.

Thank You!

bwilliams-1049831 Mr or Mrs. 500 Points: 532 More actions · Answer 11

Ok I have played some more with this and saw one interesting thing

With SET STATISTICS TIME ON

I ran the original query with the 2 CASE statments that is in the UDF.

The first time it is run I get

one line of:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 6 ms.

and 3 lines of:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

If I re-run it I get 4 lines of:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

I get the exact same result with the modified version using only 1 CASE statement.

If I go back and run the original I get the 6 ms again and three 1 ms for the first run, followed by four 1ms on a rerun.

The interesting thing is if I call the original UDF

I get 4 lines of:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

I never get the 6 ms elapsed time.

This I found interesting and contrary to Dave's Blog/article??

None of the changes seem to make even 1 ms of difference but I still wonder if executing them 6 million times would a difference.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 12

bwilliams-1049831 (11/6/2009)
None of the changes seem to make even 1 ms of difference but I still wonder if executing them 6 million times would a difference.

Absolutely. If you are doing performance comparisons, use millions of rows. One or two iterations really dont prove anything.

Should be simple enough to do.

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

Dave Ballantyne (11/6/2009)
Absolutely. If you are doing performance comparisons, use millions of rows. One or two iterations really dont prove anything.
Should be simple enough to do.

It is, indeed...

DROP TABLE JBMTest

GO

--===== Create and populate a 1,000,000 row test table.

-- Column "RowNum" has a range of 1 to 100,000 unique numbers

-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

-- Jeff Moden

SELECT TOP 1000000

SomeID = IDENTITY(INT,1,1),

SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),

SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

SomeHex12 = RIGHT(NEWID(),12)

INTO dbo.JBMTest

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned

-- Takes about 1 second to execute.

ALTER TABLE dbo.JBMTest

ADD PRIMARY KEY CLUSTERED (SomeID)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 14

bwilliams-1049831 (11/5/2009)
Normally I would not bother with something this trivial but both functions get exedcuted around 6 million times a week!

Unless the function does nothing except execute the two CASE statements, I really wouldn't bother. The difference will be immeasurable.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi