Searching SQL Server with LIKE Operator

I have a problem when I try to read rows from SQL Server 2005 from code in C#

The idea:

In my database (SQL Server 2005 Express) there is a table with a column (of datatype ntext) containing HTML code.

In my C# application user can enter a sentence (HTML code) and search the rows with contains this sentence.

The query generated from my app is:

USE test   
SELECT   
 al.aal_Id As ID,   
 al.aal_Description As Opis,   
 au.au_Title As Tytul_szablonu,   
 au.au_Note As N开发者_JAVA百科azwa_szablonu   
FROM dbo.au_Allegro al   
LEFT OUTER JOIN dbo.au__Auction au ON (al.aal_AuctionId = au.au_Id)   
WHERE  
 au.au_Type = 11   
 AND al.aal_Description COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%%' ESCAPE '\'  

In my App I'm converting special characters (e.g. ',) and adding escape character.

User tries to search for very long sentence (about 7000+ chars), when he tries to do this the sqlserver.exe process consumes all of his RAM memory and search time is about 30+ minutes (he has about 1000+ rows in this table).

The query returns 0 rows.

When he tries to run (this same) query in SQL Server Management Studio the database shows results in few seconds (with rows).

In my app I use SqlDataAdapter:

System.Data.DataTable dt = new System.Data.DataTable();  
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();  
cmd.CommandTimeout = 0;  
cmd.Connection = conn;  
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(kwerenda, conn);  
try  
{
  da.Fill(dt);
}

I tried SqlDataReader:

dr = cmd.ExecuteReader();  
while (dr.Read())  
{  
  string id = dr["ID"].ToString();  
  string opis = dr["Opis"].ToString();  
  string tytul = dr["Tytul_szablonu"].ToString();  
  string nazwa = dr["Nazwa_szablonu"].ToString();  
  dt.Rows.Add(id, opis, tytul, nazwa);  
}

When I tried to simulate this in my test database I don't have any problems with search (this same) sentences.

Have you got any tips for me ?

I can't do any changes in user datatable, i can't go to him and check what happens.

Is the SQL command executing a stored procedure? If so you might be getting different query plans, which may explain the timing difference between the apps. Your ADO.Net call might be affected by something known as parameter sniffing, which can cause radically different query execution times.

There are a couple of things you can do to avoid this problem and yield consistent results.

1. Convert parameters to local variables inside of the stored procedure.
2. Disable the feature on the SQL server altogether.

Also your syntax looks suspect as John pointed out. It would be better to use a NVARCHAR(MAX) datatype for that column if possible NTEXT should be avoided as its been deprecated.

A better alternative to doing like searches on a non-indexed column like this is to utilize the SQL's Full Text Search which is optimized for these types of queries.

• http://msdn.microsoft.com/en-us/library/ms142571.aspx
• http://www.developer.com/article.php/3446891

A couple of things you might want to do.

First, don't use nText. SQL 2005 has a datatype called nvarchar(max). It's MUCH better for storing large amounts of text. Further, ntext was deprecated so save yourself some trouble and convert it now. See this link on how to successfully do this.

Second, the query you posted is unusual. You have a left outer join, but you have a where clause on the outer joined table. Because of the where clause it's being converted (hopefully) into an inner join. You should just write it that way OR move the au.au_type = 11 to be part of the join construct. I doubt you want the latter.

Third, when the client runs the query the first time through your app it is generating a query plan based on those parameters. Running the exact same query shortly thereafter in Management Studio is going to reuse that plan and cached data. Therefore the second pass will be fast so no surprise there.

Fourth, I don't think you posted the actual query that was run. I suspect there is some data in the parameter you are comparing which either isn't escaping properly OR is using one of the reserved characters such as '[', ']', ^, etc.