0

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...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1637901
  • 1
  • 1

3 Answers3

3

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.

Haris
  • 915
  • 1
  • 11
  • 28
0

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;"
CloudyMarble
  • 36,908
  • 70
  • 97
  • 130
  • 1
    Just to be clear, this option should only be a last resort. If performance of a stored procedure is so bad that you need to do this it will only get worse with time, that is unless no one is actually adding/updating data in the database. – ChaosPandion Aug 31 '12 at 07:48
  • @ChaosPandion You just repeated what i mentioned in my answer, i agree anyway. – CloudyMarble Aug 31 '12 at 07:54
  • I read between the lines to understand what you meant but your actual answer lacks clarity. – ChaosPandion Aug 31 '12 at 08:00
  • @ChaosPandion What was so unclear about "the right way how to do it" and "to handle the symptom"? anyway if it makes you feel better you are welcome to edit the answer im ok with that. – CloudyMarble Aug 31 '12 at 08:25
  • 1
    Well the primary problem is that you are specifying connect timeout. That won't actually help avoid the timeout exception. http://stackoverflow.com/questions/847264/what-is-the-difference-between-sqlcommand-commandtimeout-and-sqlconnection-conne – ChaosPandion Aug 31 '12 at 08:33
  • @ChaosPandion I can c u smiling :), well this is something worth to be commented. +1 – CloudyMarble Aug 31 '12 at 08:40
  • Here is the query executing in stored procedure DECLARE totAmount as float DECLARE Interest as float WHILE Date1 <= Date2 BEGIN DECLARE Amount as float SELECT Amount = isnull(SUM(amt),0) from Table1 WHERE P1 = P1 AND P2 = P2 And Dt <= Date1 SET totAmount = totAmount + Amount SET Date1 = Date1 + 1 END SET Interest = (totAmount * 4)/36500 – user1637901 Aug 31 '12 at 14:21
0

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.

Hans Kesting
  • 38,117
  • 9
  • 79
  • 111