CODEWITHSUNDEEP
.netsql
user1637901
user1637901

Reputation: 1

Stored procedure is taking time to execute causing timeout exception in .net

We want to SUM amounts for all dates in a range provided.

Logic is like - in while loop we are taking sum of amount column for date range and adding that sum in another variable

This is taking 1-3 minutes to executes depending on date range.

And because of this we are getting timeout exception in code side.

Can we make changes in stored procedure to reduce time or is there any other way to handle this in code so that exception will not be thrown.

Please Help...

Upvotes: 0

Views: 1986

Answers (3)

Hans Kesting
Hans Kesting

Reputation: 39255

If your query really needs that amount of time, you can prevent timeouts by setting the CommandTimeout property of the SqlCommand to an appropriate value.

But first check whether the query can be optimized.

Upvotes: 0

CloudyMarble
CloudyMarble

Reputation: 37566

The right way would be to optimize your procedure, but since you didn't post the code i cannot say anything about it, the other way would be to handle the symptom so no Exception get thrown by increasing the Timeout in your Connectionstring like:

"CommandTimeout = try any greater value;"

Upvotes: 0

Haris
Haris

Reputation: 915

Among the various ways to tune the performance of a query are:

  • Rewriting your query

There is no one way to write a query. If you find that a query with an OUTER JOIN is taking a long time, rewrite it using an INNER JOIN. Or perhaps the WHERE clause is written in such a way that it is causing excessive database read operations.

  • Normalizing or de-normalizing tables

While normalizing tables results in ideal database design, it often leads to poor querying. If you find that frequently-used queries have to span too many normalized tables, consider some duplication of data.

  • Adding indexes

Lack of well-defined indexes is a frequent cause of queries taking a long time to execute. Creating indexes will cause a query to lookup the index rather than the table.

  • Removing indexes. In some cases, it is a poorly defined index that is the cause of slow query execution. For example, an index which does not include the frequently looked up columns in a table. In such a case, it is better to drop the index and recreate it.

Start with activating Query Execution Time in your stored procedure to see what takes time

SET STATISTICS TIME ON Select * from ......

You could also view graphical execution plan of your query by: Click Query > Include Actual Execution Plan.

You can read more about Query performance tuning on: Pinal Dave's Blog, he is the best when it comes to this.

Upvotes: 3

Related Questions