January 18, 2010 at 12:23 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2010 at 12:35 pm
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 :hehe:.
January 18, 2010 at 12:45 pm
Ninja's_RGR'us (1/18/2010)
Errh, wrong thread Gail :hehe:.
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2010 at 6:43 pm
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.
January 18, 2010 at 6:59 pm
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.
January 19, 2010 at 7:03 am
Very good advice. I love this site and the books offered for free have helped my knowledge tremendously. Dissecting SQL Execution Plans.... Perfect for tuning SQL.
March 18, 2010 at 2:35 pm
And I thought I was hard done by because a 1.5 million record update took 15 seconds!
March 18, 2010 at 2:54 pm
itsmeman (1/19/2010)
Very good advice. I love this site and the books offered for free have helped my knowledge tremendously. Dissecting SQL Execution Plans.... Perfect for tuning SQL.
Can we get those items that Gail and Lynn requested?
Execution Plans (actual)
DDL
DML
It will really improve the ability to provide you with some tuning help.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 1, 2010 at 3:18 pm
itsmeman (1/5/2010)
what advice would you all have to not using cursors if youhave sensitive data that needs to be checked to maintain its integrity?
I believe that SQL 2000 had a technology called 'Foreign Keys' to enforce something it called 'referential integrity.' Check constraints, triggers and even data types were available back then.
June 5, 2010 at 1:31 pm
Ion Freeman (6/1/2010)
itsmeman (1/5/2010)
what advice would you all have to not using cursors if youhave sensitive data that needs to be checked to maintain its integrity?
I believe that SQL 2000 had a technology called 'Foreign Keys' to enforce something it called 'referential integrity.' Check constraints, triggers and even data types were available back then.
Back then? :w00t:
Let me break some news for you. FK "technology" can be traced back in time much closer to the big bang than SS2000. You are correct, there is something -we crazy people- call "referential integrity" 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply