CODEWITHSUNDEEP
sqlsql-servert-sqloptimizationquery-optimization
GLlompart
GLlompart

Reputation: 271

How can I improve this endless query?

I've got a table with close to 5kk rows. Each one of them has one text column where I store my XML logs

I am trying to find out if there's some log having

<node>value</node>

I've tried with

SELECT top 1 id_log FROM Table_Log WHERE log_text LIKE '%<node>value</node>%'

but it never finishes.

Is there any way to improve this search?

PS: I can't drop any log

Upvotes: 4

Views: 300

Answers (5)

Nathan
Nathan

Reputation: 2775

Apart from implementing full-text search on that column and indexing the table, maybe you can narrow the results by another parameters (date, etc). Also, you could add a table field (varchar type) called "Tags" which you can populate when inserting a row. This field would register "keywords, tags" for this log. This way, you could change your query with this field as condition.

Upvotes: 1

Jonathan Wood
Jonathan Wood

Reputation: 67223

Unfortunately, about the only way I can see to optimize that is to implement full-text search on that column, but even that will be hard to construct to where it only returns a particular value within a particular element.

I'm currently doing some work where I'm also storing XML within one of the columns. But I'm assuming any queries needed on that data will take a long time, which is okay for our needs.

Another option has to do with storing the data in a binary column, and then SQL Server has options for specifying what type of document is stored in that field. This allows you to, for example, implement more meaningful full-text searching on that field. But it's hard for me to imagine this will efficiently do what you are asking for.

Upvotes: 1

Vince Pergolizzi
Vince Pergolizzi

Reputation: 6584

I don't think it will help but try using the FAST x query hint like so:

SELECT id_log 
FROM Table_Log 
WHERE log_text LIKE '%<node>value</node>%' 
OPTION(FAST 1)

This should optimise the query to return the first row.

Upvotes: 0

JonVD
JonVD

Reputation: 4268

A wildcarded query such as '%<node>value</node>%' will result in a full table scan (ignoring indexes) as it can't determine where within the field it'll find the match. The only real way I know of to improve this query as it stands (without things like partitioning the table etc which should be considered if the table is logging constantly) would be to add a Full-Text catalog & index to the table in order to provide a more efficient search over that field.

Here is a good reference that should walk you through it. Once this has been completed you can use things like the CONTAINS and FREETEXT operators that are optimised for this type of retrieval.

Upvotes: 9

JonH
JonH

Reputation: 33153

You are using a like query. No index involved = no good There is nothing you can do with what you have currently to speed this up unfortunately.

Upvotes: 0

Related Questions