<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 7,2000
»
Strategies
»
updating 1,000 records takes 5-6 minutes I...
25 posts, Page 2 of 3
««
1
2
3
»»
updating 1,000 records takes 5-6 minutes I would like to decrease the time that takes
Rate Topic
Display Mode
Topic Options
Author
Message
Jeff Moden
Jeff Moden
Posted Sunday, January 17, 2010 9:26 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 32,906,
Visits: 26,792
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #848959
Lynn Pettis
Lynn Pettis
Posted Sunday, January 17, 2010 9:58 PM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 21,617,
Visits: 27,450
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
Jeff Moden
Jeff Moden
Posted Monday, January 18, 2010 9:43 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 32,906,
Visits: 26,792
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #849255
itsmeman
itsmeman
Posted Monday, January 18, 2010 11:32 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, January 27, 2011 9:06 AM
Points: 25,
Visits: 64
I rewrote the script.
Got it down to an hour. :)
Post #849299
Ninja's_RGR'us
Ninja's_RGR'us
Posted Monday, January 18, 2010 11:51 AM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 2:27 PM
Points: 21,357,
Visits: 9,537
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
GilaMonster
GilaMonster
Posted Monday, January 18, 2010 12:23 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 37,725,
Visits: 29,981
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
Ninja's_RGR'us
Ninja's_RGR'us
Posted Monday, January 18, 2010 12:35 PM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 2:27 PM
Points: 21,357,
Visits: 9,537
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
GilaMonster
GilaMonster
Posted Monday, January 18, 2010 12:45 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 37,725,
Visits: 29,981
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
itsmeman
itsmeman
Posted Monday, January 18, 2010 6:43 PM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, January 27, 2011 9:06 AM
Points: 25,
Visits: 64
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
Lynn Pettis
Lynn Pettis
Posted Monday, January 18, 2010 6:59 PM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 21,617,
Visits: 27,450
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 »
25 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
