Problem on huge table.

[Kevin]

We have a huge table which has 12 million records. And when I run the following script, it took 50 hours. Is there anyone who can help? Thanks.

update TableA
set In=e.In, EA=e.ea, We=e.we
from TableA c, TableB e
where c.code=e.code

TableA 12,000,000 records.
TableB 750,000 records.
And have clustered index on each code field.

[Phil McCormack]

What does the query plan look like. Can you provide details of all indexes and spec of server you are running on, along with size of Tempdb and Transaction Log.



------------
Kevin at 3/14/01 1:18:35 PM

We have a huge table which has 12 million records. And when I run the following script, it took 50 hours. Is there anyone who can help? Thanks.

update TableA
set In=e.In, EA=e.ea, We=e.we
from TableA c, TableB e
where c.code=e.code

TableA 12,000,000 records.
TableB 750,000 records.
And have clustered index on each code field.

[Kevin]

Thank you for your response.
TableA: Clustered index on code, and noclustered indexes on other 3 fields (didn't use in that query). The table size is near 5GB. And index size is 400MB each.
TableB: Clustered index on code only.
Database: Data file: 8GB Log file: 3GB , autoexpand, autoshrink, truncate log on checkpoint.
Tempdb: Data 4GB, Log 100MB, autoexpand
And I can't get query plan now, cause it is still running.


------------
Phil McCormack at 3/15/01 5:28:15 AM

What does the query plan look like. Can you provide details of all indexes and spec of server you are running on, along with size of Tempdb and Transaction Log.


------------
Kevin at 3/14/01 1:18:35 PM

We have a huge table which has 12 million records. And when I run the following script, it took 50 hours. Is there anyone who can help? Thanks.

update TableA
set In=e.In, EA=e.ea, We=e.we
from TableA c, TableB e
where c.code=e.code

TableA 12,000,000 records.
TableB 750,000 records.
And have clustered index on each code field.

[Jonathan Cox]

- One possible cause is a full index. Check that you have enough padding in the index to allow for changed and movement. If one of the updated fields is the clustered index then the index will be restructured for every update.
I found that with a 65 million row table it was more efficient to remove the index, update and then re-index and recalculate statistics.
- It may be more efficient to the 'join' syntax rather than the 'where x=x' syntax.
- tempdb may be too small.

Just a few thoughts!
Jonathan

------------
Kevin at 3/14/01 1:18:35 PM

We have a huge table which has 12 million records. And when I run the following script, it took 50 hours. Is there anyone who can help? Thanks.

update TableA
set In=e.In, EA=e.ea, We=e.we
from TableA c, TableB e
where c.code=e.code

TableA 12,000,000 records.
TableB 750,000 records.
And have clustered index on each code field.

[Kevin]

Thank you for your advice. I will try to use join instead of where. And I didn't update that clustered indexed field in the query, do you think I'd better remove the index or not?


------------
Jonathan Cox at 3/15/01 9:43:50 AM

- One possible cause is a full index. Check that you have enough padding in the index to allow for changed and movement. If one of the updated fields is the clustered index then the index will be restructured for every update.
I found that with a 65 million row table it was more efficient to remove the index, update and then re-index and recalculate statistics.
- It may be more efficient to the 'join' syntax rather than the 'where x=x' syntax.
- tempdb may be too small.

Just a few thoughts!
Jonathan

------------
Kevin at 3/14/01 1:18:35 PM

We have a huge table which has 12 million records. And when I run the following script, it took 50 hours. Is there anyone who can help? Thanks.

update TableA
set In=e.In, EA=e.ea, We=e.we
from TableA c, TableB e
where c.code=e.code

TableA 12,000,000 records.
TableB 750,000 records.
And have clustered index on each code field.

[Phil McCormack]

Did you say you had non-clustered indexes on all 3 of the other columns ?

If so, drop them, run the update and re-create. If there are indexes on these columns, then this may explain a few things. You are updating the clustered index leaf pages (data), and each non-clustered index. Because the non-clustered indexes hold the clustering key to get to the data, any page splits or re-ordering of the clustered index will then subsequently affect all non-clustered indexes also.

Please keep us posted regarding any progress.




------------
Kevin at 3/15/01 9:40:38 AM

Thank you for your response.
TableA: Clustered index on code, and noclustered indexes on other 3 fields (didn't use in that query). The table size is near 5GB. And index size is 400MB each.
TableB: Clustered index on code only.
Database: Data file: 8GB Log file: 3GB , autoexpand, autoshrink, truncate log on checkpoint.
Tempdb: Data 4GB, Log 100MB, autoexpand
And I can't get query plan now, cause it is still running.


------------
Phil McCormack at 3/15/01 5:28:15 AM

What does the query plan look like. Can you provide details of all indexes and spec of server you are running on, along with size of Tempdb and Transaction Log.


------------
Kevin at 3/14/01 1:18:35 PM

We have a huge table which has 12 million records. And when I run the following script, it took 50 hours. Is there anyone who can help? Thanks.

update TableA
set In=e.In, EA=e.ea, We=e.we
from TableA c, TableB e
where c.code=e.code

TableA 12,000,000 records.
TableB 750,000 records.
And have clustered index on each code field.

[joe]

1) outside of seeing a query pan that shows a glaring problem, is it possible that you have both tables on the same filegroup/file or on the same set of drives?

You will be updating 750,000 rows in table B, which is a whole lotta rows, and you'll be scanning through much of the rows of table B to do it. If they are both on
the same file or filegroup, this could cause resource contention.

2) Are other processes accessing or locking this table at the time you are trying to update it? use SP_WHO2 to see if your transaction is blocked.

3) Use performance monitor to make sure it is doing index scans and not page scans, and this can be done while the query is running.

4) Is the query still running after 50 hours? If so you might htink about killing it and letting it roll back over the weekend and then examine
your execution plan...you never know what it will turn up.


------------
Kevin at 3/14/01 1:18:35 PM

We have a huge table which has 12 million records. And when I run the following script, it took 50 hours. Is there anyone who can help? Thanks.

update TableA
set In=e.In, EA=e.ea, We=e.we
from TableA c, TableB e
where c.code=e.code

TableA 12,000,000 records.
TableB 750,000 records.
And have clustered index on each code field.