Slow SSRS stored procedure dataset

Question

Post reply

Slow SSRS stored procedure dataset

DamianC

SSCrazy Eights

Points: 8006
More actions
February 25, 2016 at 9:42 am

#325067

Hi

I have an issue with a very slow dataset (initial response and use) that works perfectly well in SSMS

I have a stored procedure that takes 2 parameters (startdate and enddate)

In SSMS, it runs it about 2 seconds

I have a report that uses this as a dataset in SSRS

Again, this works in a couple of seconds

I've then taken that procedure, renamed it and added another couple of attributes

Now, in SSMS, it continues to execute in about 2 seconds

In SSRS, I am having issues

If I try and add this as a stored procedure based dataset, it takes about 5 minutes before eventually coming back

I can then use this and create a new report (or amend my existing report)

Now when I execute the new report that uses the new SP, it takes about 10 minutes to run (not 2 seconds!)

I've read a lot about parameter sniffing and method such as with compile and set arithabort on

Nothing seems to make this any quicker

Here's my start:

create PROCEDURE [dbo].[spGetClaimPaymentDetailsv3](@StartDate AS DATETIME, @EndDate AS DATETIME) with recompile as set nocount on; SET ARITHABORT ON; DECLARE @sd AS DATETIME = @StartDate DECLARE @ed AS DATETIME = @EndDate

I then use @sd and @ed rather than @StartDate and @EndDate

Is there anything else I should be doing here?

Thanks

Damian.

- Damian

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

DamianC SSCrazy Eights Points: 8006 More actions · Answer 1

Hi

Any thoughts on this

It is seriously winding me up now!

I've rewritten in a number of different ways and SSRS just keeps hanging when trying to open up stored procedures

I can see the data in SSMS

All I want is to see this in a report

My original SP that has worked fine for months is now hanging in SSRS

I'm not really liking SSRS right now!

Thanks

- Damian

DamianC SSCrazy Eights Points: 8006 More actions · Answer 2

Finally have a solution and it relates to SETFMT ONLY (Something I had never heard of) and the way SSRS handles result sets

Refer to this link:

http://stackoverflow.com/questions/21434827/no-fields-for-dynamic-sql-stored-procedure-in-ssrs-with-set-fmtonly

What worked for me was to put this little piece of code (where Col1, Col2 ... are my result set column names) just after 'as' within the stored procedure



if 1 = 2

  begin

    CAST('' AS NVARCHAR(20)) AS Col1,

    CAST(0 AS DECIMAL(5,3)) AS Col2, ...

  end;

It now responds instantly!

- Damian