SqlCommand shows bad performance when passing text parameter for LIKE

Question

I've encountered quite a wierd problem:

I form SQL server command dynamically and one of its part is a group of LIKE tests, that are used as textual search on several columns and tables. It looks like:

SET @text = '%' + REPLACE(REPLACE(REPLACE(@text, '!', '!!'), '%', '!%'), '_', '!_') + '%'

INSERT INTO
  @textFiltered
FROM
  @documents d 
  LEFT JOIN docData t1 WITH (NOLOCK)
    ON t1.ID = d.ID AND
       (t1.Topic like @text escape '!'
        OR t1.Subject like @text escape '!')
  LEFT JOIN docData2 t2 WITH (NOLOCK)
    ON t2.ID = d.ID AND 
       t2.Name like @text escape '!'
WHERE
  t1.ID IS NOT NULL 
  OR t2.ID IS NOT NULL

(Surely, that's not the best way to do textual search, still that's not the point)

Now, when I create SQLCommand in C#, like this:

using (var cmd = new SqlCommand())
{
   cmd.CommandText = cmdText;
   cmd.CommandType = CommandType.Text;
   cmd.Connection = connection;
   cmd.Parameters.Add("text", NVarChar, 4000).Value = searchText;
   var reader = cmd.ExecuteReader();
   ....
}

performce of executing is very poor (say, 8 seconds), while executing same query in SQL Management Studio much faster (say, 500 ms). However, if instead of passing text as parameter, I embed it into text of SQL with:

DECLARE @text nvarchar(max)
SET @text = '<embedded-text>'

then SqlCommand also runs fast. What's even more strange this behavior seems to be correlated with set of columns that are used in LIKE clauses (didn't figure how). Types of those columns can be nvarchar(size), nvarchar(max), ntext.

I suspect that problem is with parameter - maybe it's type is incorrect or smth else.

P.S. Tried to create parameter with size = length of searched text + 1 - didn't help.

% tags are appended in SQL. Added line to sql text that appends them and masks '%', '_' symbols in input. — Quercus, Feb 26 '13 at 05:54
I've had a similar issue (http://stackoverflow.com/q/2041484/87698). Apparently, the SQL Server optimizer chooses a bad query plan sometimes. :-( — Heinzi, Feb 26 '13 at 06:06

score 0 · Answer 1 · answered Mar 04 '13 at 15:51

My best guess will be that it is related to the query plan that SQL Server is choosing for you. there is big difference between using constant varchar which the SQL server can evaluate against existing statistics, and using arbitrary variable which the server know nothing about.

you can try OPTION (RECOMPILE) hint. although this hint will cause the Stored procedure to be compiled on every call, it'll also allow the SQL Server to search for the best plan for the given values, and maybe in your case it will be good trade off.

you could also add the query plan for both option of the stored procedure, and maybe someone will be able to see the difference, and pin point the exact problem.

SqlCommand shows bad performance when passing text parameter for LIKE

1 Answers1