SQL Server Performance

using 'like '%''

Discussion in 'T-SQL Performance Tuning for Developers' started by JHP, Jul 23, 2003.

  1. JHP New Member

    I am trying to make a stored proc where it has 1 input parameter and the select
    query inside the sp used the parameter in its where clause. something like this...

    ---------------------------------
    create proc test
    @field1 varchar(10)='%'
    as

    select * from table1
    where field like @field1
    ---------------------------------

    the @field is supposed to be optional. so this select query
    can actually return the whole table1 or depending on the paramter, return only sub-data.
    I gave '%' as the default value for @field1. I do not want to write the same select query
    depending on the @field1 value.

    Does a query like

    "select * from table1 where field like '%'" is slower than "select * from table1"?





  2. bambola New Member

    Both queries will perform a table scan. There is a difference though. if your field value is NULL, SELECT * from table will show it while SELECT * from table WHERE field LIKE '%' will not.

    Bambola.
  3. Whiner New Member

    To answer your last question, yes, the like query is slower than the straight select without a where clause.<br /><br />Regarding your query, the @field1 value of '%' won't fully work as a wild card when passed as it won't return rows with NULL values in column field. <br /><br />Try this procedure if you need NULL values returned:<br /><br />------------------------------------------------------<br />create proc test<br />@field1 varchar(10)= NULL <br /><br />as<br /><br />select * from table1<br />where ISNULL( field , '' ) = ISNULL( @field1 , ISNULL( field , '' ) )<br />------------------------------------------------------<br /><br />It's performance will be slow in all cases[<img src='/community/emoticons/emotion-2.gif' alt=':D' />], but the data will be complete and correct.<br /><br /><br /><br />-- Greg Haselmann
  4. bambola New Member

    To find the NULL values it is enough to do WHERE field_name IS NULL.
    As for which would be faster. From the test I ran I did not notice much of a difference
    between the two.

    If you are passing only one variable to the stored procedure and column field1 is indexed, I'd suggest you write a simple IF statement.


    CREATE PROCEDURE test
    (
    @field1 varchar(10) = '%'
    )
    AS
    SET NOCOUNT ON

    IF ISNULL(@field1, '') = ''
    SELECT * -- here I suggest you specify the columns you need
    FROM table1
    ELSE
    SELECT *
    FROM table1
    WHERE field like @field1
    If column is not indexed you could write it using ISNULL

    WHERE ISNULL(@field1, '') = '' OR (field like @field1)

    OR COALESCE

    WHERE COALESCE(@field1, a_field ) = a_field

    Bambola.

Share This Page