Report Manager Performance (or lack thereof ?)

i am in the process of creating some new reports on a new Reporting Services site

reports developed in VisualStudio2005 pointing an an AnalysisServices Cube on a (development) SQL2005 database

when i Preview the reports in Visual Studio 2005 on my development box, the reports take about 12-15 seconds to run (about 4-5 seconds before the "Report is being generated" logo appears, then another 8-10 seconds before the report appears (report is a series (1-8) of charts))

when i run the same report (with the same report criteria) via the Report Manager (pointing at the same AnalysisServices Cube), it now takes about 45 seconds to run (about 15 -16 seconds before the "Report is being generated" logo appears, then another 30 seconds before the report appears)

(n.b. this is not due to initial Report Manager start-up delays, the timings are for the second and third run of the report)

can anyone out there point me in the direction of where to look ?

also

have just had a look at the entries in the ExcutionLog table of the ReportServer database

ran the report - it took c. 30 secs to run & render

in the table

TimeStart 18/04/2007 1:20:48 p.m.

TimeEnd, 18/04/2007 1:20:57 p.m.

TimeDataRetrieval 187

TimeProcessing 539

TimeRendering 210

dont know how reporting services thinks it did it in 9 seconds ?!?!?!?!

Report hangs 4 CPU server

Forgive my lack of knowledge on SQL here...

We have a SQL server, version 7, that has 4 CPU's. Some of our staff have reports that must be run at random times before cutting checks etc. and these reports bog down the servers CPU's big time, enough so that we can not access the server via Enterprise Manager and at times not even through Query Analyzer. During this time of course other users can not do standard work of saving or accessing the database. Our Processor queue length never seems to go over 1 now. (We recently went from 2 to 4 CPU's do to a 1.4 average queue length).

I have heard that is is possible to limit the number of CPU's that certain query's or events can use. What is the proper terminology for this and can a person do this with limited knowledge? If this can be done during "working hours" so that during off hours query's can use all CPU's, even better.

Thanks,
Mike GilbertYou probably want to think HARD before you do this, but you can set the Max Degree of Parallelism (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_7h9q.asp).

-PatP|||How much ram does this machine have? Is upgrading to SQL2000 an option? What are the processor speeds?|||This is a typical situation, where everyone looks at the DBA for the solution. My gutt feeling would be to run the SQL Profilier, capture the query and do an analysis of the Query. Before thinking about changing the SQL Server HW set up, make sure that you do the following:
1. Test the Query running for the report
2. Run update Stats
3. Recompile the Query
4. Check for the Locks

Also how Big is your databases?