InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 18:32:24 GMT20http://www.sqlservercentral.com/Forums/Topic842057-49-1.aspx[quote][b]Ion Freeman (6/1/2010)[/b][hr][quote][b]itsmeman (1/5/2010)[/b][hr]what advice would you all have to not using cursors if you have sensitive data that needs to be checked to maintain its integrity? [/quote] 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.[/quote]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" :-DSat, 05 Jun 2010 13:31:06 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspx[quote][b]itsmeman (1/5/2010)[/b][hr]what advice would you all have to not using cursors if you have sensitive data that needs to be checked to maintain its integrity? [/quote] 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.Tue, 01 Jun 2010 15:18:37 GMTIon Freemanhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspx[quote][b]itsmeman (1/19/2010)[/b][hr]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.[/quote]Can we get those items that Gail and Lynn requested?Execution Plans (actual)DDLDMLIt will really improve the ability to provide you with some tuning help.Thu, 18 Mar 2010 14:54:43 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxAnd I thought I was hard done by because a 1.5 million record update took 15 seconds!Thu, 18 Mar 2010 14:35:19 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxVery 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.Tue, 19 Jan 2010 07:03:51 GMTitsmemanhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspx[quote][b]itsmeman (1/18/2010)[/b][hr]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 [b]anymore[/b] 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.[/quote]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.Mon, 18 Jan 2010 18:59:00 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxNo 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 [b]anymore[/b] 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.Mon, 18 Jan 2010 18:43:34 GMTitsmemanhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspx[quote][b]Ninja's_RGR'us (1/18/2010)[/b][hr]Errh, wrong thread Gail :hehe:.[/quote]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.Mon, 18 Jan 2010 12:45:14 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspx[quote][b]GilaMonster (1/18/2010)[/b][hr]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.[/quote]Errh, wrong thread Gail :hehe:.Mon, 18 Jan 2010 12:35:03 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxWhat 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....Mon, 18 Jan 2010 12:23:22 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxno 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 indexDo the first calculation that updates 900 000 rows based per location.Drop the clustered indexBuild the clustered index based on different keysrun 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 :w00t:.Mon, 18 Jan 2010 11:51:46 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxI rewrote the script.Got it down to an hour. :)Mon, 18 Jan 2010 11:32:08 GMTitsmemanhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspx[quote][b]Lynn Pettis (1/17/2010)[/b][hr][quote][b]Jeff Moden (1/17/2010)[/b][hr][quote][b]itsmeman (1/5/2010)[/b][hr]This issue is done for now.... [/quote]Two way street here... please explain how this issue is "done for now".[/quote]My guess, they ran the update that took 11 hours.[/quote]Heh... that would be my guess, as well. :-D Hopefully the OP will come back and confirm one way or the other but that isn't the general nature of such posts.Mon, 18 Jan 2010 09:43:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspx[quote][b]Jeff Moden (1/17/2010)[/b][hr][quote][b]itsmeman (1/5/2010)[/b][hr]This issue is done for now.... [/quote]Two way street here... please explain how this issue is "done for now".[/quote]My guess, they ran the update that took 11 hours.Sun, 17 Jan 2010 21:58:34 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspx[quote][b]itsmeman (1/5/2010)[/b][hr]This issue is done for now.... [/quote]Two way street here... please explain how this issue is "done for now".Sun, 17 Jan 2010 21:26:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxI have to agree with Gail again. If you can provide us with more specifics, table DDL (CREATE TABLE statement(s)), sample data (series of INSERT INTO statement(s)) for then table(s), expected output based on the sample data. With that we could show you how to put together a set-based approach to solving the problem.Using cursors shows a procedural way of thinking about the problem at a row at a time. Instead, you should be looking at what do I want to do to this column.Sun, 17 Jan 2010 20:10:12 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxSQL Server is designed to handle data in set format. Handling data on a row by row basis requires a lot of times the use of cursors and cursors have some disadvantages associated with them(just google to read about them).Sun, 17 Jan 2010 19:42:41 GMTbadkowhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspx[quote][b]joshua.aaanderson (1/5/2010)[/b][hr]what advice would you all have to not using cursors if you have sensitive data that needs to be checked to maintain its integrity? [/quote]Write a set-based query. That's all I can say based on the vagueness of the question. If you can get more specific someone can probably give a more detailed answer.Tue, 05 Jan 2010 15:07:26 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxCan you give us example of checks that you are carrying out?basic checks should be enforced through CHECK,unique,null or not null,data type etc.Everything else I would validate through front end.but once again I don't know what you are trying to do...Tue, 05 Jan 2010 14:37:00 GMTc00ler01http://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxThis issue is done for now.... what advice would you all have to not using cursors if you have sensitive data that needs to be checked to maintain its integrity? is that a different question to post in a different section?Tue, 05 Jan 2010 14:25:49 GMTitsmemanhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxCan you describe what you are trying to do? Quite a lot of the times the cursors can be avoided...Tue, 05 Jan 2010 07:25:36 GMTc00ler01http://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxTable structure is important. 131000 is not really a big number. It would be great if you could provide the kind of indexes that exist on the tale and how the data is updated. AS of now I could give the following hints:1. The column based on which the update happens should be indexed2. The index on the column which actually gets updated can be removed tempirarily and can be created once the index is complete3. Any foreign key relationships on the actual column getting updated should be removed during the time of updateThanksSatish MoreTue, 05 Jan 2010 07:24:27 GMTSunny-138471http://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxAgree, I must, with Gail. Hard it is to help with what has been provided.Tue, 05 Jan 2010 07:24:01 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxPlease post SQL 2000 questions in the SQL 2000 forums in the future.Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.[url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]Shouldn't be any need for a cursor here. Of course, without seeing what you want to update and to what, it's hard to say for sure.Tue, 05 Jan 2010 07:21:31 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic842057-49-1.aspxhello,i know this says sql 2005.. but the platform I am running on is sql 2000.i have a database with 1.17 million records.I need to make a change to 131,000 of these records.The best way determined to handle this change is through a cursor.To update 1,000 records it takes 5-6 minutes. I think this is unacceptable because to update 131,000 records it will take roughly 11 hours.Being new to table and performance handling, can someone direct me in the right direction to decrease the amount of time it takes to update 1,000 records?thanks.joshuaTue, 05 Jan 2010 07:02:13 GMTitsmeman