CODEWITHSUNDEEP
sql-serverstored-procedures
Sukhjeevan
Sukhjeevan

Reputation: 3156

Stored procedure execution time out problem

I have created a stored procedure.When i execute it in query analyzer it takes 47 sec. So my web application throwing Timeout exception. Can we do any modification in my code or web.config file instead of doing changes in stored procedure to resolve this problem.

I have following settings in my config file

<sessionState mode="StateServer" stateConnectionString="tcpip=127.0.0.1:42424" sqlConnectionString="data source=127.0.0.1;Trusted_Connection=yes" cookieless="false" timeout="120"/>

My Code is:

Dim ds = New DataSet()
        _dbCommand = _database.GetStoredProcCommand("GetDetailsForAll")
        _dbCommand.CommandType = CommandType.StoredProcedure
        ds = _database.ExecuteDataSet(_dbCommand)

here if i add one more line :

_dbCommand.CommandTimeout = 120

then my problem solved. so my question is i have set timeout in web.config file. Do i need to add _dbCommand.CommandTimeout = 120 line in my code also.

Upvotes: 1

Views: 1980

Answers (2)

Colin Mackay
Colin Mackay

Reputation: 19175

New answer based on updated question

The timeout in the connection string property in the .config file is the connection timeout, not the command timeout. The connection timeout is the amount of time to wait for the SQL Server to initially respond to the connection, not the amount of time it takes to perform the query.

If you want to set a command timeout in the config file, you have to code that youself as it is not part of the connection string.

Upvotes: 3

r&#233;my
r&#233;my

Reputation: 1052

enlarge the timeout in the db-connection-class .., but i would suggest to optimize the SP first !

Upvotes: 1

Related Questions