SSRS Parameter Search on a long list ids
-
I am creating a searchable SSRS report where staff can search the data sest on client ids. Staff should be able to insert client id (one at a time) into client id search parameter and then get all the info about the client in the report. The issue I have is the data set includes more than 200K unique client ids and that has adverse impact on report performances. I have created a separate and unique Client ids data set that is being referenced in the main report client id search. Any suggestion as how to design the search for a faster results?
Thank You
- hmobasher - Friday, April 14, 2017 12:40 PM
Let me confirm my understanding is correct about your post/question.
You have a report with a parameter (dropdown list ? or combo box (multivalue)?) that allows staff to "insert" (I guess select ?)
This parameter is related to a column - client id (backend dataset is 200K).My question is, can you tell me how the staff filter the client - Is this parameter a "single-value drop down list" or "multivalue drop down list" ? or other ?
There is a couple ways to minimize the impact on loading, but need to clarify your question so I can tell how. -
Thank you for your quick response. The single parameter (client id search) is not a drop down option/filter due to it's huge size; 200K unique ids. Instead, staff already have client ids and will insert them onto client id search box. For example, client x has an id#=100. Staff will insert 100 onto the search box and report retrieve all related personal information about client id=100. Hope this clarified the issue.
Thank You
- hmobasher - Friday, April 14, 2017 3:05 PM
Sounds like you are on the right track. You mention you have a dataset that "includes more than 200K unique client ids". Is this is the place you are grabbing data out ?
What if you run the script/sp (on SSMS) of the dataset (includes 200K unique clientids) directly, with and without a "where ClientID = xxx", what's the performance would be (like, how long. seconds or minutes) ?
See if you are OK to provide your main report query or the slow one, this would better explain.BTW, please do a "select count(*) from table" (the main report query), would like to know how many rows in the table.
I have a similar report but I allow user to search client by name and cascading to another parameter (showing final search result), then passing to main report query, it's fast since the final query is only searching the limited clients (with some extra calculation).
-
Thanks for all your tips and tricks. The total count is 193787. Now I added another parameter Clientname search that works (please see picture below). However, I want client id and client name searchers be independent of each other. So far, client name search works great but not client id search. When inserting an id into client id search, it loads all client names first and that take a while. This is not true for client name search. Here is my main stored procedure
Main SP
------------------------------------------
Alter procedure dbo.cms_clientsearch
@Clientid VARCHAR(max),
@Clientsearchname VARCHAR(max)
as
select *
from AnalyticsCutomTables.dbo.CMSClientTableWHERE (Clientid IN (SELECT VALUE FROM AnalyticsCutomTables.dbo.Split(@Clientid,',')) or @Clientid ='')
and (Clientname IN (SELECT VALUE FROM AnalyticsCutomTables.dbo.Split(@Clientsearchname,',')) or @Clientsearchname ='')
-------------------------------------------------------------------------------------------------------------------------Then I have a second data set (cmsnamesearch) for name search and three parameters (clientid, Clientname, and Clientsearchname). I believe I have to pass a Blank to name search so when I only search clientid data shouldn't be populated first and should accept blanks. I don't if this make any sense to you.
Thank You,
-
Ok, a couple things.
1. cms_clientsearch stored procedure is the main report query output. If this is the case, and client name works fine, but not for client id, this is pretty odd since you have the same searching criteria.
In this case, can you tell me the data type of your Client ID (original table, CMSClientTable)
(I believe the table function - AnalyticsCutomTables.dbo.Split(@Clientid,',')) is doing nothing but split a comma delimited string (like id1,id2,id3 into a table/set))
If the Client ID is int, then the performance in here would be the cause of data type conversion.
And, what's the 3rd parameter - Client Name used in this report ?2. In case if you only allow one type/column search at a time (say Client ID or Client Name), I would suggest you create a dropdown parameter, with value 0 label ClientID, value 1 label ClientName
After user pick up one (say 0, ClientID), your search will based on this Parameter and search against that criteria, like below,
Alter procedure dbo.cms_clientsearch
@SearchType int, --0: ClientID, 1: ClientName
@Clientsearchterm VARCHAR(max)
as
if (@SearchType = 0)
begin
select *
from AnalyticsCutomTables.dbo.CMSClientTable
WHERE (Clientid IN (SELECT VALUE FROM AnalyticsCutomTables.dbo.Split(@Clientsearchterm,',')) or @Clientsearchterm ='')
end
else
begin
select *
from AnalyticsCutomTables.dbo.CMSClientTable
where (Clientname IN (SELECT VALUE FROM AnalyticsCutomTables.dbo.Split(@Clientsearchterm,',')) or @Clientsearchterm ='')
end -
Continue my previous post, here is a couple guessing on what I see from your report:
1. All 3 parameters looks like dropdown to me (I see they all have down arrow, it's telling me they are multi value parameter). Tell me if they are actually single value - just like a textbox.
2. You wish to search by [Search Client ID] (Parameter1), result goes to [Client Name] (Parameter3) or [Search Client Name] (Parameter2), result goes to [Client Name] (Parameter3), and then click on View Report based on the final result from [Client Name] (Parameter3), is this correct ?
If yes, do you want to let user repeat their searching (mix between client id and client name) and view multiple clients at the same time ?
3. If the ClientID datatype is int , your stored procedure should be like below,
Alter procedure dbo.cms_clientsearch
@SearchType int, --0: ClientID, 1: ClientName
@Clientsearchterm VARCHAR(max)
as
if (@SearchType = 0)
begin
select *
from AnalyticsCutomTables.dbo.CMSClientTable
WHERE (Clientid IN (SELECT convert(int, VALUE) FROM AnalyticsCutomTables.dbo.Split(@Clientsearchterm,',')))
end
else
begin
select *
from AnalyticsCutomTables.dbo.CMSClientTable
where (Clientname IN (SELECT VALUE FROM AnalyticsCutomTables.dbo.Split(@Clientsearchterm,',')))
end
You can also apply below back to your current stored procedure like this,
Alter procedure dbo.cms_clientsearch
@Clientid VARCHAR(max),
@Clientsearchname VARCHAR(max)
as
select *
from AnalyticsCutomTables.dbo.CMSClientTableWHERE (Clientid IN (SELECT convert(int, VALUE) FROM AnalyticsCutomTables.dbo.Split(@Clientid,',')) or @Clientid ='')
and (Clientname IN (SELECT VALUE FROM AnalyticsCutomTables.dbo.Split(@Clientsearchname,',')) or @Clientsearchname ='')In my final code, I removed the = '' in both criteria, cause it doesn't make sense to have it in "if" filtered structure.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply