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

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

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

StackExchange Database Administrators Q#43002, answer score: 8