Post reply

Slow query with variable in search criteria

  • February 19, 2003 at 6:14 pm

    #174908

    Hi! Does anybody knows the reason why query 1 is slower than query 2

    1. DECLARE @VARIABLE varchar(3)

    SET @VARIABLE = '%'

    Select * from TABLE where FIELD like @VARIABLE

    2. Select * from TABLE where FIELD like '%'

  • February 19, 2003 at 6:28 pm

    #449323

    Showplans for these two queries would be different. Check those out and you will get the clue. I think Optimizer is smart enough to see that the second query could take advantage of an index on the column (if such exists). For the first query, because of the LIKE clause plus a variable, it does not have enough information to create more efficient showplan.

    Michael

  • February 20, 2003 at 5:27 pm

    #449444

    Thanks. I've checked and there are very different showplans. It takes 89% of time to filter through the variable. I will continue trying.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply