Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Report Filter: Long Drop Down List Performance


Report Filter: Long Drop Down List Performance

Author
Message
darkhelmutis
darkhelmutis
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 840
So I have a VERY simple report returning three columns from one table. It has four report filters:

Transaction ID
User Name
Start Date
End Date

The first filter, Transaction ID, would ideally return a list of ALL Transaction IDs in the table. The problem is there are more than 700,000 rows. This drags the report performance to its knees. Right now, it only returns the most recent 1000 filters.

So is there a way to return all 700,000 rows and maintain performance here given that this is SSRS 2005? I don't think cascading report filters are an option here (I think that is SSRS 2008 or newer). In other words, I don't think I can filter the results of the Transaction ID filter by the other three filters.

Any suggestions?

Thanks,
DH
Ray M
Ray M
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1076
My .02c
From a user perspective, who wants to scroll thru 700,000 id's?
especially because the ssrs drop down does not allow type ahead, and the list is small
From a technical perspective, no platform, (asp.net, SSRS) will cleanly handle 700,000 items in a dropdownlist.

Your going to have to get creative.
Output the id's to a table, with other information that will help the user.
Then the report will drilldown when a user selects one of the id's?
Not sure what your trying to accomplish.
darkhelmutis
darkhelmutis
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 840
I agree, 700,000 rows in not feasible, thus why I am looking for options. Sadly SSRS 2005 is pretty limited in that regard.

The Transaction ID is one of the criteria the end user can use to identify the Transaction in question. They will sometimes look up very old Transaction IDs at that.

For the moment, they can overcome this by using the Start and End Dates.

We are in the process of upgrading to SSRS 2012, so I may not bother any further with this. Thanks for the .02 cents!

DH
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14973 Visits: 38992
how about changing the drop down into an optional text field...that way if the user KNOWS the ID, they can just type it in;

i'm no SSRS expert, but i do a lot of web stuff; my rule of thumb is > 50 values really needs a different selection criteria than a SELECT / drop down list.

I usually have a search screen that allows them to search on various criteria, and then provide links to the details based on their search...and i typically limit it to 100 records at a time at the most.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ray M
Ray M
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1076
darkhelmutis (5/2/2013)
I agree, 700,000 rows in not feasible, thus why I am looking for options. Sadly SSRS 2005 is pretty limited in that regard.

The Transaction ID is one of the criteria the end user can use to identify the Transaction in question. They will sometimes look up very old Transaction IDs at that.

For the moment, they can overcome this by using the Start and End Dates.

We are in the process of upgrading to SSRS 2012, so I may not bother any further with this. Thanks for the .02 cents!

DH

Then allow them to run a report that searches for TransactionID's between dates.
which outputs the id's to a data table then link to another report that calls your report by ID.
and / or allow them to just type it in as Lowell suggests.
darkhelmutis
darkhelmutis
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 840
I figured out that it is possible to use cascading filters (i.e. dependant filters) in SSRS 2005:

This is how I did it:

- Updated the SP that retrieved the Transaction IDs to include @start_date and @end_date parameters, both set to a default value of NULL

- Updated the dataset based on that SP in the report to now include two parameters whose values are retrieved from the report filter paraters Start Date and End Date

- Reordered the report filters so that the Start Date and End Date filters occur first

Voila, now the Transaction ID filter only displays the Transaction IDs that apply to the time frame. Yes there is the annoying screen refresh. Yes it is possible that someone could specify a large date range that returns a lot of records. I will consider strategies to deal with the later.

Next step will be to convert the SP to use dynamic SQL with an explicit parameter list so that I can have a variable predicate and more stable execution plans!

Thanks for the feedback guys!

DH
wolfkillj
wolfkillj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 2582
darkhelmutis (5/2/2013)
I figured out that it is possible to use cascading filters (i.e. dependant filters) in SSRS 2005:

This is how I did it:

- Updated the SP that retrieved the Transaction IDs to include @start_date and @end_date parameters, both set to a default value of NULL

- Updated the dataset based on that SP in the report to now include two parameters whose values are retrieved from the report filter paraters Start Date and End Date

- Reordered the report filters so that the Start Date and End Date filters occur first

Voila, now the Transaction ID filter only displays the Transaction IDs that apply to the time frame. Yes there is the annoying screen refresh. Yes it is possible that someone could specify a large date range that returns a lot of records. I will consider strategies to deal with the later.

Next step will be to convert the SP to use dynamic SQL with an explicit parameter list so that I can have a variable predicate and more stable execution plans!

Thanks for the feedback guys!

DH


Glad you figured out the cascading report parameters. That's exactly how I used to do it when I spent most of my time developing reports in SSRS 2005. And yes, the screen refresh after selecting the first parameter values is annoying!

I'm curious about what you mean by "convert[ing] the SP to use dynamic SQL with an explicit parameter list so that I can have a variable predicate and more stable execution plans". Are you saying you want to allow the user to select values for only some of the parameters and then filter the results using only those parameters (i.e., a catch-all query)? If so, you're right on that dynamic SQL is the way to go - this blog post gives an example of how to do it. Good luck, and open another thread here if you run into trouble.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
darkhelmutis
darkhelmutis
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 840
Wolf-

You are exactly correct. I generally never create catch-all queries and opt for the dynamic sql implementation every time. Gail Shaw did a great write up on the problems with the catch-alls. Following Erland Sommarskog write up on the right ways to do dynamic SQL also helps.

I can't wait till I can put this SSRS 2005 implementation in the grave and move onto SSRS 2012.

DH
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search