SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


updating 1,000 records takes 5-6 minutes I would like to decrease the time that takes


updating 1,000 records takes 5-6 minutes I would like to decrease the time that takes

Author
Message
itsmeman
itsmeman
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 94
hello,

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.

joshua
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86479 Visits: 45238
Please 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.
http://www.sqlservercentral.com/articles/Best+Practices/61537/

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.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38993 Visits: 38508
Agree, I must, with Gail. Hard it is to help with what has been provided.

Cool
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)
Sunny-138471
Sunny-138471
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 266
Table 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 indexed
2. The index on the column which actually gets updated can be removed tempirarily and can be created once the index is complete
3. Any foreign key relationships on the actual column getting updated should be removed during the time of update

Thanks
Satish More
c00ler01
c00ler01
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 102
Can you describe what you are trying to do? Quite a lot of the times the cursors can be avoided...
itsmeman
itsmeman
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 94
This 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?
c00ler01
c00ler01
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 102
Can 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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86479 Visits: 45238
joshua.aaanderson (1/5/2010)

what advice would you all have to not using cursors if you
have sensitive data that needs to be checked to maintain its integrity?


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.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


badkow
badkow
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 57
SQL 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).
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38993 Visits: 38508
I 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.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search