Home > Work > SSRS Web Part Performance 2008 R2 vs. 2012 Initial Results #SharePoint #SP2010 #in

SSRS Web Part Performance 2008 R2 vs. 2012 Initial Results #SharePoint #SP2010 #in

On one of my client projects, we heavily use SQL Server Reporting Services in SharePoint integration mode.

The benefits in comparison to Native mode are primarily around the RS SharePoint Add-in which allows us to use query string (URL) filter web parts to pass parameters to reports and create powerful dynamic dashboards as well as using SharePoint to permission and store the reports.

There is an issue however.  Performance in this mode is not amazing, as detailed here:

Now that SQL Server 2012 has gone to RTM, we can start to get a measure of how well the new web part performs and I am pleased to say that the improvements appear to be great so far…

The Scenario

  • SharePoint Version: 2010 SP1 – December 2011 CU with Project Server
  • DB Server: SQL Server 2008 R2 SP1
  • SSRS Version: 2008 R2 SP1
  • SSRS Add-in: 2012 (Denali) – RTM
  • Server Architecture: 1 x WFE / App Server (Virtual) + 1 x SQL Server (Virtual)

NB: All testing has been carried out on our DEV server, please always try out new software on a development / test environment first to ensure the benefits and to safe guard production from issues.

The Reports:

Table based reports rolling up Project Server and SharePoint data based on parameters provided by the end user (Cannot show due to client confidentiality I’m afraid).

The Results:

Now this is just a teaser really since we will be doing full testing on our master development environment, but for now…

Project Portfolio Table Report

  • Number of returns items: 17 rows
  • Number of parameters: 1 (Portfolio ID)

SSRS2008R2vs2012

NB: All time taken in seconds using Fiddler2

SSRS2008R2vs2012_ProjectPortfolioWholePage_Graph SSRS2008R2vs2012_ProjectPortfolioSSRS_Graph
SSRS2008R2vs2012_ProjectPortfolioWholePage_Pie SSRS2008R2vs2012_ProjectPortfolioSSRS_Pie

As you can see, we are seeing significant time increases with the new web part and within my client we have the go ahead to carry out further testing.

More useful links:

Advertisements
  1. Neil King
    May 1, 2012 at 09:14

    Excellent information Giles, really good post

  2. MDeveloper
    May 22, 2012 at 13:53

    I have SSRS Report Viewer webpart in SharePoint native mode. I want to pass two parameters to Report Viewer webpart via query string. I added Query String URL filter webpart on my webpart page and when I try to connect (pass filter vaule) to report viewer webpart it doesn’t pass anything but shows that its connected. 😦

    Please advise how to pass URL parameters to report viewer webpart in MOSS2007 sharepoint native mode.

    Thanks,
    Maulee

    • May 28, 2012 at 06:46

      Hi Maulee,

      Apologies for the delay in my reply, the comment got caught in the spam filter.

      Unfortunately the only way to pass parameters to the SSRS Report Viewer Web Part is to run SSRS in integration mode. When SSRS is in native mode, the only parameter that can be passed is the report URL.

      Hopefully that helps.

      Kind Regards

      Giles

  3. Shalini
    June 15, 2012 at 14:48

    Hi Giles

    I have a question regarding report viewer web parts.

    I have added two SSRS reports (report viewer web parts) on the web page. I need to have these two reports linked. The first (main report) being a USA Map and the second(subreport) being when a state is selected from the main report it displays a table with some information for that state selected. I am not knowing how to link up these two reports. The Connections option when clicked has two other options Get Report Parameters from and Get report definition from which are grayed out. Can you please help me find solution for this.

    Thanks in advance!!

    Shalini

    • June 15, 2012 at 15:46

      Hi Shalini

      I believe this happens when SSRS is running in Native mode. These options are only available in SharePoint Integration Mode.

      Can you let me know which mode you are running in?

      Kind Regards

      Giles

  4. Shalini
    June 15, 2012 at 22:01

    Hi Giles,

    Thanks for your reply!!
    Its running in Native Mode. I have read some blog : http://www.dotnetcurry.com/ShowArticle.aspx?ID=678
    Do I have to implement something like this??

    Shalini

    • June 16, 2012 at 06:53

      Hi Shalini,

      If you are unable to switch modes in Reporting Services then something like that or using a content editor web part with html iframes and javascript to read parameters from the query string would be your only choice.

      Details of parameter options are here:

      Usage:
      http://blogs.msdn.com/b/alross/archive/2010/11/27/correct-format-for-sending-parameters-to-ssrs-via-query-string.aspx

      Command Options:
      http://technet.microsoft.com/en-us/library/ms152835.aspx

      I hope that helps and let me know how you decide to resolve.

      Kind regards

      Giles

      • Shalini
        June 19, 2012 at 17:20

        Hi Giles,

        I was asked to implement an other way. In one report, I should be able to display the map and when clicked on any state, in the same report I should be able to show the other detailed data which could either be a subreport or a table inserted.
        Does this makes sense?

        Can you assist if I am thinking correct?

        Thanks,
        Shalini

  1. May 2, 2012 at 15:36
  2. May 24, 2012 at 07:45
  3. August 20, 2013 at 21:09

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: