0

I am working on an SQL report that uses a multi-value parameter that contains a total of 41 users. The report works fine if I select 1 or up to 19 total users, but breaks if 20 or more are selected from the list.

By break I mean it attempts to execute for 40+ minutes before I kill it). When running for 1 or for 19 users the report takes 1:10 to run. I am using two datasets.
One - my main query in which the parameter is used.

Two - The second query to acquire the list of users for the SSRS parameter.

I use this method frequently with no issues for things like locations, insurances, etc.

The parameter is called in a WHERE statement like so: AND EventUserID IN (@user)

If I comment that line out and use: AND EventUserID IN ('KTR','GORCN',......) with the full list of usernames that were acquired with the same query that is being used in the second dataset it works fine and will return the full report.

I have tested it with different groups of users to make sure that one of the users weren't breaking it, but that didn't matter. I also should mention that the query for the second dataset is one I used from another report that uses it the same exact way. That report will run fine will all users selected (parameter properties are set the same).

I am working with MS SQL Server and MS Visual Studio. More details can be provided if necessary.

Thanks in advance for your time and assistance.

ViKiNG
  • 1,294
  • 2
  • 19
  • 26
GKRobb
  • 37
  • 6
  • so what is @user? That has the full list of usernames stored in it? – Ryan Gadsdon Oct 11 '17 at 15:42
  • `IN` does not actually accept a single parameter that is a list of values. If `AND EventUserID IN (@user)` is legal syntax in SSRS, internally this is textually expanded to a full list of values. The query this results in apparently has a bad execution plan associated with it, but if a full list of constants works, the difference should be rather subtle. If your server is not one that is absolutely mission critical with regards to performance, see if `DBCC FREEPROCCACHE` forces recompilation to a more effective plan. (Never do this reflexively on a busy OLTP server.) – Jeroen Mostert Oct 11 '17 at 15:44
  • Beyond that, the usual song and dance for tackling slow queries applies: use Profiler to get the exact query, determine the execution plan from SSMS, update your statistics if necessary, check for missing indexes. That SSRS is the producer of the queries doesn't change the general approach; it just potentially makes the solution more complicated if the query can't be rewritten. – Jeroen Mostert Oct 11 '17 at 15:48
  • @RyanGadsdon '@user' is the variable used by the SSRS parameter. – GKRobb Oct 11 '17 at 16:16
  • but does it hold the values you're comparing using the IN clause? – Ryan Gadsdon Oct 11 '17 at 16:25
  • The first thing I would suggest (like some others) is to use the profiler to see exactly what is being requested on the server. If the statement runs slow in SSMS (which it should as it's the same request) then try some of the above. I usually stick an `option (recompile)` at the end to eliminate parameter sniffing.After that look at the execution plan and see if indexes are potentially missing. Report back here and take it from there. – Alan Schofield Oct 11 '17 at 19:04
  • @RyanGadsdon Yes, it gets them from a query in my second dataset that returns all of the UserIDs/Names – GKRobb Oct 11 '17 at 19:05
  • @AlanSchofield In SSMS the query runs in 1:10, with ALL userIDs in a 'WHERE EventUserID IN ('id1','id1','id3',....) statement. It is failing in Visual Studio when trying to 'Preview' the report. I set the date parameters and Select ALL user IDs and it fails. – GKRobb Oct 11 '17 at 19:08
  • Hi GKRobb, did you manually write that statement or did you get it from a profiler trace? It's important to get it from the profiler as it might not produce the exact SQL you expect. – Alan Schofield Oct 11 '17 at 19:10
  • Make sure you have an index on the [EventUserID] field in the table with the WHERE EventUserID IN statement. – Russell Fox Oct 12 '17 at 20:30

1 Answers1

0

This looks like an issue I came across a while back. You essentially have a limited number of parameters you can pass back to SSRS.

Here's some similar issues:

You can increase this from the web.config file though

Basically you want to add a section in your web.config file inside the <appsettings/> section:

add key="aspnet:MaxHttpCollectionKeys" value="9999"

That 9999 value should represent the number of parameters that you believe will be used.

If using SharePoint integration Mode: C:\inetpub\wwwroot\wss\VirtualDirectories\\web.config

If using SSRS native Mode: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer.

CPorteous
  • 957
  • 6
  • 10