Random Timeout Expired

[TheBestOfMe]

Hi, I have a weid problem in a VB6 application using ADO conection to a SQL Server 2000. I do have sometime a timout expired on a stored procedure call that return a sigle row in a table (less than 1000 rows). In debug mode, if the error occured and I resume, I press F8 to see if the query can be reexecute a second time and it's working(les than 1 sec).

Sometime I have the error and sometime it works fine. And i set the CommandTimout and ConnectionTimeout to 300.

I don't know what it could be.

Thanks.

 

[MarkSweetland]

SQL Server is returning a cached recordset during the second execution of the stored procedure and results in a perceived "faster" execution. I don't believe it's an ADO and VB6 problem, but a T-SQL problem. Even though the timeoout occurs during the ADO call from VB, the sp should not take 300 seconds to complete on a 1000 row table. This can be verified by calling the stored procedure in the SQL Analyzer and checking the time to execute (if you execute the sp again with the same parameters, you'll see the results are returned quicker than tne first call).

Initially, you should look at your table structure, primary keys, indexes, and also any joins in the stored procedure.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[TheBestOfMe]

Hi,

the table has 10 or 12 colums with some primary keys. The table contain data pre-calculated, so I don'y have any join in my query. If I execute the sp in query analyser, the execution time is 1 sec or less. I think the network could be the cause of the problem, because the lan is 10mb and there are a lot of computer connected to the switch.