UPDATE and REPLACE for lots of rows

user_8947333 0
Viewed 107

I have 17 millions rows in my table, some of them contain an URL in the [url] column, and I want to replace some part of the URL in all my rows.

I have 8 millions rows in my table that need to be updated.

I already optimized my query a little, doing the replace by batches of 4,000, but it's still taking very long:

declare @Rows INT,
@BatchSize INT;

SET @BatchSize = 4000;
SET @Rows = @BatchSize; -- initialize just to enter the loop
while (@Rows = @BatchSize)
BEGIN
UPDATE TOP (@BatchSize) [dbo].[table]
SET
  [url] = replace([url], 
  '?sv=2019-12-12&foo=bar',
  '?sv=2020-02-10&bar=foo')
where [url] like '%foo=bar'
END;

Do you have any idea how I can improve this task?

optimization sql-server sql
2 Answers

The problem might be that each update needs to scan the entire table because of the WHERE clause. One solution -- ironically -- is to skip the batches. The UPDATE will take longer but it will scan the table only once.

A second alternative is an index to optimize the WHERE clause. There are three approaches for this clause:

  • Use a full-text index.
  • Create an "expression-based" index, which in SQL Server requires adding a computed column and then indexing on the computed column.
  • Create a filtered index.

The third is the simplest method. However, alas, SQL Server does not allow like in a filtered index, so it doesn't work in your case.

So, try this version:

alter table t add url_suffix as (right(url, 7);

create index idx_table_url_suffix on table(url_suffix);

Then you can phrase the where logic as:

where url7 = 'foo=bar';
user_11440350

You may want to take a look the MySQL string functions. They may allow you to do the whole thing in a SQL statement (since you're just doing a replace).

Something like:

UPDATE table SET some_text_row = replace(some_text_row, 'some phrase', 'some other phrase') WHERE some_text_row LIKE '%some phrase%';

user_163132310
Related