Updating a table with millions of records, its been 4 days

Question

I am currently updating a table with millions of records, its been 4 days and query is still executing.

I checked the activity monitor its shows that query is running.

In event log there is no errors at all.

Performance wise:

• Tempdb in disk A (850 gb free space)
• database file in disk B (750 gb free space)
• 16 GB ram

Please suggest me what should i do?

The query

UPDATE
    dbo.table1
SET 
    costPercentage = ISNULL(t2.PaymentIndex, 1.0),
    t2.TopUp_Amt = (ISNULL(t2.PaymentIndex, 1.0) - 1.0)
    * ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00),
    Total_Tariff_Inc_t2 = ISNULL(t2.PaymentIndex, 1.0)
    * ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00)
FROM
    dbo.table2 t2
WHERE
    LEFT(dbo.test1.procodet, 3) = LEFT(t2.ProviderCode, 3) COLLATE database_default 

Answer 1

There is an interesting detail to this query that I did not spot at first. Thanks to Fabricio Araujo's answer I now see it: you are accessing two tables. I have never seen this kind of usage of the update statement before and I do not advise using it. I recommend you use the more intuitive join syntax per Fabricio's answer.

The likely cause is that the join between the two tables produces an extreme number of rows. This might happen if the LEFT(col, 3) expression produces duplicate values. If it produces 10 duplicates this will result in 100000x100000=10000000000 rows in the join result.

I do not think that indexing plays a role here. SQL Server can resolve this unindexed join just fine with a hash or a merge join. Does not take 4 days.

The other probably cause would be a cardinality underestimation of the join inputs or outputs. SQL Server might have chosen a loop join.

As this is still speculation I recommend that you post the query plan which will shed light on this issue.

Answer 2

This query requires you to scan every row in the table because

• I guess procodet or ProviderCode are not indexed
• Even if they were indexed, you have a LEFT which is a function on a WHERE predicate
• And you have COLLATE too which is effectively a function on a WHERE predicate

"a function on a WHERE predicate" means indexes won't be used

If you batch it (say on UPDATE TOP (10000) ... AND costPercentage IS NULL) then you need an index on costPercentage and this assume you are setting it.

The only solutions I see are

• populate a new table in batches, based on, say, the primary key
• create indexed, computed columns to hide the LEFT and COLLATE expressions, then run the update

Answer 3

First of all, change the query to:

UPDATE t1
SET 
    costPercentage = ISNULL(t2.PaymentIndex, 1.0),
    t2.TopUp_Amt = (ISNULL(t2.PaymentIndex, 1.0) - 1.0)
    * ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00),
    Total_Tariff_Inc_t2 = ISNULL(t2.PaymentIndex, 1.0)
    * ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00)
FROM
  dbo.table1 t1
  inner join dbo.table2 t2
    on LEFT(t1.procodet, 3) = LEFT(t2.ProviderCode, 3) COLLATE database_default 

As per indicated by Jeff Moden's first post in that discussion, your query is very similar to the one he warned about the "Halloween effect".

After that, those LEFT expressions must be indexed. gbn's answer give you the pointers of how to do that.