September 20, 2004 at 12:39 pm
You will see my queries below. Query 1 uses the value of a varchar variable to determine what to filter using the WHERE clause. Query 2 is hard-coded to perform the same action as depicted in Query #1. When running both of these queries, Query #1 uses a completely off the wall index, while Query #2 uses the index it is expected to use, and hence performs a LOT better. I have already done an sp_recompile on tblGlobalUsers to make sure the stored procedure that queries this table is recompiled, I have defragged the indexes with DBREINDEX, etc. Can't think of why these use different execution plans, and why Query #1 picks such an off the wall index. We have also updated the statistics on tblGlobalUsers as well.
Is there something I can do to make this perform better? Maybe a better way to dynamically generate the WHERE clause?
Query 1:
DECLARE @SearchBy varchar(20)
DECLARE @SearchFor varchar(20)
SET @SearchBy = 'email'
SET @SearchFor = 'email@domain.com'
SELECT UID, billMethod, fname, lname, memLevel, nick, d_t, password, email,
accountClosed, cancelPend
FROM tblGlobalUsers
WHERE
CASE @SearchBy WHEN 'password' THEN password ELSE '' END
LIKE CASE @SearchBy WHEN 'password' THEN @searchFor ELSE '' END
AND CASE @SearchBy WHEN 'email' THEN email ELSE '' END
LIKE CASE @SearchBy WHEN 'email' THEN @searchFor ELSE '' END
AND CASE @SearchBy WHEN 'fname' THEN fname ELSE '' END
LIKE CASE @SearchBy WHEN 'fname' THEN @searchFor ELSE '' END
AND CASE @SearchBy WHEN 'lname' THEN lname ELSE '' END
LIKE CASE @SearchBy WHEN 'lname' THEN @searchFor ELSE '' END
AND CASE @SearchBy WHEN 'uid' THEN uid ELSE 0 END
= CASE @SearchBy WHEN 'uid' THEN CAST(@searchFor AS int) ELSE 0 END
ORDER BY lname
Query 2:
SELECT UID, billMethod, fname, lname, memLevel, nick, d_t, password, email,
accountClosed, cancelPend
FROM tblGlobalUsers
WHERE email = 'email@domain.com'
September 20, 2004 at 1:19 pm
SQL Server needs to pick one and only one access path for your SELECT statement. Since there are 5 columns being compared it will assume that every one is being used all the time which will never be the actual case due to the CASE statements embedded into the WHERE clause.
As for a "better" way, what I consider a more readable way would be as follows:
DECLARE @SearchBy varchar(20)
DECLARE @SearchFor varchar(20)
DECLARE @SearchForPassword varchar(20)
DECLARE @SearchForEmail varchar(20)
DECLARE @SearchForFName varchar(20)
DECLARE @SearchForLName varchar(20)
DECLARE @SearchForUID int
SET @SearchBy = 'email'
SET @SearchFor = 'email@domain.com'
if @SearchBy = 'password' set @SearchForPassword = @SearchFor
else if @SearchBy = 'email' set @SearchForEmail = @SearchFor
else if @SearchBy = 'fname' set @SearchForFName = @SearchFor
else if @SearchBy = 'lname' set @SearchForLName = @SearchFor
else if @SearchBy = 'uid' set @SearchForUID = Cast( @SearchFor AS int )
else RAISERROR ( 'Invalid @SearchBy option.', 16, 1)
SELECT UID, billMethod, fname, lname, memLevel, nick, d_t, password, email,
accountClosed, cancelPend
FROM tblGlobalUsers
WHERE
password LIKE ISNULL( @SearchForPassword, password )
AND email LIKE ISNULL( @SearchForEmail, email )
AND fname LIKE ISNULL( @SearchForFName, fname )
AND lname LIKE ISNULL( @SearchForLName, lname )
AND uid = ISNULL( @SearchForUID, uid )
ORDER BY lname
If you are not going to be passing in % or _ symbols in your SearchFor clause then you should definately use equality checks instead of LIKE checks.
One way of getting an optimal index choice is to use dynamic SQL to build only the WHERE clause components you actually want to use. Another way would be to set up an IF block to execute any one of the 5 versions of the SELECT statement based on the SearchBy value. If the above version of the query doesn't help out the optimizer for you and neither of the other two suggestions sound good to you there is another option I can think of. Should the query be a lot larger than what you gave and therefore not something you would care to replicate 4 times and maintain 4 versions you could perform a double hit on the table. Basically, set up an IF block to query only the primary key value(s) based on the SearchBy choice. Once you have that, set up a single SELECT statement using a WHERE clause on the primary key. If it is possible to have multiple rows returned then you can store the Key list in a @Temp table. The final query would then be a SELECT against the @Temp table LEFT JOINed against the tblGlobalUsers table. If the primary key is a single column then you could alternatively use an IN clause as well.
September 21, 2004 at 3:29 am
>> Is there something I can do to make this perform better?
Yes. SQL Server doesn't 'know' T-SQL - help it out, tell it what you want more explicitly
>> Maybe a better way to dynamically generate the WHERE clause?
There is no good way to use dynamically generated SQL.
Remember that there are no prizes for shortest code. Replace your SELECT statement with this:
IF @SearchBy = 'password' SELECT UID, billMethod, fname, lname, memLevel, nick, d_t, password, email, accountClosed, cancelPend FROM tblGlobalUsers WHERE password LIKE @SearchFor ORDER BY lname ELSE IF @SearchBy = 'email' SELECT UID, billMethod, fname, lname, memLevel, nick, d_t, password, email, accountClosed, cancelPend FROM tblGlobalUsers WHERE email LIKE @SearchFor ORDER BY lname ELSE IF @SearchBy = 'fname' SELECT UID, billMethod, fname, lname, memLevel, nick, d_t, password, email, accountClosed, cancelPend FROM tblGlobalUsers WHERE fname LIKE @SearchFor ORDER BY lname ELSE IF @SearchBy = 'lname' SELECT UID, billMethod, fname, lname, memLevel, nick, d_t, password, email, accountClosed, cancelPend FROM tblGlobalUsers WHERE lname LIKE @SearchFor ORDER BY lname ELSE IF @SearchBy = 'uid' SELECT UID, billMethod, fname, lname, memLevel, nick, d_t, password, email, accountClosed, cancelPend FROM tblGlobalUsers WHERE uid = (cast(@SearchFor as int) ORDER BY lname
Even a child - or the SQL Server optimizier - can work out which indexes to use here.
September 21, 2004 at 7:55 am
AKM,
We have done exactly that. After toying around with different suggestions other people posted on different forums we came to the conclusion that it will never do what it should unless we code it like you have shown above. Thanks for your time!
Shawn
September 21, 2004 at 1:10 pm
bergshawn,
AKM's code is the most optimal performance wise, but the tradeoff is a potential maintenence nightmare (that's your "prize" for writing less code).
Using the case statement is extremely un-optimal, as it confuses the compiler.
Aaron's use of "isnull" is a much better option, only use the "=" sign instead of "Like". This is pretty optimal, and isn't going to be much slower than using "IF...THEN".
If you do decide to use "IF...THEN", you should use it like this:
IF @SearchBy = 'password'
BEGIN
SELECT UID, billMethod, fname, lname, memLevel, nick, d_t, password, email, accountClosed, cancelPend
FROM tblGlobalUsers
WHERE password LIKE @SearchFor
ORDER BY lname
return
END
IF @SearchBy = 'email'
BEGIN
SELECT UID, billMethod, fname, lname, memLevel, nick, d_t, password, email, accountClosed, cancelPend
FROM tblGlobalUsers
WHERE email LIKE @SearchFor
ORDER BY lname
RETURN
END
It will compile the same, and it's much easier to read.
PS: You should try removing the order by and see if that affects the execution. Sometimes ordering can best be done on the client side.
cl
Signature is NULL
September 21, 2004 at 1:28 pm
Not that I have any real problem with the code Calvin posted but I will put in my 2 cents. I prefer using BEGIN and END clauses as they make the code more clearly defined sectionally and also make tacking additional code in later on less error prone (the second statement inside an IF would break things without BEGIN and END already in place). I personally prefer the IF / ELSE IF structure as it is more direct and there is no confusion at a top level glance at the structure that only one of the sections will execute. I also find that I prefer to avoid RETURN statements in the middle of a stored procedure for a couple of reasons. One is that if you don't read the SP from the top down you will not mistakenly think that the code at the bottom will always execute. I don't follow that religiously, but I make it a general policy (mostly RAISERROR and "if @@ERROR" get followed by a return but there are some exceptions). Another reason I avoid returns is that when trouble shooting a SP it often helps out to copy/paste the SP into Query Analyzer and quickly convert it to straight T-SQL in order to play with it in a private environment. Since RETURN fails outside a SP you have to figure out how to deal with the situation in your Query Analyzer T-SQL.
Anyhow, good luck and enjoy.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply