Very slow Search query

Question

Hi,

I'm using a query for searching some data in a Log Table. This table contains mutilple varchar(max) properties that we can not index. Also, I'm using the statement like wich is not very performant to find the text.

The query works well on a small period (search  the logs of 3 days). But on large period of time, it is very slow.

Is there a way to resolve this problem ?

Regards,

Answer 1

Please always state what version you are using.

In general, finding a substring of a larger string requires a lot of work. Even if you could index the varchar(max) columns, using LIKE with a wildcard at the beginning would mean the index could not be used.

Is there any other column in your queries that could be indexed and help reduce the number of rows that have to be searched?

You could investigate creating a covering index with all the columns in the query, and these can include varchar(max) columns.

You could also take a look at Full Text Search.

---

HTH, Kalen Delaney www.SQLServerInternals.com

Answer 2

Thanks,

Actually I'm using SQL server 2008.

I have 8 varchar(max) columns but I can't reduce the number of columns.

I'll explore the covering index, fulltextsearch and see the difference with SQL Server Profiler .

 

Regards,

Answer 3

Hi,

I've optimized the query thru covering indexes. But Full Text Search does not work.

Actually I'm using a temporary table in the query where I create a unique index , fulltext catalog , full text index (key = unique index and stoplist = system).

SQL Server compile it but I got any result on the exection. 

I've also tried by creating a table and testing the full text search, Same problem.

How to get full text search working ?

Regards,