question

NaomiNNN avatar image
0 Votes"
NaomiNNN asked EchoLiu-msft commented

Quick in code, very slow in SP

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.

sql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have any update?

Echo

0 Votes 0 ·

I'm trying to add update in a reply to Erland

0 Votes 0 ·

Ok, we will always pay attention to the problem process.

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered

Hi @NaomiNNN,

It could be parameters sniffing issue.

Please try to execute stored procedure by using WITH RECOMPILE:

 EXEC StoredProcedureName @parameters WITH RECOMPILE;


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @NaomiNNN,

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

Echo

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered ErlandSommarskog commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

When you see the 'first' run taking longer than later runs - that generally is an indication that the data has to be loaded into the buffer cache from disk. Once the data has been loaded - subsequent executions don't have to wait for the data to be loaded. You can verify this by viewing the actual execution plan for the first execution and any of the later executions - and look specifically for physical reads.

Either way - 4 minutes for 50 rows sounds like there are many opportunities for improvement. Since I have to guess (without having the execution plans or the query available) - it would appear that this query is reading way too much data for the final results.

0 Votes 0 ·

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


What Jeffery said. That or that plans have fallen out of cache or been marked for recompile due to index rebuild, so the first execution includes a lot more compiles. Or a combination of both.

0 Votes 0 ·