September 7, 2006 at 3:04 pm
I have a simple query that takes only 3 seconds to run from Query Analyzer, but it takes a couple of minutes to run in a simple matrix control in Reporting Services 2005. I'm new to this and have successfully set up a couple of other reports. RS is on a workstation running Windows XP (yeah I know, it's for testing) with 1GB of RAM. I thought the fact that it's a stored procedure with parameters that limits the rows would eliminate this problem. Any suggestions? Thanks!
Create proc usp_bi_sales_by_cust
@FiscalYear int =null,
@begperiod smallint =null,
@endperiod smallint =null
as
Set nocount on
select
FiscalYear,
FiscalPeriod,
CustNum,
CustSeq,
Name,
City,
State,
Zip,
Sales = sum(Sales),
GM = sum(GM)
from bi_sales_cust
where
FiscalYear >= @FiscalYear and
FiscalPeriod between @begperiod and @endperiod
group by
FiscalYear,
FiscalPeriod,
CustNum,
CustSeq,
Name, City, State, Zip
smv929
September 7, 2006 at 3:59 pm
I had an issue like this but it was not reporting services causing the problem. It was the execution plan when it was a store procedure vs as a query in query analyser.
In query analyser it was doing a table scan. As a store procedure it was doing a index seek and a book mark lookup. Sounds stange but this added 20 mins to the process. I had to add a index hint to force a table scan to get around this.
Don't know if that is your problem but good luck.
September 10, 2006 at 3:54 pm
i'd try a table object and see if this runs faster?! maybe the rendering to a matrix is the slow part as opposed to the query. How quickly does the query run in the data pane?
I'm assuming that you're just returning the data without any further processing in the report, if so then this is a little odd as you're calling a proc which is MS's recommendend option....
hth
Martin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply