Hi all, I have a SSRS Report that I call through a Report Action in a Cube. It is running Sharepoint Integrated mode (which i doubt is what causes my problem). The report pulls from a relational dataset with 4 parameters. When i run the query from SSRS it takes < 1 sec. When i run the report from SSRS through a webbrowser (passing parameters) it takes serveral minutes. I have tried serveral suggestions from posts in this forum to try and solve the issue - but nothing have worked so far. (se comments in the query below). The call to the report looks like the following: http://dwbitest01/ReportServer/Pages/ReportViewer.aspx?http://veritastest/Abonnement/SSRS_Rapporter/AboNummerUdtraek.rdl&omfatning_kode=12345&status=Bero&produkt_kode=EKT&dato=25-01-2011&rs:Command=Render&rs:Renderer=HTML5 The stored procedure that I use in the dataset looks like this: CREATE PROCEDURE [dbo].[AboNummer] @omfatning_kode nvarchar(10) = '1234567' ,@status nvarchar(25) = 'Bero', @produkt_kode nvarchar(25) = 'JP', @dato nvarchar(10) = '01-01-2011' /* Default values added for performanceimprovement */ AS BEGIN SET NOCOUNT ON/* Added for performanceimprovement. */ /* Declare local variables for performanceimprovement to avoid "Parameter Sniffing". */ declare @OMF nvarchar(10) declare @STA nvarchar(25) declare @PRO nvarchar(25) declare @DAT nvarchar(10) set @OMF = @omfatning_kode set @STA = @status set @PRO = @produkt_kode set @DAT = @dato SELECT dm_abo_dim_abonnement.Abonnementsnummer, dm_abo_dim_abonnement.fornavn, dm_abo_dim_abonnement.efternavn, dm_abo_dim_abonnement.abonnements_status, dm_all_dim_tid.aar, dm_all_dim_tid.uge_nr, dm_abo_dim_produkt.produkt_navn, dm_abo_dim_prisliste.omfatning_kode, dm_abo_dim_prisliste.betalingsperiode_kode, dm_abo_fct_abonnement_dag.antal, dm_abo_dim_abonnement.koen_kode, dm_abo_dim_abonnement.koen_tekst, dm_abo_fct_abonnement_dag.status, dm_abo_dim_prisliste.produkt_kode, dm_all_dim_tid.dato FROM dm_abo_fct_abonnement_dag INNER JOIN dm_abo_dim_abonnement ON dm_abo_fct_abonnement_dag.dim_abonnement_id = dm_abo_dim_abonnement.dim_abonnement_id INNER JOIN dm_abo_dim_prisliste ON dm_abo_fct_abonnement_dag.dim_prisliste_id = dm_abo_dim_prisliste.dim_prisliste_id INNER JOIN dm_abo_dim_produkt ON dm_abo_fct_abonnement_dag.dim_produkt_id = dm_abo_dim_produkt.dim_produkt_id INNER JOIN dm_all_dim_tid ON dm_abo_fct_abonnement_dag.dim_dato_id = dm_all_dim_tid.Dim_tid_id WHERE (dm_abo_dim_prisliste.omfatning_kode = @OMF) AND (dm_abo_fct_abonnement_dag.status = @STA) AND (dm_abo_dim_prisliste.produkt_kode = @PRO) AND (dm_all_dim_tid.Dim_tid_id = CAST(SUBSTRING(@DAT, 7, 4) + SUBSTRING(@DAT, 4, 2) + SUBSTRING(@DAT, 1, 2) AS int)) OPTION (TABLE HINT(dm_abo_fct_abonnement_dag, INDEX (dm_abo_fct_abonnement_dag_QueryIDX))) -- Added for performanceimprovement END Any ideas? Thanks.... Jesper W

I see you dictate to SQL Server to use a specific index, any reason? Could be parameter sniffing... See some useful options http://pratchev.blogspot.com/2007/08/parameter-sniffing.htmlBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Hi Uri, Thanks for your reply. The dictation of a specific index was a test for trying to solve the problem. It makes no difference if i use it or not - but i included it in my example to let you see what i had allready been trying. I thougt i allready avoided "Parameter Sniffing" by declaring local variables (se begining of code). But maybe i am doing it wrong? / Jesper W

Hi JesperWittendorff, From your description, the report takes more time to run in integrated mode then in native mode, right? Please correct me if my understanding is wrong. Actually, reports render more slowly in integrated mode than in native mode. Please refer to the following reasons and hope it can help you. 1. The bulk of the time spent during report rendering is due to the number of HTTP calls between the Web Front End (WFE) and the Reporting Services servers. Integrated Mode results in more calls than Native Mode, which results in more overhead. 2. This SQLCAT article concludes that, although there’s a 400 millisecond performance penalty expected in SP Integrated Mode, that SP Integrated Mode with SQL Server 2008 R2 ultimately outperforms Native Mode. We found the performance penalty to be far more than a 1/2 second delay in our situation and therefore could not risk performance on existing and future client implementations. The best solution at this time was to revert to Native Mode. After Microsoft reviewed HTTP traces, IIS logs, FREB analysis, SharePoint logs, PerfMon data, Profiler traces, and network analysis, they concluded the bulk of the time is being spent in the Report Viewer control. For more information about the topic, please refer to the following article, what’s up with the Slow SSRS R2 Rendering in SharePoint 2010 Integrated Mode: http://www.sqlchick.com/entries/2011/1/25/whats-up-with-the-slow-ssrs-r2-rendering-in-sharepoint-2010.html IF you have any question, please feel free to ask. Thanks, Eileen

Hi All, Thank you very much for your reply's. Eileen: You are right - the report takes slightly longer in integrated mode. This is however not a big issue here so we are continuing in Integegrated mode. I found out after doing a trace that i had an unused dataset in the reoport selecting distinct Status from the Fact table. Eventhough it was not used in the report - it was still running on report open. After deleting this dataset from the report the perfomance is as expected. Thanks. Jesper W