Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Report Filter: Long Drop Down List Performance Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 9:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 164, Visits: 478
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
Post #1448860
Posted Thursday, May 2, 2013 12:48 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:29 PM
Points: 1,481, Visits: 1,031
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.
Post #1448952
Posted Thursday, May 2, 2013 1:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 164, Visits: 478
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
Post #1448959
Posted Thursday, May 2, 2013 1:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,899, Visits: 32,113
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1448963
Posted Thursday, May 2, 2013 1:48 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:29 PM
Points: 1,481, Visits: 1,031
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.
Post #1448967
Posted Thursday, May 2, 2013 2:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 164, Visits: 478
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
Post #1448976
Posted Friday, May 3, 2013 9:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 1,051, Visits: 2,556
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
Post #1449262
Posted Friday, May 3, 2013 11:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 164, Visits: 478
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
Post #1449306
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse