November 5, 2007 at 1:32 pm
Hi,
I have just deployed my SSRS reports to the report manager.I have about 5 different filters on my report, applied grouping as well as sorting.My report would display more than 1000 records as it is querying results from a huge database. Is there a way for me split my report output to a number of pages ? My report is also very slow. Do provide suggestions for me to improve the performance.
Thank You in Advance !
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
November 5, 2007 at 2:01 pm
Without knowing more about your process and the query behind the report I really cannot say why it is slow. Looks at your query design, how toy apply your filters and things like if you are retriving a lot more data than you need. Also, try searching for "Optimizing Reporting Services" here, on google or some other search engine and look at some of the items there to see if anything readily helps you. Otherwise you really have to explain more for us to understand what might help.
November 6, 2007 at 5:37 am
You don't need to cross-post on these forums, we will find it.
RS performance is in a couple of pieces, the actual query being run and the the rendering of the report in HTML (and then the rendering to PDF if you go that way).
First, spend the time to make sure your query is performing well. If you run it in query analyzer and it is taking more than a second to return 1000 rows, you may have a query or database design issue. Post the query back here and someone will help.
If the query performs well and the report is still really slow, it is RS rendering the report. This sometimes happens with really complicated formulas and formatting in the report, but it is pretty unusual. If this happens, see if there is a way to simplify the report rendering. These suggestions may help:
- sort records in the query rather than in the report groupings
- avoid heavy use of sub reports
- try to do calculations in T-SQL rather than in the report
November 7, 2007 at 6:45 am
I did run the query in Query Analyzer and the response time for 10000 records was labout 20 seconds.I have removed all grouping and sorting that was applied on the report and still no difference.I have also checked on the caching and snapshots options on the Report Manager.There is no calculation that is involved within my query either.Is there any other setting or suggestions that I can try?My database details are as below,
No of records = 1 000 000
No of filters applied on report is 5.
The maximum number of records that would be returned by query is about 10000, depending on the filter selection.
All the filters have multivalue selection being passed from a dataset [Query-reqular Select statment, no calculation involved].
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
November 7, 2007 at 8:10 am
Look at the execution plan in Query Analyzer and see if you can spot maybe a chance for a better index which may have impact in spades in some cases.
Also, I converted over most of my reports to using Stored Procedures. In some of these cases the called Procedure is setup to determine between a set of Procedures which best fits the conditions being applied. And calls that, of course all output the same data but each is designed to the strength of the particular set of applied conditions. The idea is to have available better execution plans and hopefully reduce the amount of time it takes to get a plan. Could be that the query plan may work best under one set of applied filters but a different set may be supplied the same plan from the cache and it have a negative impact.
November 7, 2007 at 11:38 am
Since your query takes about 20 seconds, how long does your RS report take? What response time are you expecting? A million rows should be no big deal (and faster than this). Some thoughts:
- post your query. Much easier to comment then.
- can you push the filters into the query. If you do (and reduce your rowset), you should gain some speed.
- how many pages are created on a typical pull? (i.e. rows per page) Can you filter it down further, relying on the parameters for the user to jump to specific information?
November 8, 2007 at 7:40 am
Antares686 (11/7/2007)
Also, I converted over most of my reports to using Stored Procedures. In some of these cases the called Procedure is setup to determine between a set of Procedures which best fits the conditions being applied. And calls that, of course all output the same data but each is designed to the strength of the particular set of applied conditions.
That's a good approach. Can you please describe an example of the report's dataset that determins which proc to use. For instance, in the dateset window, do you type the name of a "master" proc that determines and calls another procedure or do you supply a script like...
If @param1 = x
Begin
usp_procA @param1
End
If @param1 = y
Begin
usp_procB @param1
End
An example would be helpful because I get the concept, but need some details to get me there. THanks!
smv929
November 8, 2007 at 8:59 am
OK here goes, for my example let''s assume I know a query performs better if @Param2 is not included when not set and @Param3 has no effect on performance. Additioanlly my design requires a value for @Param1 (to keep simple here).
CREATE PROC dbo.Main_Proc
@Param1 datatype,
@Param2 datatype,
@Param3 datatype
AS
SET NOCOUNT ON
IF @Param2 IS NULL -- or whatever condition for 2 not defined is
EXEC dbo.Only_Param1and3_Proc @Param1, @Param3
ELSE
EXEC dbo.All_Param_Proc @Param1, @Param2, @Param3
GO
CREATE PROC dbo.Only_Param1and3_Proc
@Param1 datatype,
@Param3 datatype
AS
SET NOCOUNT ON
SELECT * FROM dbo.Table1 WHERE Col1 = @Param1 AND Col3 = IsNull(@Param3,Col3)
GO
CREATE PROC dbo.All_Param_Proc
@Param1 datatype,
@Param2 datatype,
@Param3 datatype
AS
SET NOCOUNT ON
SELECT * FROM dbo.Table1 WHERE Col1 = @Param1 AND Col2 = @Param2 AND Col3 = IsNull(@Param3,Col3)
GO
Thus in my report I call dbo.Main_Proc and let it make the decision a to which applies, of course the recordset output from both need to be the same. But you might derive from this other reports that only use Only_Param1and3_Proc or All_Param_Proc, which if you do make sure you track the relationships in case changes are requested.
November 8, 2007 at 9:12 am
Thanks! That is very helpful. That is the way I will do it.
I've considered an alternative whereby you define the dataset in RS using a dynamic string that returns the statement that would evaluate to the name of the proc along with passed parameter values. I know this will likely work, but your approach seems more solid.
smv929
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply