Using SSRS 2008 R2 I have a report with one of the parameters being driven off a list of values from a table. This list has around 25000 rows in it. At run-time, the report runs slooooow and gets stuck for almost 150sec before it shows up in the SQL Profiler and comes back with results. Happens both in BIDS and ReportServer (native mode and SP integrated) Any thoughts?-Remember to mark as helpful/the answer if you agree with the post.

Any thoughts? Don't pull 25,000 rows into a dropdown, for starters. :-) Is there any way that you can setup a tiered set of dropdowns? For instance: if you're trying to show a dropdown with 25,000 products, could you ahve a dropdown with the product category, and populate the list of products based on the category selected? This means that a user would have to go through a couple of clicks to get their product, but it also means they don't have to scroll through an entirely unmanageable list. Cheers, -- Mike Helms

Don't pull 25,000 rows into a dropdown, for starters. :-) Believe me, that was the suggestion I made to the business too :) - but you know how that can go! This is a list of Brand Names of products and is NOT categorized in a hierarchy, and is the starting point of the report criteria. Why do you think SSRS chokes on such a list? It populates fine, and hangs at the time of rendering the report, The ExecutionLog table in the SSRS database, or the table in the underlying database is not even accessed all this time. Any other thoughts?-Remember to mark as helpful/the answer if you agree with the post.

If you can't use cascading parameters as was suggested by Mike then you have to implement your own parameter area. To do this you can use the ReportViewer control in remote mode.

It populates fine, and hangs at the time of rendering the report, The ExecutionLog table in the SSRS database, or the table in the underlying database is not even accessed all this time. Hi Naveen Das, Actually, the process of report displaying contain three parts: data retrieval, report processing, or report rendering. So the time it spent should be the summation of these three parts, so you should analysis delay time is in which part, then give some optimization in relevant part, for optimization method, please follow these: My data takes too long to retrieve My report takes too long to process My report takes too long to render Thanks, Challen Fu Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Why do you think SSRS chokes on such a list? It populates fine, and hangs at the time of rendering the report, The ExecutionLog table in the SSRS database, or the table in the underlying database is not even accessed all this time. Any HTML dropdown object with 25,000 elements is going to run slowly. It wouldn't take long to code a simple HTML page with a static dropdown with 25,000 elements to test this hypothesis ... and I'm fairly certain you'd see the browser choke uncomfortably. 25,000 data elements is nothing for any respectable SQL database, and moving that data from the database to the report server should be equally non-eventful. If these elements were being aggregated by the report, it would probably also go quickly ... but you're rendering each object into HTML, which then gets interpreted by the browser. At this point you're at the mercy of the client machine's power -- and even if they're running a very powerful machine, rendering the HTML is still an interpretive process. If the dropdown list is a bunch of name brand products, could it first be filtered by the brand? Just thinking about ways to make a long list smaller ... even if you coull get it down, say, to a thousand objects, I think you'd see performance increase notably. Cheers, -- Mike

Thanks Mike - I am sure it is the HTML rendering that the report 'hangs' on... I will have to look for alternatives to the dropdown than we have currently. Is there a way to confirm the theory? Monitor some events, logs, etc?