September 9, 2014 at 9:23 am
I am still learning how SQL server processes queries. Is there a difference in performance between one Stored Procedure vs splitting it into two Stored Procedures to be ran in a single SSRS report.
Or are they the exact same thing?
Thanks for your time any education on this would be helpful
***SQL born on date Spring 2013:-)
September 9, 2014 at 10:00 am
Hi,
It depends on the result set size of your Stored procedure. Like if the Stored procedure returns millions of rows or it returns only 1000 rows. There is no need to split the Stored procedure if its fine tuned and indexes are properly build. Check the execution plan of your query.
Post your query and execution plan so that we can suggest more.
September 9, 2014 at 10:29 am
I would also say that is depends on how you are using the data in your report. Remember 2 stored procedures in 2 data sets mean 2 calls to the SQL Server instead of one that returns all the data you need. For SSRS I usually try to return all the data I'm going to need for the report in one procedure and filter in the report, but I have to admit I haven't tested to see if overall performance is better with 2 data sets.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 9, 2014 at 12:41 pm
Jack that's exactly what I'm trying to figure out if two smaller call backs vs. one large one will change performance much. Also when those calls are made does the SQL server process the two separate Stored Procedures concurrently vs. consecutively? After talking with my boss I may just split this into two separate stored procedures and two separate rdl's. This stored Procedure is massive, well over a thousand lines 5 temp tables and 65 updates. The average user for this report is probably looking and pulling back between 10,000 and 50,000 rows on average with 30 columns.
***SQL born on date Spring 2013:-)
September 11, 2014 at 8:12 am
Hey Thomas,
In my experience, there haven't been performance gains found by splitting data pulls as-is. That being said if you can break up the processing of what this "Behemoth sproc" is pulling that is where the real performance gains come. Yes you can get this data to concurrently pull. That is the point where you can see performance gains mostly.
If you are executing against the same type of data, once a query is creating its execution plan that all gets cached and should be visible in speeding up subsequent calls to that data -- of course you can just create covering indexes as well to really solve that issue...
My thought on any "big" ssrs report is make sure you have the data mostly aggregated to what you need, and generally that approach makes reports much more optimal.
Good luck !
Jeremy
September 11, 2014 at 8:19 am
Thomas,
I would be remiss if I didn't tell you - SQL SERVER PROFILE.
If you are looking to really dig into understanding what SQL is doing... I would strongly suggest this tool, as it will let you see exactly transaction times for these queries and you can test anything you want.
If you have any questions on that front feel free to ask?
Thanks,
Jeremy
September 11, 2014 at 8:28 am
Thanks!
***SQL born on date Spring 2013:-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply