saturation
asked on
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?
select * from company where comp_companyid = 2619
the query comes back immediately. However, when I run
select * from company where coalesce(comp_companyid,0)
then SQL times out. Why would it be doing this?
it's possible, because the coalesce() function on the field makes that the index on the field cannot be used.
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
select * from company where comp_companyid = 2619
UNION ALL
select * from company where comp_companyid is null
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
=> 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
ASKER
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.
"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.
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
select * from company where coalesce(comp_companyid,0)
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.