stored proc timeout question

Question

stored proc timeout question

Peter LEvine

SSC-Addicted

Points: 462
More actions
May 18, 2004 at 2:48 pm

#85671

I am using a stored proc to query a database and return values. The stored proc has to utilize several temp tables, including a table cursor. (Too long a story to explain.)
The problem I am encountering is that I get a "timeout expired" message.
This does not occur all the time...only when the returned record sets are very large.
Is there a way I can adjust the timeout properties?
Thanks.
May 21, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alamir SSCertifiable Points: 6085 More actions · Answer 1

When you use complicated queries joined from several tables, These tables are updated frequently, then you lock these tables (or you will wait for previous transaction lock on these tables to be completed to put your locks on them) .. so all other poeple using these tables are locked too (so hang happened - because they are in the queue of lock of these tables)!!

the best thing is to run the query on the server .. and with each table name in the query, add this statement "with (nolock)" .. so you read data as it is (not recently updated = not commited transaction), and you don't wait for locks to be free and you don't lock these tables, and any other transaction will complete successfully (for you and others) .

you will write something like this:

select Invoices.*, Situation.*

from Situation with (nolock) INNER JOIN Invoices with (nolock)

ON Situation.SituationID = Invoices.Situation

where Situation.SituationID =1

Disadv:

- when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you can see it at any other time.

Adv:

- no hang at all

- very fast Response

- Little summary for Locks in SQL Log file.

also : you may need to add more Memory (RAM) when server still hangs a little after transfering your queries to path-through queries.... becuase every transaction or query is done on server first, then processed data is passed to your PC.

i hope this will help you

Alamir Mohamed
Alamir_mohamed@yahoo.com

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 2

The timeout can be adjusted, but it depends on how you are executing the procedure.

If you are using Query Analyzer you can find it in Tools / Options / Connections / Query time-out; by default, it is 0, meaning "never time-out".

If you are using ADO, set the CommandTimeout property (of the Command object or of the Connection object).

Razvan

Peter LEvine SSC-Addicted Points: 462 More actions · Answer 3

Thanks Razvan, the connection timeout settings were among the first things I checked, and they were set to 0 in all the proper places.

Thanks Alamir, I'll try the "with no lock" to see how that goes. I would up running adjustuing the language and running the query over a weekend. It took about 50 hours to complete.

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 4

Peter,

There are two different properties: ConnectionTimeout and CommandTimeout.

The first one (ConnectionTimeout) applies only to the Connection object and is used when the connection is established for the first time.

The second one (CommandTimeout) applies to the Command object as well as to the Connection object and is used every time an Execute is invoked.

Razvan

PS. If it takes so much time, I suggest you make some more indexes (where appropriate) and use set-based operations instead of cursors.