Using COALESCE forces SQL to timeout

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?
saturationAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
it's possible, because the coalesce() function on the field makes that the index on the field cannot be used.
0
Aneesh RetnakaranDatabase AdministratorCommented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

saturationAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
saturationAuthor Commented:
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
corptechCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.