Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 stored proc performance issue

Author  Topic 

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-03-04 : 11:50:53
Hi, there,

I created this select stored proc but had a hard time figure out why it did not perform the way it should. (compared to original sql string way).

Here are several observations under SSMS:

1) without the sp, every of my 16 sets of input came back less than 1 second as.

declare @para1 mydatatype = val1, @para2...@para5=val5
Select ...where xyz=@para1...

2) running as next, 4/16 came back 1 to 2 second, but the rest took 22 to 40 seconds, which is the problem.

declare @para1 mydatatype = val1, @para2...@para5=val5
EXEC dbo.mysp @para1, ...@para5

3) The only different among these test inputs is the last parameter, which is a nvarchar(20) student ID string.

4) Display estimated exec plan suggests: Missing Index(Impact 48.4392): Create Nonclustered index on tbl_fee_payment INCLUDE [AMOUNT].
Several top expensive costs are: Clustered Index Scan 35%, and Nested Loops(Left outer join) 13%.

The thing confused me is why the naked sql(both as my test here and the production code in ado) perform better than my sp.

As one always tried to promote stored proc over native sql, I greatly appreciate any help.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-04 : 11:58:38
Google for parameter sniffing - that may be what is causing the problem. Here is one article: http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

As suggested in that article, one possibility is to identify the offending statement in the stored procedure and use RECOMPILE option.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-03-04 : 12:35:39
Thank you, James!

I added With Recompile into my Create mysp, but it did not change the outcome.

By the way, all my testing input para sets returned 1 to 3 rows, and @param5 is used as

Where...
and STU_ID = @param5
Order by...
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-04 : 13:44:12
Are you doing statement level recompilation, or at the stored procedure level? If you are doing it at the stored proc level, instead do it at the statement level.

If that does not help, compare the query plans for the ad-hoc query against the query plan used by the stored procedure.

Also, make sure that statistics are updated if they are not.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-03-04 : 14:03:21
I am sorry I don't understand what is statement level Compilation. I did not find OPTION Clause of the SELECT has Compile.

I added into
Create mysp ...
@para5
with Compile
as
Begin
Set nocount on;
Select...
End
Go

There is only one Main SELECT in the sp. In that case, isn't sp level and statement level the same?

Also how do you update statistics in this case? Manualy run sp_updatestats once, or add something into sp itself?

I have manually compared the plans, and did not find big difference.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-04 : 14:39:23
If there is only one statement, I can't see the recompile at statement level vs recompile at stored proc level making any difference. Nonetheless, the statement level recompilation is described here: http://www.sqlmag.com/article/sql-server/-using-the-recompile-query-hint-to-solve-parameter-sniffing-problems-94369 or on MSDN.

Update Statistics can be run on a single table for all its indexes: UPDATE STATISTICS dbo.YourTableName
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-03-04 : 15:27:17
If the stored proc is some type of "generic" query, where you might pass 1 to 10 (or however many) different parameters, and you've hard-coded that query with all the variables, SQL has a hard time constructing a good, efficient query plan for that type of catch-all query.

Using dynamic sql instead, including only the parameter(s) that were actually passed in, usually performs much better.

Of course dynamic SQL has some potential permissions issues, and SQL injection issues if not coded carefully, but overall it will give you much better query plans.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-03-04 : 15:34:08
Ok, I have updated the table statistics.

I also changed all the joins from PL/SQL style implicit "from x, y" to tsql "x join y on..."

Because I saw 2 warnings in the plan about no join predicate.

But it still performs poorly.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-03-04 : 15:48:34
The thing I hate to give up and let the programmer run embeded sql is that even within my small sample size, I had these 4 student IDs that worked beautifully while the other 8 failed the test.

It doesn't make sense. The secret must be in the underlying data and the query I converted into stored proceure.
Go to Top of Page
   

- Advertisement -