InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 03:32:06 GMT20http://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxThats a great solution but i have a problem, my parameters have multiselect option setting local parameters is giving me error, can any one help... pleaseQuery - DECLARE @LOCAL_VAR_ORGN VARCHAR(MAX) SET @LOCAL_VAR_ORGN in (@Organisation)DECLARE @LOCAL_VAR_BL VARCHAR(MAX) SET @LOCAL_VAR_BL in @BusinessLineDECLARE @LOCAL_VAR_SE VARCHAR(MAX) SET @LOCAL_VAR_SE in @SalesExecutiveselect vd.Month, vd.month_No, vd.Year, vd.Week, vd.Date, vd.Organisation, vd.[Business Line], vd.[Sales Executive], count(distinct vd.CSTMR_KEY)[Total no of Lines], ( case when SUM(vd.[Total Amount Due])&gt;0 then COUNT(distinct vd.CSTMR_KEY) else 0 end )[Total No Of Unpaid Lines], SUM(vd.[Total Amount Due])[Total Amount Due] from IV_DBT_RPT_FR_ORGNSTN VD where (vd.Date&gt;=@FromDate and vd.Date&lt;=@ToDate)AND VD.Organisation IN (@LOCAL_VAR_ORGN)AND VD.[Business Line] IN (@LOCAL_VAR_BL)AND VD.[Sales Executive] IN (@LOCAL_VAR_SE)group by vd.AR_BHVR_KEY, vd.[Business Line], vd.C, vd.CNTRCT_DD, vd.CSTMR_KEY, VD.Year, vd.Date, vd.month_No, vd.Month, VD.Week, vd.Organisation, vd.[Sales Executive]--order by VD.DateFri, 01 Mar 2013 11:47:10 GMTsaxena200http://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxHi,How did this issue get rresolved?I am facing a similar problem now but could not find any solution.I've a stored procedure which is taking 22 sec when i execute it from SSMS.The number of rows being returned are 2962.But when i execute the Report which is uses the same stored proc from report server, is taking 3 min to process.I have checked the report execution log and it shows more time for data retrieval but not rendering or processing.My report does not have any aggregations or groups .there are no filters and yes I have handled parameter sniffing but that did not help much.I am displaying the data returned by data set in a simple table(tablix) control without any grouping or sorting.The time taken to load the report is increasing with the increase in number of rows being returned by the stored procedure.Also,I dO not have any paging in my report as per the requirement.all the records are displayed in single page. The interactive page size is set to 0What could be the possible reason for the report server to take long time to display the report.Wed, 11 Jan 2012 09:51:18 GMTaparna333http://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxHi Cheryl Hanlon,It's quite terrific point that you have pointed out. It literally saved me from a disaster today. I was facing the same issue and by replacing parameters with local variables, it rendered my data within 5 seconds. Awesome.Now I have to go back to all my SPs to get this implemented.Thanks so much for this point.Vaidy MohanThu, 08 Dec 2011 00:20:35 GMTVaidy Mohanhttp://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxHi, I have actually been dealing with the same issue over the past 24 hours and this is how i resolved it.Firstly I added some proper indexes on the database, I was still having speed issues. Then i disabled parameter sniffing on the query.Basically ssrs sniffs the parameter coming into a report and can sometimes produce a convulted execution plan based on those parameters. To disable parameter sniffing you should create a local variable within your sproc or sql statement and assign the parameter to that instead.I.E If you passed a variable from your report called @customerId and your query looked like this select * from customer where id = @customerId Change it to declare @localCustomerId varchar(10)set @localCustomerId = @customerIdselect * from customer where id = @localCustomerId This will produce a much more efficient execution plan if parameter sniffing is the problem. Also if you are using a sproc try setting nocount onHope this helps !CherylWed, 14 Apr 2010 02:38:28 GMTCheryl Hanlonhttp://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxhas there any resolution on this issue? We are having the same issues with rendering reports from a sql querie on IIS. Managers are complaining that it slow as a turtle gettng just a month's worth of data.Tue, 13 Apr 2010 01:19:04 GMTsql_noobhttp://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxI would also look at your database and make sure the statistics are updated and that you have proper indexes setup. Perform the heavy lifting in the database and not in SSRS to get at your aggregate values. The stored procedure should definitely be an improvement over the embedded T-SQL.Fri, 17 Apr 2009 11:59:25 GMTdenglishbihttp://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxBut how many rows are being returned and it sounds like the aggregation is being performed in the table with grouping, so that will add some overhead. Look at performing the aggregation ahead of time if you don't need the detail.You are running SQL Server, IIS, Report Server, ... and what else on the server. It is just not Report Server. Monitor the resources on the server and run a Profiler trace on SQL Server to see what is going on. Because of the volume of data you might need to look at making some modifications to the packet size possibly in IIS or test running the report with a smaller dataset.Fri, 17 Apr 2009 11:56:34 GMTdenglishbihttp://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxIts just the table having rows.Returned rows are 4 or 5 aggregated rows based on certain conditions.But i'm running only reportserver, no other applications are running on server.As of now i'm the only one accessing reports, soon users will access the application.But if the no of hits are more, the report performance will be even worse right?Regards,RamuFri, 17 Apr 2009 11:48:05 GMTramu.valletihttp://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxThe big difference would be that everything is running on the single server and you are going through IIS with the Report Server. I would monitor the resources on the server. Are you really returning 30 million rows of data for the report or is that just the table that you are querying?Fri, 17 Apr 2009 05:14:48 GMTdenglishbihttp://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxI've checked the Execution log, there most of the time it is taking for data retrieval only.Report is fast in BIDS and its very slow in reportserver.I'm checking in both the places one after the other, always BIDS is faster and reportserver is slower.I tried changing to a stored procedure, its even more worse(taking almost double the time).If it is resource issues, why it is taking long time in reportserver not in BIDS.And i've almost 30,000,000 rows in the table.Is there anything wrong with IIS?Thanks,RamuFri, 17 Apr 2009 04:59:31 GMTramu.valletihttp://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxIf everything is on the same server then most likely not a network related issue. How large is the dataset? Does the report generate quickly in BIDS, but just slow on the actual Report Server? How much memory do you have on the server and have you monitored the resources on the server (CPU, Memory, etc.) while you are running the report. Since everything is running on the same server and maybe there is even more running on it you are experiencing some resource issues. What else is installed on the server and are you experiencing issues with other reports? Have you tried moving the logic to a stored procedure and test it that way?If you ever need to use a network trace utility you can use [url=http://www.wireshark.org/][u]Wireshark[/u][/url] and you can also utilize the traceroute by doing a [b]tracert <servername>[/b] or [b]tracert <ip address>[/b] from your cmd prompt.[b]TRACEROUTE[/b]Traceroute tracks the path that a packet takes from your computer to a destination address. A traceroute also shows how many times your packets are being rebroadcast by other servers until it gets to the final destination. [url=http://help.expedient.com/general/ping_traceroute.shtml][u]Ping and Traceroute[/u][/url]Fri, 17 Apr 2009 04:40:55 GMTdenglishbihttp://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxCheck the executionlog table to determine if the time is spent on data retrieval or processing or rendering. If your resultset is very large, eventhough the query results may be returned faster, the processing time of the report will be high.Fri, 17 Apr 2009 02:40:55 GMTArun Sathianathanhttp://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxThanks a lot for the immediate response.Here are the Details of my SQLServer,Edition Enterprise EditionProductLevel SP3ProductVersion 9.00.4035.00ResourceVersion 9.00.4035Yes, all are running on the same server.Yes, i can see the result of the query in 10 to 20 sec in BIDS as well as Management studio.I'm just running this in Report Manager (HTML) or Reportserver.It is a normal chart report and its just using embedded T-SQL.I've not used any network trace utility, can u suggest me how to use that?Hope these details will help you to understand the problem.Please let me know if you have any suggestions.Thanks,RamuFri, 17 Apr 2009 00:15:50 GMTramu.valletihttp://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxProbably need some more information in regards to this to offer more advice. Are you using all SQL Server 2005 components? What version (build) are all of the components? Are they running on the same server or in a distributed environment? How does the report run locally from BIDS? Are you able to run the dataset query without issues in BIDS, but the report generation is taking longer? Are you trying to output this to a particular format or just running this in Report Manager (HTML)? Is there anything special going on with the report like Images embedded? Have you reviewed the Profiler trace when executing this against the SQL Server? Are you using a stored procedure as the data reference or just using embedded T-SQL? Have you run a network trace utility or reviewed the traceroute to see what is going on as far as your network is concerned?Thu, 16 Apr 2009 15:22:46 GMTdenglishbihttp://www.sqlservercentral.com/Forums/Topic698464-147-1.aspxHi all,I've a query which is taking 10 to 20 sec when i execute it from SSMS.But when i execute the Report which is developed using the same sql query from report server, is taking 2 min to process.What could be the possible reason for the report server to take long time to execute the same query and why it is not taking that much time in management studio?Please suggest me how to improve the performance of report server..!Regards,RamuThu, 16 Apr 2009 08:03:45 GMTramu.valleti