SQL Server Performance

Like clause performance

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by TakalaneSesame, Dec 7, 2006.

  1. TakalaneSesame New Member

    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?
  2. Madhivanan Moderator


    Better you check the condition

    If @dropdown_Value is null
    --Return all
    else
    --Return specific data

    Madhivanan

    Failing to plan is Planning to fail
  3. TakalaneSesame New Member

    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 (20x20?).
  4. Madhivanan Moderator

    You can use where condition

    Where (@dropdown is NULL or column=@dropdown)

    Madhivanan

    Failing to plan is Planning to fail
  5. TakalaneSesame New Member

    Simple and effective. Thanx
  6. Adriaan New Member

    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)
  7. TakalaneSesame New Member

    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?
  8. Adriaan New Member

    Test it. Compilation time for the statement will be negligeable for large resultsets.

Share This Page