Quick in code, very slow in SP

Naomi 7,361 Reputation points
2021-03-19T14:23:40.433+00:00

Hi everybody,

We have a very slow stored procedure. I worked on optimizing its code. I ran multiple tests of the code of the procedure against production (not as a procedure, but using code inside) and I got my results in 2 minutes as the worst result.

Today we're running this as a procedure and it has been running the long and complex query forever (I added lots of logging, so I know which particular query is slow). It originally used 2 scalar UDFs - one for the constant and one for the parsing of the varchar column to get its 7th part and use as part of the where clause. This was slow in my tests (~30+ sec.) but not as bad as seeing it now. I removed the scalar UDF and replaced with string_split and OUTER APPLY. There are also lots of tables joined in the process but they are supposed to be joined with one temp table which only has 100 rows.

Anyway, why would I see such drastic difference in results and what would be the best way to optimize this query? It looks like some stale plan for that one is still in effect even though the query is re-written.

I'll double check if all indexes are in place for the JOIN conditions.

Any ideas of how to speed up this monster?

Thanks in advance.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 24,946 Reputation points
    2021-03-19T15:31:15.463+00:00

    Hi @Naomi ,

    It could be parameters sniffing issue.

    Please try to execute stored procedure by using WITH RECOMPILE: 

    EXEC StoredProcedureName @parameters WITH RECOMPILE;
    1 person found this answer helpful.
    0 comments
  2. Erland Sommarskog 101K Reputation points MVP
    2021-03-19T22:57:04.483+00:00

    Without seeing your code and not knowing all variations you did, we can't say anything about the case as such.

    However, I have an article on my web site, Slow in the Application, Fast in SSMS? and it sounds like this article could help you sort out at least some of your confusion.

    0 comments
  3. EchoLiu-MSFT 14,571 Reputation points
    2021-03-22T03:47:14.417+00:00

    Hi @Naomi ,

    Could you please share us your query code and table structure (CREATE TABLE …) along with some sample data(INSERT INTO …)?

    Echo

    0 comments
  4. Naomi 7,361 Reputation points
    2021-03-24T15:43:55.737+00:00

    Hi,

    I was trying to reply to Erland but got a message about exceeding 1K, which is weird, so I put it as a separate reply to myself.

    Hi Erland,

    I may need to find some time to re-read this long article again. For now my colleague identified one missing index, when we added it, the performance improved significantly. The longest run of this complex procedure is ~4 min. although we haven't hit a huge number of rows yet.

    However, here is what I noticed - it seems to me that the first run of the day may be the slowest regardless on the number of rows to process (say, today it's 4 min. for only 50 rows). The rest of the day it usually takes under a minute for a bigger number of rows. I'm wondering if there is a way to speed up this initial run. Although 4 min. for the very first run is still OK and much better than several hours we used to get for lots of rows.

    I also made some extra changes and going to show them to my colleague (they are minor and I don't expect them to make any impact, but I did split that original query into 2 using the temp table - I don't believe it'll make matters worse - but not sure if it's necessary too based on our current performance).

    Thanks.