January 17, 2005 at 4:26 pm
I'm stumped
Been using Reporting Service for almost a year. I'm currently working on a report that runs very slow in Report Manager and Report Designer. This report relies on a stored procedure that has 4 parameters.
The procedure runs consistently under 5 seconds in both dev and production from Query Analyzer. The results are near instant and I have run numerous sets of parameters against the sp.
BUT, when I run this new report from within the Report Manager or Designer it takes minutes. There may be some db contention in production but none in dev. I also started a new report, added the sp and without even going to the layout tab, tried to run the report. Same thing..very slow.
Anybody else ever see this. Been doing this a long time and this is the first I've seen of this.
Thanks
Mardy
January 17, 2005 at 11:08 pm
You might have already tried this but if you have not and you do have lot of SQL Statements embedded in your Stored Procedure then one trial would be to set nocount off. This way it will avoid any extra round trips. Have you benchmarked the execution of the Stored Procedure while running from query analyzer against the execution of Stored Proc when executing from the report designer.
January 18, 2005 at 6:26 am
A profiler trace is a good idea. I 'll see if I can learn something from that.
Thanks
January 18, 2005 at 10:33 am
Well the trace helped alot. As I ran the sp, I noticed that in some cases the number of reads was significantly greater than in other cases. I tweaked and tested and have found that in some cases the db is using indexes better than in others. I still don' understand what is happening. The sp is basic as follows:
CREATE PROCEDURE dbo.rpt_AuditByTheatre
(
@start_date varchar(32) = null, --datetime = null,
@end_date varchar(32) = null, --datetime = null,
@theatre_id varchar(25), -- Required
@description varchar(255)= 'ALL'
)
AS
BEGIN
SET NOCOUNT ON
IF @start_date is null
SET @start_date = convert(datetime,convert(varchar(10),getdate(),1))
ELSE
SET @start_date = convert(datetime,@start_date)
IF @end_date is null
SET @end_date = convert(datetime,convert(varchar(10),getdate(),1)) +1
ELSE
SET @end_date = convert(datetime,@end_date) +1
SELECT s.store_ref,
s.site,
a.performance_number,
a.audit_result,
u.description,
a.played,
case when u.description='Lobby' then 'Lobby' else Right(u.description,2) end AS Adtm,
a.ad_name
From audit a
JOIN unit u (nolock) on a.unit = u.unit
JOIN site s (nolock) on u.site_id = s.site_id
Where a.played >= @start_date and a.played < @end_date
and s.store_ref = @theatre_id
and (upper(u.description) = upper(@description) or @description = 'ALL')
ORDER BY 1,7,6
END
GO
When I run this with the @start_date and @end_date the same date (i.e. 11/20/04 to 11/20/04, the execution plan is different than if I run the procedure for 2 days as 11/20/04 to 11/21/04. Very strange. The difference in the execution plan results in almost 8 million reads (profiler) versus 2 thousand for the 2 day range. Is there something else going on or does it make sense that the optimizer would choose a different path based on parameter value?
Thanks
Mardy
January 19, 2005 at 4:20 am
Could you post the execution plans for the two date ranges you mentioned so that we can see what is happening?
You could use Index hints to try and force the execution along the more efficient path.
Nigel Moore
======================
January 19, 2005 at 8:45 am
Nigel
Thanks for the reply. Be gald to post the execution plans but not sure the best way to do it. For now I'll pass along what I learned from an MS newsgroup.
Apparently, the parameters in my sp are getting sniffed. According an article at http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#EEAA,
Parameter sniffing refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans.
In my case the sniffing leads to slower performance. I added a LOOP hint to the join and that solved the problem. Interestingly, another solution was to add two new variables for the start and end date and set the variables equal to the start and end date parameters. Change the where clause condition to use the variables instead of the parameters. In general like this:
create proc usp
@start datetime,@end datetime
as
declare @s-2 datetime,@e datetime
set @s-2=@start
set @e=@end
select *
from tb
where dt between @s-2 and @e
go
It worked. The best I can conclude is that the variables provide some sort of sniff protection.
Mardy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply