SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 stored proc performance issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

768 Posts

Posted - 03/04/2013 :  11:50:53  Show Profile  Reply with Quote
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.

Edited by - Hommer on 03/04/2013 11:56:59

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 03/04/2013 :  11:58:38  Show Profile  Reply with Quote
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

768 Posts

Posted - 03/04/2013 :  12:35:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 03/04/2013 :  13:44:12  Show Profile  Reply with Quote
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

768 Posts

Posted - 03/04/2013 :  14:03:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 03/04/2013 :  14:39:23  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
278 Posts

Posted - 03/04/2013 :  15:27:17  Show Profile  Reply with Quote
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

768 Posts

Posted - 03/04/2013 :  15:34:08  Show Profile  Reply with Quote
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

768 Posts

Posted - 03/04/2013 :  15:48:34  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000