Performance Resources or Articles

Hi,
I am trying to find more about the common reasons for bottlenecks using
reporting services. Also I would like to learn how to performance test
reporting services applications.
Any resources or summary would help.
Thanks,
Sunny.Reporting services generates its own database to monitor everything
(think of it as a metadata database, like the master database).
After install you you'll be able to address the reporting services
database and query logtables about logins, usage of users and
performance.

Performance Reporting Services 2000

We are currently looking into to the performance of a system that utilizes
Reporting Services 2000. Can anyone point me to how to reduce response time
in generating reports? We generate 5 tabs of reports one for each business
unit and corp can see all of them. Is there any way to cache in 2000 based
on roles? The data is updated in the middle of the month and reviewed the
last week of the month. Is there a way to prime or execute these reports at
the beginning of the day with a scheduled type of job. Any help with
performance would be appreciated.instead of all of this bullshit caching talk; you should just be using
Analysis Services
much, much, much simpler architecture
-Aaron
Robert Bernheim wrote:
> We are currently looking into to the performance of a system that utilizes
> Reporting Services 2000. Can anyone point me to how to reduce response time
> in generating reports? We generate 5 tabs of reports one for each business
> unit and corp can see all of them. Is there any way to cache in 2000 based
> on roles? The data is updated in the middle of the month and reviewed the
> last week of the month. Is there a way to prime or execute these reports at
> the beginning of the day with a scheduled type of job. Any help with
> performance would be appreciated.|||In report manageer, under execution, set your report to generate from a
snapshot. and then set the snapshot schedule as needed, possibly monthly on
day 16. After updating make sure to click on Apply button at bottom of
execution screen. Also if you want an initial snapshot, check option <Create
a snapshot of the report when the apply button is selected>, apply changes
and you are done.
History snapshots can also be created manually by clicking on a report's
history tab at top of page and choosing <New Snapshot>.
Hope this helps.
"Robert Bernheim" wrote:
> We are currently looking into to the performance of a system that utilizes
> Reporting Services 2000. Can anyone point me to how to reduce response time
> in generating reports? We generate 5 tabs of reports one for each business
> unit and corp can see all of them. Is there any way to cache in 2000 based
> on roles? The data is updated in the middle of the month and reviewed the
> last week of the month. Is there a way to prime or execute these reports at
> the beginning of the day with a scheduled type of job. Any help with
> performance would be appreciated.
>

Performance regarding transposing fact data

Hello

I have an Analysis Services performance question:

Scenario:

My Fact table has a column that indicates some value-ID and only one value column. Out of this fact table I'd like to create a cube with two measures, one for each value-ID. So I could either write a query like

Code Snippet

SELECT key1, key2,

sum(case valueID when 'A' then value else null end) as measureA,

sum(case valueID when 'B' then value else null end) as measureB

FROM myFacts

GROUP by key1, key2

Then I'd use this table or view as fact table.

Or I could include the case syntax directly in the measure definitions inside Analysis Services and use the original table.

Does anybody know what's better concerning performance

-regarding cube processing?

-regarding querying the cube?

Are there other things that make one solution the better one?

Hi,

If you use your select statement to load the data into the cube.

-Processing will be slower as it has to execute the case statement

-Querying will be faster

If you read in the values with out the case statement

-Processing will be quicker as it doesn't have to execute the case statement

-Querying will be slower, you will have to create to MDX calculated measures that do the same as you are showing.

If you use your group by select statement, the granularity of your fact is also going to change - is that really what you want?

I would just create two MDX calculated measures, does depend on the size of data I suppose.

CREATE MEMBER CURRENTCUBE.[measures].[measureA] as

([Measures].[Value],[Dimension].[Dimension Key].&)

,non_empty_behavior = [Measures].[Value],VISIBLE = 1;

Might look something like above - ish

Hope that helps,

Matt

|||

Hi Matt

Thank you for your reply.

(First I apologize for not having added that I'm using Analysis Services 2000 not 2005.)

Generally I don't want to create a calculated member for each measure becaus calculated members are only calculated during run time but I want to have the measures correct after the cube was processed.

In Analysis Services 2000 I don't have the ability to use a select statement as a source for a fact table. Instead a table or view must exist in the underlying datasource. So there I have the possibility to either create a table or view with the select statement above or I enter the case expression inside the measure definition. So somewhere the case will be executed because having a dimension like "valueID" is not what I want or, wait, ...maybe it could be also a possible solution to have just one measure in the cube and have a valueID dimension... But this leads to more difficult queries and some inconveniances for cube users.

Regards

Chris

|||

Hi,

My 2000 skills are a little rusty and weren't perhaps that great in the first place

I would probably create a view, it would allow you to compare A against B or even create a total of A and B if you need to.

Sorry I can't be much help, perhaps someone else will help you.

Cheers

Matt

Performance on Fact table connected in View

Hi,

I have a Fact table and I'm accessing it through SQL Views where I have "group by". This is the one I feed to the Analysis Services. Would it be faster to access data if I have group by's in Views and feed that to Analysis Services or would it be better to just do the select statement in View and let the Analysis do all the aggregations?

cherriesh

If SQL and SSAS are on the same box then removing the group by should make things go faster as SQL will not need to sort the results and spool them temporarily out to tempdb until all the results have been read and there will be little latency to deal with between the two services.

If they are on separate machines it depends if the amount of time it takes to do the group by is more or less than the time it would take to transmitt the additional data over the network.

Performance of SQL

I am using Reporting Services and I have a heavily parameterised report which
update each others lists etc.
When I run the report through the web service, the sql; server goes
ballistic, and runs at 100% for several minutes.
I have looked in SQL Profiler at what is going on and the actual queries (my
SQL) is only taking a few seconds to run, but there is loads of other stuff
happening, moving around chunkdata and the like. Also looking at the current
activity, I can see
several Shared DB Locks on my database and it is all looking pretty nasty.
My reports are all dynamic so is there anyway that I can strip back all of
these other things. I am quite prepared to belive that there is a problem in
my code, but I could translate the entire database into French quicker than
my 3 second SQL runs.
Can anyone shed any light on this."Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Tom,
Try modifying your queries for using Stored Procedures. I am really not
aware how you are using the parameters or else prepare your data's in the
form of table or views so that you can run the reporting services on the view
or tables. if you are using 2005 prepare a report model and use it.
Regards
Amarnath
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thanks for the pointers, but I am confident that this issue is a little more
than that. I have about 40 reports, some of which are very straightforward
and some that arent.
All use a few parameters, and search through a chunk of data. It is the
difference between the speed of the SQL in Query Analyser and that in Report
Designer. I cant see how precomiled SQL ios going to make a significant
difference, and I cant do what I want to do with views as the query has these
parameters and performance would go down to several minutes if I did it that
way.|||You need to clean up the database, that would speed everything up. ^_^|||Make sure you are only returning the rows you need in your report ( no
filtering.)
Make sure you are not doing extra sorting in Groups, etc, that might already
be done in the SQL.
Take a look at the log in REporting Services, and it will tell you how much
time is spent doing each of three steps... rendering often can take quite a
while... All of the data has to be loaded into memory on the IIS server, then
aggregations etc are done there..
The best thing you can do otherwise is to begin to simplify a slow report...
reducing the number of rows, sorts, groups, filters etc until you can find
the culprit..
Also try to rending using different things html, pdf. etc and see if that
makes a difference..
Also try rendering from a snapshot and see..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thank you all fo0r your advice,
Except Sorcerdon. What makes you think you know anything about my database?
Anyway. I will have a look at the reports, maybe reinstall a few things and
see if it clears up the mess. It certainly appears that it relates to
something between my SQL and hitting nthe database. I know that my SQL is
fine, as I can run that against the db and it is fine. Does Report Server
dump the results off somewhere before rendering? Or is it straight back via
asp.net data providers.?