Solved

Using COALESCE forces SQL to timeout

Posted on 2009-07-06
7
767 Views
Last Modified: 2012-06-22
When I run

select * from company where comp_companyid = 2619

the query comes back immediately.  However, when I run

select * from company where coalesce(comp_companyid,0) = 2619

then SQL times out.   Why would it be doing this?
0
Comment
Question by:saturation
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
it's possible, because the coalesce() function on the field makes that the index on the field cannot be used.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
when u use a coalesce, sql has to perform another check operation to see whether companyid is null, if so set it as 0 ,

select * from company where comp_companyid = 2619
UNION ALL
select * from company where comp_companyid is null
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
ie, I presume you run the query test-wise in your management studio, query window.
=> use the Query Menu => include actual execution plan

and rerun both queries.
you will get a nice graph of the 2 queries execution plans.
one with index lookup
the second one with full table (or full clustered index) scan
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:saturation
Comment Utility
Also, I just noticed I'm getting the following error:

"SQL Execution Error"
Error Source: .NET SQLClient Data Provider
Error Message:  Lock request time out period exceeded.

I'm perplexed.  I have no problems querying any of the other tables in the database.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
anyhow, note that:


select * from company where coalesce(comp_companyid,0) = 2619

will not produce any results different from

select * from company where comp_companyid = 2619

unless you wanted to find the null values:

select * from company where comp_companyid IS NULL


0
 

Author Comment

by:saturation
Comment Utility
I do realize that; however, I'm troubleshooting an application that builds that query out and I don't have any control over the query...

Also, I just noticed that I can't even rebuild the indexes because of that error...Can individual tables be locked?   How do I release those locks if so?  
0
 
LVL 2

Accepted Solution

by:
corptech earned 500 total points
Comment Utility
I always use the IsNull (fieldname, 0).  There are arguments for and against using IsNull or Coalesce.  I've read of some performance issues with using coalesce.  Mostly with subqueries.  So, you could try the IsNull function and see if you get better results.  However, as angel mentioned, your sample SQL don't need the IsNull or coalesce the way its written.  The statement will return the same results with or without them.  

select * from company where comp_companyid = 2619 OR comp_companyid IS NULL  

This will give you both 2619 and NULL records.  Also, double check to make sure comp_companyid has been indexed.  That will improve performance too.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now