Large multi-select list box - performance
in [Reports]
|
From: emmanuel on 20 May 2010 06:30 I am in similar situation but with a 27000 item multiselect parameter. The following approach did improve performance a bit as it generates the string from the selected items in vb instead of sql/mdx but it is still quite slow in long parameter lists. http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/cf0ef6ba-d128-4acf-899d-48df71c60296 The info provided by this post is not 100% accurate (at least in my case) it needs some changes in the quotes around the query in OLEDB. Emmanuel peter feakins wrote: Large multi-select list box - performance 05-May-10 We have large multi-select list box (8000 items). When we run the report in visual studio (database is on separate server) the select box loads very quickly and when we click view report it renderes quickly ( both wothin a few seconds). When we deployed the report to the reporting services server and attempt to execute the report the remotely we have performance issues. The initial loading of the combo box takes over a minute and then the rendering after the report is executed will take close to 2 minutes. The queries themselves run very quickly. Other reports without the large multi-select execute very quickly. What accounts for the difference in performance between the two environments? -- peter feakins Previous Posts In This Thread: On Wednesday, May 05, 2010 12:56 PM peter feakins wrote: Large multi-select list box - performance We have large multi-select list box (8000 items). When we run the report in visual studio (database is on separate server) the select box loads very quickly and when we click view report it renderes quickly ( both wothin a few seconds). When we deployed the report to the reporting services server and attempt to execute the report the remotely we have performance issues. The initial loading of the combo box takes over a minute and then the rendering after the report is executed will take close to 2 minutes. The queries themselves run very quickly. Other reports without the large multi-select execute very quickly. What accounts for the difference in performance between the two environments? -- peter feakins On Wednesday, May 05, 2010 12:58 PM Bruce L-C [MVP] wrote: What version of RS? What version of RS? The 8000 item list, is it filled with a query or stored procedure? Seems unreasonable to have users scroll 8000 items. Have you considered cascading parameters? -- Bruce Loehle-Conger MVP SQL Server Reporting Services Submitted via EggHeadCafe - Software Developer Portal of Choice BOOK REVIEW: Effective C#, Second Edition [Addison Wesley] http://www.eggheadcafe.com/tutorials/aspnet/b2f8766d-a4c1-4d5a-97af-c38852b3b455/book-review-effective-c.aspx
From: Bruce L-C [MVP] on 20 May 2010 13:01 You really need to consider cascading parameters. Humans have to interact with the list. 27,000 item multiselect parameter is ridiculous. Good luck the user even being able to tell what they have selected or find what they want to select. Cascading parameters is the way to go. -- Bruce Loehle-Conger MVP SQL Server Reporting Services "emmanuel" wrote in message news:201052063028emmanuel.volikas(a)gmail.com... > I am in similar situation but with a 27000 item multiselect parameter. > > The following approach did improve performance a bit as it generates the > string from the selected items in vb instead of sql/mdx but it is still > quite slow in long parameter lists. > > http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/cf0ef6ba-d128-4acf-899d-48df71c60296 > > The info provided by this post is not 100% accurate (at least in my case) > it needs some changes in the quotes around the query in OLEDB. > > Emmanuel > > > > peter feakins wrote: > > Large multi-select list box - performance > 05-May-10 > > We have large multi-select list box (8000 items). When we run the report > in > visual studio (database is on separate server) the select box loads very > quickly and > when we click view report it renderes quickly ( both wothin a few > seconds). > > When we deployed the report to the reporting services server and attempt > to > execute the report the remotely we have performance issues. The initial > loading of the combo box takes over a minute and then the rendering after > the > report is executed will take close to 2 minutes. The queries themselves > run > very quickly. Other reports without the large multi-select execute very > quickly. > > What accounts for the difference in performance between the two > environments? > > -- > peter feakins > > Previous Posts In This Thread: > > On Wednesday, May 05, 2010 12:56 PM > peter feakins wrote: > > Large multi-select list box - performance > We have large multi-select list box (8000 items). When we run the report > in > visual studio (database is on separate server) the select box loads very > quickly and > when we click view report it renderes quickly ( both wothin a few > seconds). > > When we deployed the report to the reporting services server and attempt > to > execute the report the remotely we have performance issues. The initial > loading of the combo box takes over a minute and then the rendering after > the > report is executed will take close to 2 minutes. The queries themselves > run > very quickly. Other reports without the large multi-select execute very > quickly. > > What accounts for the difference in performance between the two > environments? > > -- > peter feakins > > On Wednesday, May 05, 2010 12:58 PM > Bruce L-C [MVP] wrote: > > What version of RS? > What version of RS? > > The 8000 item list, is it filled with a query or stored procedure? > > Seems unreasonable to have users scroll 8000 items. Have you considered > cascading parameters? > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > > Submitted via EggHeadCafe - Software Developer Portal of Choice > BOOK REVIEW: Effective C#, Second Edition [Addison Wesley] > http://www.eggheadcafe.com/tutorials/aspnet/b2f8766d-a4c1-4d5a-97af-c38852b3b455/book-review-effective-c.aspx
|
Pages: 1 Prev: Parameters from Dropdownlist Next: it did not work for me yet |