Slow SSRS stored procedure dataset

• DamianC

  SSCrazy Eights

  Points: 8023

  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

• DamianC

  SSCrazy Eights

  Points: 8023

  More actions

  February 26, 2016 at 7:05 am

  #1861382

  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: 8023

  More actions

  February 26, 2016 at 8:28 am

  #1861413

  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