SQL Server Performance Forum – Threads Archive
Like clause performance
I would like to add functionality to an application where if a user selects something in a drop down then I will filter by that value, but if the user clears the drop down then I will get everything. I have decided to do this using a LIKE clause in a Stored Proc where I will have a parameter set either to the exact value (if selected) or to % (if cleared). My question is:1. Does SQL Server see the LIKE ‘%’ and ignore the filter or will it still attempt to filter, even though all results will be returned?
2. If an exact value is used with no wilcard characters (LIKE ‘Michael’) will SQL Server treat it as a = (which I assume will be faster than a LIKE), and also use the index on the name field?
3. Do the same rules apply to MySql?
Better you check the condition If @dropdown_Value is null
–Return all
else
–Return specific data Madhivanan Failing to plan is Planning to fail
I cannot really check the condition. It is one of those reporting things where the user is supposed to slice and dice the data as they wish, so there are 20 dropdowns by which they can filter this way. If I were to check for every dropdown, I would have to do it too many times (20×20?).
You can use where condition Where (@dropdown is NULL or [email protected]) Madhivanan Failing to plan is Planning to fail
Simple and effective. Thanx
How can it be that you cannot check whether your variable is null? Is the client app generating the query statement? Then why does it bother to use T-SQL variables? A lot of OR’s among the criteria will slow down your query (fact of life). With optional criteria, you will find that the best performance is when you use dynamic SQL, so you don’t have to include criteria that are not supplied anyway. DECLARE @P1 VARCHAR(10), @P2 VARCHAR(10)
DECLARE @SQL VARCHAR(1000) SET @SQL = ” IF @P1 IS NOT NULL
BEGIN
SET @SQL = ‘col1 LIKE ”’ + @P1 + ””
END IF @P2 IS NOT NULL
BEGIN
IF @SQL <> ” SET @SQL = @SQL + ‘ AND ‘
SET @SQL = ‘col2 LIKE ”’ + @P2 + ””
END IF @SQL <> ”
SET @SQL = ‘SELECT col FROM MyTable WHERE ‘ + @SQL
ELSE
SET @SQL = ‘SELECT col FROM MyTable’ EXEC (@SQL)
Thanks Adriaan. Doesn’t SQL Server analyse the OR from left to right and gets out when it finds the first true condition? Will it really make much performance difference to do a @Variable IS NULL (the left side of the OR) check for the non-specified conditions? Won’t this just be offset by the compiling of the dynamic statement?
Test it. Compilation time for the statement will be negligeable for large resultsets.
]]>