CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2sql-update
Yaroslav
Yaroslav

Reputation: 6534

Slow updates using LIKE on where

I should do several updates on 2 tables on a couple of columns. Tables are both around 1 million rows. I must do the change on 3 different servers. The update is simple, just delete a substring. The substring is around 200 chars long and the columns are VARCHAR(MAX). The change will affect around one third of the rows. This is the query:

DECLARE @myVar varchar(250) = 'blablabla2345f25wdf34gqefblablabla...blablablabla'
UPDATE myTable
    SET myCol = REPLACE(myCol, @myVar, '')
WHERE myCol LIKE '%' + @myVar + '%'

The problem is that I began with one table and is taking loooong to execute, I stopped the execution after 10min. First server is just the test/sandbox server, but others are pre-production and production. Any recommendation on how to speed up the query?

Upvotes: 0

Views: 60

Answers (2)

D Stanley
D Stanley

Reputation: 152566

Three reasons (at least):

  1. You're filtering on an unindexed column expression, so a table scan is required
  2. You're searching in a VARCHAR(MAX) column, which may be stored in a separate page from the main table data, so additional I/O is required (plus additional overhead of string comparison)
  3. You're modifying VARCHAR(MAX) data, so the data may need to be reorganized to fit in a new space, causing additonal I/O.

Unless there's additional field that you can filter on I can't think of a way to speed it up. Running it in batches will reduce the amount of transaction log overhead and will show you incremental progress, which at least feels like it goes faster.

Upvotes: 2

Trenton Trama
Trenton Trama

Reputation: 4930

The reason it's slow is because your WHERE statement isn't hitting any indexes. Like can't use an index when the string begins with a wildcard. Depending on the server, you might be able to use an index if your search term was 'someterm%'

Is there any way you can fix your query so that there will be an index for it to look up?

Upvotes: 3

Related Questions