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=val5Select ...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=val5EXEC dbo.mysp @para1, ...@para53) 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 |
|
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 asWhere...and STU_ID = @param5Order by... |
|
|
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. |
|
|
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 asBeginSet nocount on;Select...EndGoThere 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. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
|
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. |
|
|
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. |
|
|
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. |
|
|
|