December 18, 2007 at 3:12 pm
I'm fairly new to SSRS but no SQL Server - I have a dashboard type report. This report contains 7 Datasets all extracting data from the same table but very different calculations on the data so there's not really a way to integrate. Each of the queries and/or stored procedures have subqueries. There is actually two problems:
1. I watch the activity monitor in SQL Server and it goes nuts with multiple instances of the new Process ID and lots or PAGEIOLATCH_SH what type. Can I somehow set the Datasets to execute sequentially rather than on transaction, I guess that's the right term?
Each of the datasources execute very quickly individually.
2. If I run some reports with a single parameter and set that parameter with the built in drop down it runs very slow, even if there is just one dataset. Now if I run the same report with hard coding the parameter in the query it renders almost instantaneously. Why might using a parameter slow down a basic report?
Any ideas is greatly appreciated.
Thanks,
Robert
December 18, 2007 at 3:43 pm
Why might using a parameter slow down a basic report?
Dunno about most of your problems (although sub-queries could be a huge problem if they are actually correlated subqueries), but some say that a thing called "parameter sniffing" could cause the symptoms I've quoted above... best if you Google it...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2007 at 5:51 pm
Jeff, thanks for the idea - I'll have to research that.
Keep those ideas coming folks - I know there's a bunch more crazy smart people out there. Could be a free lunch for the effort - you may have to come to Tulsa though. hehe
Robert
December 18, 2007 at 6:45 pm
When a dataset is created by a standard text query and a parameter is included for SSRS wouldn't the parameter get replaced before being executed at the server, meaning sql server wouldn't even be aware of the parameter?
December 18, 2007 at 8:25 pm
I'm not sure if your performing the calculations in your code in in the report itself. If you're doing the calcs in the report, move them into the procedures instead. SSRS isn't nearly as efficient as the SQL engine.
Also, I'd be willing to bet that you could do all of the necessary actions in one proc.
Just a thought.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 18, 2007 at 9:25 pm
I'm doing all the calculations in the proc, if nothing else, it's much easier dealing with division by 0 problems. I'm much more comfortable working in T-Sql.
Some of the queries can be combined since they return data based on the last 4 saturdays of data in rows but another returns a single row with avg per day per another qty. I could probably cut it down to two procs. I may have to resort to this - I'm in the process of building a cube in SSAS and use that in the report that may be another answer but I was hoping to avoid waiting on a cube to process every morning.
Too bad a proc can't return more than one dataset.
Thanks,
Robert
December 18, 2007 at 10:59 pm
robert@robertspencer.com (12/18/2007)
Too bad a proc can't return more than one dataset.
Thanks,
Robert
Actually a proc CAN return more than one dataset. :w00t: We're doing it in our .NET application. Now I'm not sure if the RS interface can handle it or not. but it can be done.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 20, 2007 at 11:23 am
SQLServer can certainly produce multiple result sets out of SP's, and most SQL client drivers can handle some form of identifying these independently, but neither SSRS2K and SSRS2K5 can currrently handle this. Dunno about SSRS2K8 when it comes.
As JS mentioned trying to get everything done in a single SP is the best way to go and the way SSRS likes it. I have often resorted to repeating seemingly unneeded data columns on a result set, coming from what would have been the result set of another query but this can get kludgy if the number of extra columns for the secondary query are more than around 10 or so. Oftentimes with the extra tacked-on data columns you have to get creative with adding groups to the report to keep things in line.
maddog
December 20, 2007 at 11:40 am
Another idea, if you have all the DataSets defined in the same single report and are tying them to different tables or data regions in the dashboard, you might try to break each section of the report that has a different data set into a subreport and call it from the main report. The main report then would only contain the first section tied to the first DataSet, and in this manner you can have one DataSet per actual .rdl file. I had a similar situation a while back where I started with a huge single report with eight different and unrelated data regions, each tied to a different DataSource, but I broke it up into a main report and 7 subreports and it worked faster and stopped having the timeouts it was getting occasionally.
There are situations where if your 'master' report is using subreports and the subreports are defined in the detail or group rows within a table based on a primary DataSet, since the subreport essentially gets fired for each row in the result set the database workload can get pretty severe as the subreport datasets are not re-used. If you are calling self-contained subreports from a master report where the subreport (and the DataSource) essentially gets processed only once you are OK, as it sounds like you are doing with your dashboard.
maddog
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply