Two hundred and 29 Million rows in the table

Question

Two hundred and 29 Million rows in the table

Varun R

Ten Centuries

Points: 1171
More actions
June 1, 2012 at 4:18 am

#261949

Hi All,
I have (two hundred and 29 million ) records in a table. we have a procedure which is updating the data in the table. there are 4 updates running inside the procedure. It is taking around 3 days to complete the script. How I can optimize the script?
1. I have a clustered index in the table
2. 3 Non clustered index in the table
3. I used database page compression
There is no missing indexes in the table. I went for another approach ie batch update using while loop. It is also taking extra time. I am restricted to partition the table. Is there any other approach to make the procedure in 1 0r two hours?
Regards,
Varun R

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Satish Nagaraja SSCrazy Points: 2632 More actions · Answer 1

Asumming these updates are based on particular period of time say on 6 months of data , but it is scanning older data as well then probably partition is way to go.

Also seen in most cases the batch updates are based on where clause which runs for hours selecting the rows which needs updation if this case it would be good idea to check how many rows it is fetching and does it do index scan.

Cheers

Sat

Cheer Satish 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Partitioning is not for performance.

Please post query, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Varun R Ten Centuries Points: 1171 More actions · Answer 3

This is a kind of data warehouse table. we are creating reports after running this procedure. It is doing Index scan too.. There is an auto increment column named ID , I had done batch (100000) update by putting where condition in the column ID . In the execution plan , it is updating the clustered index.That's also taking time.

R.P.Rozema SSChampion Points: 12317 More actions · Answer 4

Have you tried reducing the 4 updates into a single one, possibly even using the merge statement to do any needed insert/delete actions in one single pass too?

And what sort of updates are you doing? Does your server need to grow your db or the tempdb to perform your updates? In this case you could go and pre-allocate the db(s) some more space to make it speedier.

Possibly removing all indices (but leave the primary key) from the table(s) before running the update and recreate them afterwards can help a great deal too.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Per Gail's request above, please post query, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/quote]

Please understand that we can't see from here what you see there, unless you want to grant us VPN access and sys admin rights to your server (which I'm pretty sure you can't and won't). You have to help us help you.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Varun R (6/1/2012)
Hi All,
I have (two hundred and 29 million ) records in a table. we have a procedure which is updating the data in the table. there are 4 updates running inside the procedure. It is taking around 3 days to complete the script. How I can optimize the script?
1. I have a clustered index in the table
2. 3 Non clustered index in the table
3. I used database page compression
There is no missing indexes in the table. I went for another approach ie batch update using while loop. It is also taking extra time. I am restricted to partition the table. Is there any other approach to make the procedure in 1 0r two hours?
Regards,
Varun R

How many rows are you trying to update with each UPDATE statement?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Varun R Ten Centuries Points: 1171 More actions · Answer 7

Thanks for all your answers. I will try give more information in coming days.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Varun R (6/3/2012)
Thanks for all your answers. I will try give more information in coming days.

Just checking. You all set?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Varun R Ten Centuries Points: 1171 More actions · Answer 9

Varun R

Ten Centuries

Points: 1171

June 20, 2012 at 1:12 am

#1503247

No jeff ........still working on it.