Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

updating 1,000 records takes 5-6 minutes I would like to decrease the time that takes Expand / Collapse
Author
Message
Posted Sunday, January 17, 2010 9:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 37,104, Visits: 31,658
itsmeman (1/5/2010)
This issue is done for now....


Two way street here... please explain how this issue is "done for now".


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #848959
Posted Sunday, January 17, 2010 9:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:57 AM
Points: 23,398, Visits: 32,250
Jeff Moden (1/17/2010)
itsmeman (1/5/2010)
This issue is done for now....


Two way street here... please explain how this issue is "done for now".


My guess, they ran the update that took 11 hours.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #848969
Posted Monday, January 18, 2010 9:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 37,104, Visits: 31,658
Lynn Pettis (1/17/2010)
Jeff Moden (1/17/2010)
itsmeman (1/5/2010)
This issue is done for now....


Two way street here... please explain how this issue is "done for now".


My guess, they ran the update that took 11 hours.


Heh... that would be my guess, as well. Hopefully the OP will come back and confirm one way or the other but that isn't the general nature of such posts.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #849255
Posted Monday, January 18, 2010 11:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 25, Visits: 76
I rewrote the script.

Got it down to an hour. :)


Post #849299
Posted Monday, January 18, 2010 11:51 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
no offense but that's still slow as hell.

I have a script that calculates the inventory per location as well as total per item for each of all 900 000 transaction in our system.

It takes 30 seconds to run those steps :

Copy the full table into temp table (only required columsn obviously)
Put a clustered index
Do the first calculation that updates 900 000 rows based per location.
Drop the clustered index
Build the clustered index based on different keys
run the update for the 2nd column.


All that takes only 25 seconds and assuming my math is still ok, that's over 4.5M lines written! Ok that's a nice server (2 cpus dual core, 16 GB of ram and san drives) but it's still a freaking heck of a lot faster than what you have right now.

A base comparaison would be that I update 150 000 rows a second with my code while you write 36 rows per seconds. That only 4166 times faster than your code... just saying .
Post #849313
Posted Monday, January 18, 2010 12:23 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 43,045, Visits: 36,205
What do you want to bet that there's still a cursor or two in that.

I had a process a while back that deleted 2 million rows, did aggregations over 150 million rows then inserted another 2 million. Total duration - 45 minutes.
Ok, that was a monster of a server, but still....



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #849324
Posted Monday, January 18, 2010 12:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
GilaMonster (1/18/2010)
I have the feeling this is less of a case of it not working on SQL 2008 than it simply isn't supported on SQL 2008. So it might run on SQL 2008, but there would be no vendor support in the case of problems.




Errh, wrong thread Gail .
Post #849331
Posted Monday, January 18, 2010 12:45 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 43,045, Visits: 36,205
Ninja's_RGR'us (1/18/2010)
Errh, wrong thread Gail .


I hate it when SSC does that. Answering 2 threads at the same time stuff often gets posted to wrong thread. Now where did the answer I wrote for here go.....

Fixed.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #849340
Posted Monday, January 18, 2010 6:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 25, Visits: 76
No cursors were used. 150,000+ records took an hour to change, yup, bad, bad, bad. Those 150,000 could have had many more charges associated with the records. Both tables that needed to be edited have 2 million plus rows. The hardware is older and is used extensively.

I'm sure I could have created a temp table slapped a non-clustered or clustered index and maybe I would have received better performance on those 150,000 records, but the reality is the two tables that were being modified were the culprit (all statistics were up to date).

The shop here has a bunch of very old outdated methods from its original culture that was hired, I don't know, 8 years ago, and everyone still uses those methods to do maintenance and manage data at customers sites.

I know venting is stupid, but I'm going to do it anyway. The process to improve anything around here due to the nature of the business is unwarranted, and I just don't have the ambition anymore to deal with all the hoops to jump through to get things more efficient.

Plus, I don't know what I'm doing in the first place. At least I'm making an effort to improve.
Post #849468
Posted Monday, January 18, 2010 6:59 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:57 AM
Points: 23,398, Visits: 32,250
itsmeman (1/18/2010)
No cursors were used. 150,000+ records took an hour to change, yup, bad, bad, bad. Those 150,000 could have had many more charges associated with the records. Both tables that needed to be edited have 2 million plus rows. The hardware is older and is used extensively.

I'm sure I could have created a temp table slapped a non-clustered or clustered index and maybe I would have received better performance on those 150,000 records, but the reality is the two tables that were being modified were the culprit (all statistics were up to date).

The shop here has a bunch of very old outdated methods from its original culture that was hired, I don't know, 8 years ago, and everyone still uses those methods to do maintenance and manage data at customers sites.

I know venting is stupid, but I'm going to do it anyway. The process to improve anything around here due to the nature of the business is unwarranted, and I just don't have the ambition anymore to deal with all the hoops to jump through to get things more efficient.

Plus, I don't know what I'm doing in the first place. At least I'm making an effort to improve.


Venting is good. If you really want to improve keep reading the posts on SCC, Wether forums or articles. Look at the problems, develop solutions and see how they compare to what others provide. If you still want to identify how to improve processes where you work, post what you can and see what transpires. Even if it is difficult to try and change things where you work, you actually have two choices. First is to just start looking for another job. The second is to just keep working and try to slowly implement changes that will eventually help improve things where you work.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #849470
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse