SSRS 2016 Parameter Search on a long list id

Are you pulling all 200,000+ results into a dataset and then filtering? That could be very slow. Pass the client ID as a parameter to the SQL query so that it returns only that data that you need in the dataset for the given parameters. With good indexing on the underlying tables there should be almost no delay.

what i can understand from the explanation is.

You have parameter like "UserID" and that parameter is getting value from the data set "UserID".

Now as you wrote that, user id data set has 200K+ id. It will defiantly make your report slow. Even it will take time to render the report in preview mode. As it will get 200K user id and bind that to parameter you have created.

One solution is that remove the Drop down parameter and set it as free text. Based on the input provided by report user, you can query your database and show the detail.

And if you can not remove the drop down. if your user id is sequential.
you can create one parameter like "User ID Range".

0 - 10000
10001 - 20000
20001 - 30000 and so on.

When user select range in first parameter based on that value populate the "User ID" parameter.

This way you will bind only 10K value at a time and it might improve the performance at some extent.

Hope it will help you.

Thanks
Arif

Thank you Megan & Arif for great suggestions. In fact, I am passing the parameter thru SQL and since this will be a multi value & multi parameters, I have the following main query for to create first data set (CMSClientSearch) that should allow search by Clientid or by Client name.

select *
from AnalyticsCutomTables.dbo.CMSClientTable
where
(isnull(ClientID,'')  in (@Clientid)) and   (ClientName like '%' + @Clientname + '%')

MY second data set (Clientidlist) includes:
SELECT DISTINCT isnull(ClientID,'') as Clientid, isnull(ClientName,'') as ClientName
from AnalyticsCutomTables.dbo.CMSClientTable
ORDER BY ClientID

I have two parameters Clinetid and Clientname that get the list from Clientidlist data set. There are two issues:
1. Despite passing parameters thru SQL, I still get drop down for both
2. Even with filtering/drop down, the performance is not great but not bad either. However, I can't choose one or the other both parameters has to be populated in order to get the desired data.

Hope this make sense.

Helal

"ClientName like '%' + @Clientname + '%'" is a performance killer when executed against a large table, because it can't use an index. The use of "%" at both ends of the pattern is the problem. You can index if the "%" appears at only one end.

Depending on the total amount of data in each row, you might be able to speed up the query by providing a non-clustered index containing only the ClientName column. That could reduce the total volume of data to be read, if you are currently searching against the clustered index and if there is a lot of other data in each row. The biggest improvement, however, would come from eliminating the leading "%" in the pattern.

If ClientName contains multiple delimited parts, such as prefix, names, and suffix, you might be able to parse it into components and build a search table using those. That could improve the chances of matching without using the leading "%".

By the way, "select *" may also affect performance, if it causes columns to be pulled into the dataset that are never used.

I'm not clear on what you are saying about having to populate both parameters (available value lists, presumably). If the two parameters are independent then the parameter available value list datasets will both run in parallel and load all of their data. If you want the second parameter dataset to filter based on the value selected for the first parameter, you can do that.

The first parameter dataset will still have to run unfiltered, but the query for the second dataset can be parameterized using the value of the first parameter. RS will run the first dataset, enable the first UI parameter, and disable the second parameter in the UI. Once parameter 1 is selected by the user, RS will run the second dataset using that parameter, and enable the second parameter in the UI, which may now contain a much shorter available value list.

Great tip on removing leading %. I can't still figure out
1. Why I am still getting drop down list on the two parameters; Clientid and Clientname! I am passign them thru SQL where clause and allwoing for Blanks in parameter option.
2. I need the two parameters independent of each other...meaning, staff can search by Clientid only or by Clientname. Thus, the two shouldn't be dependent. on each other.

BY the way, both Clientid and Clientname are varchar.

Helal