timeout error for update statement

The earlier solution works perfectly in updating the table but when I run it for over 10,000 lines it stops after doing a few hundred updates.  I recieve the following error:  "The timeout period elapsed prior to completion of the operation or the server is not responding."  

Is this due to the opening of connections over and over?  Is there any way to fix it?  Is there a more efficient way to do this?  If not I can do the update in smaller batches.  I was just wondering if I was doing something wrong.
InfoTeamAsked:
Who is Participating?
 
GhunaimaCommented:
Another way is to save all the values returned by the getallusers to a table first then use a single update command for updation.

Also in current logic use only the connection 'conn', there is no need to define "conn1". Also dont forget to close the connection at the end of the procedure.
0
 
radcaesarCommented:
How did you execute ur update?

.NET? If yes try below solutions. Before that, I suspect some performance in your code/server. Check that.

1. Connection Timeout for Connection. It could be solved by setting ConnectionTimeout property of Connection object in Connection String.
2. Timeout for Data access ( Command Object ). You can set CommandTimeout property to Command object. I recommend you set CommandTimeOut property to bigger one value.
0
 
InfoTeamAuthor Commented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
Before changing the defualt CommandTimeout consider adding an index on gpid.
0
 
Anthony PerkinsCommented:
And even before that, I would stronglly urge you to change that whole RBAR (Row By Agonizing Row) mess.
0
 
InfoTeamAuthor Commented:
Perk,
How else can you do it?  I think I only know the unfortunate RBAR mess way.  I'm sure its something with server...maybe.  Thanks for looking at it anyway.
0
 
Alpesh PatelAssistant ConsultantCommented:
Please increase the timeout period in Connectionstring.
0
 
Anthony PerkinsCommented:
How many rows are there in tblAssets?  Do you have an index on gpid?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.