Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 
share|improve this question
7  
Does the update process have a "Blocked By" column in Activity Monitor? –  Andomar May 24 '13 at 15:23
2  
Can you add table structure, indexes and the query please –  gbn May 24 '13 at 15:26
3  
@lucky, since the query is blocked by SPID 53, could you examine what is it doing with DBCC INPUTBUFFER (53)? –  Travis Gan May 24 '13 at 15:45
2  
@lucky, How long this SPID process has been running? Is this SPID the leading blocking SPID? Execute SP_WHO2 and examine please. –  Travis Gan May 24 '13 at 15:57
2  
Please post the estimated query plan. Preferably as a .sqlplan file, or as an image. –  usr May 24 '13 at 16:21
show 20 more comments

migrated from stackoverflow.com May 24 '13 at 15:25

This question came from our site for professional and enthusiast programmers.

3 Answers

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
share|improve this answer
    
@ gbn .. thanks that's a great idea.. but as data is in millions this process will take time....i was thinking may be there is a way to find out the progress of query? –  Lucky May 24 '13 at 15:51
1  
Why would it take 4 days to scan "millions" of rows? No matter how big and heavily indexed the rows might be, that should not take 4 days. The root of the problem is still unknown. –  usr May 24 '13 at 15:54
1  
If you regualrly deal with large data, what about you get a proper server for that? Put the data on a SSD etc.. –  TomTom May 24 '13 at 15:55
1  
@Lucky sure. I was addressing the answer. There is something wrong that we have not found yet. It is not the query by itself or the hardware. That would never amount to 4 days of duration. –  usr May 24 '13 at 15:56
3  
Given that the query is joining a 3 character portion of a column to a 3 character portion of another column, the result will more than likely contain duplicates. This is much worse than merely updating millions of rows. I bet it is scanning through a work table in the billions. –  datagod May 25 '13 at 1:24
show 3 more comments

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.