Query is slow from stored procedure but faster outside the procedure

Question

Query is slow from stored procedure but faster outside the procedure

praveen_vejandla

SSCrazy

Points: 2069
More actions
August 20, 2013 at 5:49 am

#277171

I have a query that joins 3-4 tables out of which there are 2 temp tables. It is a simple join query and there are no parameters/variable passeds to the query.
Query runs faster when run outside the stored procedure. If I put the same query as a stored procedure without any parameters, it is running slower.
Query plan in both cases is same and amount of logical reads also same and set options also same.
Any idea what could be the root cause behind this?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

praveen_vejandla (8/20/2013)
I have a query that joins 3-4 tables out of which there are 2 temp tables. It is a simple join query and there are no parameters/variable passeds to the query.
Query runs faster when run outside the stored procedure. If I put the same query as a stored procedure without any parameters, it is running slower.
Query plan in both cases is same and amount of logical reads also same and set options also same.
Any idea what could be the root cause behind this?
Thanks.

From this vague description it is impossible to tell. Can you post the query, the actual execution plan, the table ddl including indexes?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

I don't believe you when you say that the query plans are identical.

This article on my web site covers this situation and should get you going: http://www.sommarskog.se/query-plan-mysteries.html.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

praveen_vejandla SSCrazy Points: 2069 More actions · Answer 3

Please find the query plans attached. Before posting to the forum, I have already gone through your article and spent lot of time but could not find the root cause.

Thanks a lot for the reply.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

The plan files are indeed very similar. However, I disagree when you say that the query is something simple. I don't see the full query, but I see that there are umpteen calls to a scalar function which gives me an uncanny feeling.

Could you post the full text of the stored procedure, as well as the function?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

praveen_vejandla SSCrazy Points: 2069 More actions · Answer 5

I have sent the stored procedure and SQL query to you in an e-mail.

Thanks.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

There is something which is amiss here. The code you sent me had no parameters or variables. However, the plans you posted has:

A quick glance indicates that the plans are for a similar query to the queries you mailed me, so I guess you played around.

Nevertheless, it is difficult to help with conflicting information.

When you ran the code with parameters/variables, did you get a noticeable difference in performance? Or do you only get that when you run without parameters? What difference are we talking about?

Can you post the code to the user-defined function? (It's much better to post the code here than mailing it to individuals, so that others can join in.)

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

praveen_vejandla SSCrazy Points: 2069 More actions · Answer 7

It seems I picked up a wrong plan for the e-mail attachment. I tried re-creating the issue but surprisingly, it is not reproducible now.

I will get back to you if issue repeats again.

Thanks.