May 15, 2006 at 10:18 am
Hi Db Gurus,
Before getting into the problem I would like to give some back ground information.
I have a table which is the biggest table(ptcp) on the database size which contains rows 24954048.
I have a stored proc which actually populates a temporary table. Then the temporary is joined to the ptcp and updates (2 columns) on the table ptcp.
The total time taken for this proc to complete is 7 hours and this looks very weird for me.
These all the steps I have done.....
Dropped all the non-clustered index and ran the stored proc still then no performance improved.
Could any one suggest me the checklist to improve the performance for both code and server/database settting for this kind of data migration.
Thanks in advance.
Thanks,
Ganesh
May 15, 2006 at 12:23 pm
Have you looked at the query plan?
Is the temp table indexed?
For ptcp, you should defnintely have the join condition indexed (and probably clustered).
Without more detailed informatio those are the things that come to mind.
May 16, 2006 at 3:36 am
In addition to Pam Abdulla's reply :
If you're updating a varchar-type column, it may be possible sql has to relocate your row to another page, maybe even perform some pagesplits to solve this.
Maybe rebuild your clustering index with a resonable factor of free space before your perform your update.
With a single updatestatement, your db-log will suffer the full amount of the transaction.
Maybe for this operation switch to simple logging and update in reasonable chuncks of transactions so your log will not grow to heavy and rollsover for the next update.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 16, 2006 at 6:36 am
You did not mention how big percentage of the total number of table rows are affected by your UPDATE statement?
I assume here that you want to update all (100%) 25M rows:
If the rows (with the new data) will have a greater rowsize then before, probably it needs to relocate almost 100% of the rows (remember that the rows are stored sequentially in a page and if the fillfactor is not big enough you _will_ run out of space on _every single page_ allocated to your table). And that takes time...
Your greatest performance improvement here is to create a new table (with the two new columns) and insert ALL data JOINed with your temp table (so the two new columns get populated without row-relocation).
If you do not have enough space to do this in one go, try to do it in multiple steps (even though your DELETEs will probably consume much time).
Hanslindgren
May 7, 2011 at 4:00 am
Parallelization for queries (SELECT) is covered quite well by the SQL engine itself, but when it comes to large volume data modifications (UPDATE, INSERT, DELETE), the standard engine does parallelize towards best use of all available resources (disk, multiple cpu-cores, etc.).
Therefore you may also have a look into the approach of the free Community Solution of SQL Parallel Boost at Codeplex (sqlparallelboost.codeplex.com).
This approach can also be used to execute multiple SQL statements in parallel.
A purely SQL engine related parallelisation solution takes advantage of minimized complexity and has no 'external' components like SSIS involved, Furthermore it's the best performing solution regarding task splitting and synchronization, as it hasn't potential connection and communication overhead. The overall performance gain thru parallelisation with SQL Parallel Boost is up to 10 !
In case you don't wan't to rebuild your own solution, SQL Parallel Boost provides a self-contained pure T-SQL based solution, which can be easily embedded in existing applications and ETL process tasks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply