Evils of coalesce on a column in the Where clause due to nullable parameters

Question

Post reply

Evils of coalesce on a column in the Where clause due to nullable parameters

MadAdmin

SSChampion

Points: 11261
More actions
March 8, 2012 at 6:51 am

#252232

Our one DB had Procs which were designed to get data depending on Nullable parameters being passed.
Create proc MyProc(@var1 int = null,@var2 varchar(50) = null, ....,@var8 bigint = null)
The where clause looked like this
Where table1.col1=coalesce(table1.col1,@var1) AND .......... AND tableX.colx=coalesce(tableX.colX,@var8)
The end result was a proc which in some cases generated 24 Million logical reads on a table on one execution.
One proc was doing 40% of all io on DB and another was using 37% of all cpu generated by the procs on the DB.
These procs did not use any indexes that were there, and did not log to missing_indexes since it would be useless.
First step was to rewrite as Dynamic sql, so that we only had plans built on non null parameters, ensuring we use sp_executeSQL to avoid SQL injection and encourage plan reuse.
After I rewrote the 10 worst performing procs and tested all permutations of nullable parameters, server CPU went down by 91% and IO went down by 87%, and that was before adding indexes.
Memory grants reduced drastically as well.
Worktables to store temporary result sets are gone (so I am guessing less activity on tempDB?).
Procs were reduced to using about 0.005% of db resources each.
The 24 million io proc was reduced to 8 logical reads on the one table.(units not millions)
That is an improvement that new hardware can't match.
The next step was to look at missing indexes after about 1 week after DB restart and the results were completely different to the old stats.
Just wanted to evangelize about this experience.
A big win is that I have converted the devs into avoiding functions in WHERE clause at all cost so I do not have to keep checking for worst practice.
So my top tip is, look at procs first, rewrite if necessary, then look at indexing.
Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Gail Shaw wrote about "Catch all" queries a while ago[/url].

Since then, whenever I've needed to do something like that I've always started with that article in my mind.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 2

i wrote a blog on a similar issue here...[/url]

My example is using ISNULL but works in similar way. Massive performance gains to be had!!

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

MadAdmin SSChampion Points: 11261 More actions · Answer 3

Apparently Gail Shaw has given massive amounts of contributions in this regard and this article talks in depth about general performance tuning.

I think my issue was included in this article as well.

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Now if I can only get the DBA and devs to read these articles:unsure:

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

No, nothing on catch-all queries in the simple-talk article. That's just about poor indexing. I always meant to write a follow-up about poor T-SQL. but could never decide what to include and what not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 5

MadTester (3/8/2012)
So my top tip is, look at procs first, rewrite if necessary, then look at indexing.

I absolutely agree. "Performance is in the code."

Very well done and thanks for sharing the experience. After seeing a bazillion "my code is running slow how to fix it" posts, it's absolutely awesome to see one about incredible success. You've probably made my month for me!

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

MadAdmin SSChampion Points: 11261 More actions · Answer 6

GilaMonster (3/8/2012)
No, nothing on catch-all queries in the simple-talk article. That's just about poor indexing. I always meant to write a follow-up about poor T-SQL. but could never decide what to include and what not.

The article is integral in showing how to identify a problem without using Profiler.

The first thing the devs asked me is how I found the performance issues.

They generally do not know about Execution plans and the set statistics option.

So this is the article which will let them know how to get that information.

But you are correct, the solution is not on that link at simple talk.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]