| Groups | Blog | Home |
sql server reporting services : Subreport performance
I've worked with Crystal and Access and now SRS and I have a question
regarding subreports in SRS. Take a standard header/details report in which the details are a subreport (in this case we can't put all the records in one Stored Proc. Do the parameter link and it all works fine except when we watch it in profiler it acts like crystal, making individual SP calls to return the data for each detail section. This is where we find performance degrades. Our particular circumstance is that we will have a header with 3-4 detail sections that all share a common parameter with the header and filter to that parameter. Because of this configuration is makes it impossible to return a single dataset with all the relevant data. The header itself will return 500+ records and combined with the 3-4 detail sections could be over (500 x 4) 2000 sp calls. Sloooow. Seems to us that MS Access did the best job at this.
Bruce,
Thank you for your reply. I agree with your statements, however in this particular case we write budgeting solutions and this report (among others) prints the budget details that go into a large budget binder. In prior projects we have limited the parameters to not allow the user to return to many records back. For instance instead of allowing an "<all>" parameter value we would require that they select at least one department. It's a little maddening to them to have to print this report for each department. It just seems so inefficient. +++++++++++++ [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Not much you can do about this. That is how it works. > > From a design perspective you could ask yourself if the user is really > browsing through 500 records. Or, is there a way to provide them with a way > to get to the records they really want to look at. Another way is to start > off with another report that they browse through and then use jump to report > when they want more information. This pattern is very understandable to > users and is very fast. I use drill through all the time, much more than > drill down. Most reports I use that have subreports I try to have as the > detail report. > > It sure sounds to me like the report has a lot of detail that a human is not > going to look at. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "TJ" <TJ@discussions.microsoft.com> wrote in message > news:AF1D23FD-B5FB-4164-876A-EC510E2BBFC7@microsoft.com... > > I've worked with Crystal and Access and now SRS and I have a question > > regarding subreports in SRS. > > > > Take a standard header/details report in which the details are a subreport > > (in this case we can't put all the records in one Stored Proc. Do the > > parameter link and it all works fine except when we watch it in profiler > > it > > acts like crystal, making individual SP calls to return the data for each > > detail section. This is where we find performance degrades. > > > > Our particular circumstance is that we will have a header with 3-4 detail > > sections that all share a common parameter with the header and filter to > > that > > parameter. Because of this configuration is makes it impossible to return > > a > > single dataset with all the relevant data. The header itself will return > > 500+ records and combined with the 3-4 detail sections could be over (500 > > x > > 4) 2000 sp calls. Sloooow. > > > > Seems to us that MS Access did the best job at this. > > > > Anyone have some other suggestions? > >
Not much you can do about this. That is how it works.
From a design perspective you could ask yourself if the user is really browsing through 500 records. Or, is there a way to provide them with a way to get to the records they really want to look at. Another way is to start off with another report that they browse through and then use jump to report when they want more information. This pattern is very understandable to users and is very fast. I use drill through all the time, much more than drill down. Most reports I use that have subreports I try to have as the detail report. It sure sounds to me like the report has a lot of detail that a human is not going to look at. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "TJ" <TJ@discussions.microsoft.com> wrote in message news:AF1D23FD-B5FB-4164-876A-EC510E2BBFC7@microsoft.com... > I've worked with Crystal and Access and now SRS and I have a question > regarding subreports in SRS. > > Take a standard header/details report in which the details are a subreport > (in this case we can't put all the records in one Stored Proc. Do the > parameter link and it all works fine except when we watch it in profiler > it > acts like crystal, making individual SP calls to return the data for each > detail section. This is where we find performance degrades. > > Our particular circumstance is that we will have a header with 3-4 detail > sections that all share a common parameter with the header and filter to > that > parameter. Because of this configuration is makes it impossible to return > a > single dataset with all the relevant data. The header itself will return > 500+ records and combined with the 3-4 detail sections could be over (500 > x > 4) 2000 sp calls. Sloooow. > > Seems to us that MS Access did the best job at this. > > Anyone have some other suggestions?
Understood. If the performance is poor you could always schedule a snapshot
and then have some parameters that filter (versus a query parameter). -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "TJ" <TJ@discussions.microsoft.com> wrote in message news:6D9E7308-2C69-493D-A02A-422E65BCD659@microsoft.com... > Bruce, > > Thank you for your reply. I agree with your statements, however in this > particular case we write budgeting solutions and this report (among > others) > prints the budget details that go into a large budget binder. > > In prior projects we have limited the parameters to not allow the user to > return to many records back. For instance instead of allowing an "<all>" > parameter value we would require that they select at least one department. > It's a little maddening to them to have to print this report for each > department. > > It just seems so inefficient. > > +++++++++++++ > > "Bruce L-C [MVP]" wrote: > >> Not much you can do about this. That is how it works. >> >> From a design perspective you could ask yourself if the user is really >> browsing through 500 records. Or, is there a way to provide them with a >> way >> to get to the records they really want to look at. Another way is to >> start >> off with another report that they browse through and then use jump to >> report >> when they want more information. This pattern is very understandable to >> users and is very fast. I use drill through all the time, much more than >> drill down. Most reports I use that have subreports I try to have as the >> detail report. >> >> It sure sounds to me like the report has a lot of detail that a human is >> not >> going to look at. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "TJ" <TJ@discussions.microsoft.com> wrote in message >> news:AF1D23FD-B5FB-4164-876A-EC510E2BBFC7@microsoft.com... >> > I've worked with Crystal and Access and now SRS and I have a question >> > regarding subreports in SRS. >> > >> > Take a standard header/details report in which the details are a >> > subreport >> > (in this case we can't put all the records in one Stored Proc. Do the >> > parameter link and it all works fine except when we watch it in >> > profiler >> > it >> > acts like crystal, making individual SP calls to return the data for >> > each >> > detail section. This is where we find performance degrades. >> > >> > Our particular circumstance is that we will have a header with 3-4 >> > detail >> > sections that all share a common parameter with the header and filter >> > to >> > that >> > parameter. Because of this configuration is makes it impossible to >> > return >> > a >> > single dataset with all the relevant data. The header itself will >> > return >> > 500+ records and combined with the 3-4 detail sections could be over >> > (500 >> > x >> > 4) 2000 sp calls. Sloooow. >> > >> > Seems to us that MS Access did the best job at this. >> > >> > Anyone have some other suggestions? >> >> >>
Good idea.
Wouldn't it be great if MSFT could put a little more intelligent design in these types of situations? I.e. Parent Query results would drive one resultset for the children and then split those results off to the right parent child combos? Hope that makes some sense. Thanks for the ideas. [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Understood. If the performance is poor you could always schedule a snapshot > and then have some parameters that filter (versus a query parameter). > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "TJ" <TJ@discussions.microsoft.com> wrote in message > news:6D9E7308-2C69-493D-A02A-422E65BCD659@microsoft.com... > > Bruce, > > > > Thank you for your reply. I agree with your statements, however in this > > particular case we write budgeting solutions and this report (among > > others) > > prints the budget details that go into a large budget binder. > > > > In prior projects we have limited the parameters to not allow the user to > > return to many records back. For instance instead of allowing an "<all>" > > parameter value we would require that they select at least one department. > > It's a little maddening to them to have to print this report for each > > department. > > > > It just seems so inefficient. > > > > +++++++++++++ > > > > "Bruce L-C [MVP]" wrote: > > > >> Not much you can do about this. That is how it works. > >> > >> From a design perspective you could ask yourself if the user is really > >> browsing through 500 records. Or, is there a way to provide them with a > >> way > >> to get to the records they really want to look at. Another way is to > >> start > >> off with another report that they browse through and then use jump to > >> report > >> when they want more information. This pattern is very understandable to > >> users and is very fast. I use drill through all the time, much more than > >> drill down. Most reports I use that have subreports I try to have as the > >> detail report. > >> > >> It sure sounds to me like the report has a lot of detail that a human is > >> not > >> going to look at. > >> > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "TJ" <TJ@discussions.microsoft.com> wrote in message > >> news:AF1D23FD-B5FB-4164-876A-EC510E2BBFC7@microsoft.com... > >> > I've worked with Crystal and Access and now SRS and I have a question > >> > regarding subreports in SRS. > >> > > >> > Take a standard header/details report in which the details are a > >> > subreport > >> > (in this case we can't put all the records in one Stored Proc. Do the > >> > parameter link and it all works fine except when we watch it in > >> > profiler > >> > it > >> > acts like crystal, making individual SP calls to return the data for > >> > each > >> > detail section. This is where we find performance degrades. > >> > > >> > Our particular circumstance is that we will have a header with 3-4 > >> > detail > >> > sections that all share a common parameter with the header and filter > >> > to > >> > that > >> > parameter. Because of this configuration is makes it impossible to > >> > return > >> > a > >> > single dataset with all the relevant data. The header itself will > >> > return > >> > 500+ records and combined with the 3-4 detail sections could be over > >> > (500 > >> > x > >> > 4) 2000 sp calls. Sloooow. > >> > > >> > Seems to us that MS Access did the best job at this. > >> > > >> > Anyone have some other suggestions? > >> > >> > >> > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |