Fast query runs slow in SSRS 2

Question

This is basically the same question as "Fast query runs slow in SSRS" however nothing in there I could see worked for me.

Context: Created a Report Builder dataset to use for my report, query is:

SELECT  DISTINCT A.A
,   A.B
,   A.C
,   A.A + ', (' + A.B + '), : ' + CAST(A.C AS VARCHAR) D
FROM Table1 X
INNER JOIN TableA A ON X.Key = A.Key
WHERE (X.Col1 IN (@Param1))
AND (X.Col2 IN (@Param2))
UNION
SELECT '(All)', '(Select to apply all)', 0, '<APPLY ALL>'
ORDER BY C, A
OPTION (OPTIMIZE FOR UNKNOWN)

I have 3 multi valued parameters. The first (@Param1) and second (@Param2) lists load fine and the second is not dependent on the first. The third list which uses the dataset described above is dependent on both 1 and 2 as you can see from the query above.

I add the OPTION (OPTIMIZE FOR UNKNOWN) clause as an attempt to speed it up but to no avail. I do not even know how to declare a multivalue parameter anyhow so this was my only option to try at this stage.

After I am finished selecting values from my second dropdown/parameter and move to the third parameter the report loading icon thing churns for about 5 seconds than it just halts and nothing happens after that and I never get to select from my third dropdown unless I am prepared to wait until it finishes but that would seem to be about 20 minutes, so then after a while I just kill the web-page and start again. When I run the report query in SQL Server in runs in a second and when I run thru the report in the Report Builder IDE it works fine a little delay like seconds as compared to 20 minutes however running via SharePoint/SSRS it takes 20 minutes.

Does anyone have a suggestion to try?

Answer 1

I found my own solution more by accident than ingenious thought and which was purely a mistake on my behalf so sorry to waste anyone's time however it still left me a little perplexed, or perhaps annoyed is a better way to describe it.

Apparently one of my attempts to improve performance also included caching the DataSet which was done a week or so earlier. I had "Cache shared dataset" turned on with a specific daily schedule however no "Cache Refresh Plan" if that makes a difference - probably does as it may have still been using the cached version, i.e. perhaps changing a dataset does not actually force a automatic refresh when loading the data in the drop-down on the report. So in the process of updating my query which was after I had set the caching I went from straight sql to a multi-statement table valued function (with Option optimize for unknown) then to a stored procedure that called my function. So after updating the query in the dataset and refreshing my report that used the dataset because of the cache it was probably not applying my latest change. I turned off the cache and then my report third parameter (Dropdown) started responding. Still quite a bit slow when running in SharePoint where in report builder IDE it is instantaneous however, down to about 10 seconds as opposed to infinite amount of time. So the question I am left with is was using a stored procedure actually effective or was the fact I used the option on the query and further whether to cache or not to cache if it is going to cause a non obvious problem, i.e. will not refresh until the next scheduled period and I am not sure if having a "Cache Refresh Plan" whether it would make any difference. And also more importantly why does running a query via SharePoint as supposed to MS Server Management Studio so much more intricate, I am sure if I wrote my own custom web-page that called the same query it would also be instantaneous - anyhow things to go back to experiment with if I can be bothered.

Obviously data changes overtime and the report similar to another post on this site was working fine then all of sudden it became non-responsive anyhow it is working good enough for now without a need to cache.