Optimizing stored procedure that exceeds timeout limit

BalajiG2000 0

I am encountering a challenge with a Windows service responsible for generating reports using the stored procedure "Mgmt_Report". The stored procedure is subjected to a timeout period of 5 minutes. While smaller reports, typically taking 1 to 3 minutes to execute, are handled efficiently, larger reports, which can take roughly 30 minutes, result in timeout exceptions.

The stored procedure exclusively consists of select statements; no update or insert statements are involved.

In an attempt to optimize performance, I've modified the stored procedure to utilize temporary tables for retrieving specific data instead of direct table joins. This adjustment has shown promise, reducing the execution time to approximately 12 to 15 minutes. However, I am seeking further optimization strategies.

I am considering implementing indexes to enhance query performance. However, I am uncertain about the appropriate indexing approach and when to apply it effectively on temp tables?.

Exploring the possibility of introducing parallelism in the query execution to expedite processing time. However, I am unsure of the potential benefits and implications of this approach

it is imperative to maintain the timeout period of 5 minutes to prevent blocking other reports from generating.

I require guidance on additional optimization techniques beyond utilizing temporary tables, particularly regarding effective indexing strategies and the potential benefits and considerations of introducing parallelism into the query execution. Additionally, insights into optimizing performance while adhering to the specified timeout constraints would be greatly appreciated.

Greg Low 1,685 Reputation points Microsoft Regional Director

2024-02-21T08:07:41.43+00:00

You really need to get your head around indexing. We have an indexing course for DBAs and Devs (at training.sqldownunder.com) but there are many others around.
It's quite common to have procedures that don't respond well when working with varied parameters. (See articles on parameter sniffing) My guess though, is that just fixing the indexing will do what's needed. It's the #1 thing that I see wrong in these situations.
As a general rule, adding temp tables, etc. is an anti-pattern, not a positive idea. Like anything though, there are exceptions to this. One good example is if the query uses lots of nested CTEs. You can quickly get to a point where the optimizer doesn't have good cardinality estimates to work with, and materializing the data into a temp table can help.
But most of the time, making yet another copy of the data, just to query it, is counter-productive.
Finally, you mention the blocking. Do you have Read Committed Snapshot Isolation (RCSI) enabled at the DB level? It's very likely that you don't and that you probably should. (Even though it's hard to generalize). For locking issues, there is another course at the link above, that covers locking, blocking, and deadlocks.
Erland Sommarskog 107.1K Reputation points

2024-02-22T22:40:21.9733333+00:00

I will have to disagree with Greg a bit on the use of temp tables. As you have found, it can certainly help to break out parts of a large query to temp tables. There is an overhead for materialising the result, but on the flip side, because the temp tables have statistics, the optimizer gets more information for the next step.

Obviously, you need to break of up the query in the right place, but it is often quite easy to do, because you know more about the query and the data than the optimizer does.

Beyond that - with the information you have shared, there is not much we can say beyond general advice. Better indexing can certainly help. There can also be anti-patterns in the query that prevent indexes from being used.

One thing that can be help is Lee Tudor's sp_sqltrace which you can find on my web site. If you feed it a call to your stored procedure, it will set up a trace for your spid. When the procedure has completed, it will return how long time each statement took. If you have a loop, the occurrences of the same statement will be aggregated to a single row. You can also use it to capture execution plans - which you will need to do, because it is in the execution plan you can get understand of why a query is slow.

Beware that you will need to adjust the parameter @snoop_time, since your procedure runs for such a long time. By default, the trace that sp_sqltrace sets up, stops after five minutes.
Greg Low 1,685 Reputation points Microsoft Regional Director

2024-02-22T23:21:25.5666667+00:00

There are clearly scenarios where it helps. I just would never take it as a general rule to say it always helps. I see way too many times where temp tables are introduced and are counter-productive.
Erland Sommarskog 107.1K Reputation points

2024-02-23T08:21:16.7933333+00:00

Yeah, it definitely takes some good gut feeling to know when it is thr right thing to do.

Share via

Optimizing stored procedure that exceeds timeout limit