March 20, 2007 at 5:40 am
Hi all,
I hav this query which is not using index..
select * from contactEnquiries where Enquiry_id=@id or @id=''
Here if the @id value is passed '' it will show all the rows..
but its not using index even if i set @id value other than ''
can anyone help me rebuild this query to get better performance..??
Thanks in advance...
March 20, 2007 at 7:00 am
First, the low hanging fruit. Is the column Enquiry_Id an integer (most ID columns are, why I'm asking)? If so, it looks like you're passing a string & letting it get converted.
Anyway, the problem is the OR logic. It forces the query to use a table scan. Actually, you're going to get a table scan any time you pass in ''. But, a quick & easy solution would be:
declare @id varchar(50)
set @Id = NULL
IF @Id IS NOT NULL
SELECT * FROM HumanResources.Employee
WHERE EmployeeId = @Id
ELSE
SELECT * FROM HumanResources.Employee
There are better ways and I'll look some up in a bit. Have to run to a meeting.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 20, 2007 at 8:36 am
OK. Maybe I'm wrong.
I tried several things and the original is still the performing best.
I thought this would work:
SELECT *
FROM x
WHERE 1 = CASE WHEN '' = @id THEN 1
WHEN keycol = @id THEN 1
END
But that resulted in a scan too. COALESCE after setting the @id to null resulted in a scan because it's basically a where clause as well. At this point, I'll keep an eye on the thread to see what other people post because I'm a bit stumped.
One of my co-workers suggested getting the good query plan, storing it and then forcing it's use since you're in 2005. I'd suggest you add the query hint to force a recompile if you go with the first suggestion.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 20, 2007 at 9:22 pm
"select * from contactEnquiries where Enquiry_id=@id or @id=''"
Maybe this is too easy (although I hope not because we do it this way in a lot of places!):
select * from contactEnquiries where (@id='' or Enquiry_id=@id)
Whether this solves your problem or not, it's much better, as if the @id='', it doesn't evaluate the 2nd half of the WHERE clause if it's in this order. The SQL guys before me never noticed this, which means other people probably haven't either.
Hope this helps,
Rick
Rick Todd
March 21, 2007 at 3:46 am
That's not ideal either because, at optimisation, the query optimiser can't evaluate the first half of the or. SQL doesn't have short circuit evaluation
The only way I've managed to deal with this kind of thing in the past was to have three stored procs, one for the case where there is a variable passed, on for all the records then one to choose between them.
You can try ISNULL. I've had limited success with it, but my queries are usually a lot more complex. Also, an optimise for hint might be useful. What's the most common form of the query? All rows or one row?
Assume this is within an sp with a param of @EmployeeID
DECLARE @EmpID VARCHAR(6)
SET @EmpID = ISNULL(CAST(@EmployeeID as VARCHAR(6)),'%')
SELECT * FROM Employees where EmployeeID like @EmpID
OPTION (OPTIMISE FOR (@EmpID = 1)
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
March 21, 2007 at 6:16 am
Whew. I thought I was going nuts for a minute when that turned out not to be as easy as I originally thought. We all came to the same conclusion where I work. The problem is basically listing two different access methods to the data, therefore, you have to optimize each seperately. We've generally done this the same way as you, three procs, a wrapper and the two different access methods.
By the way, simplying evaluating all the rows first doesn't in any way help the optimizer use the index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply