SSRS Report very slow with added parameters

Posted on 2014-03-09
Last Modified: 2014-03-21
I have created and SSRS report which was working great and quite responsive.  I've added two multi-value parameters and specified them as filter criteria on the dataset and performance has tanked.

Selecting all the parameters  is set as the default which I had hoped would be as fast as the original non-parameterised version but no luck.
Question by:canuckconsulting
  • 4
  • 3
LVL 37

Expert Comment

ID: 39917059
First you need to figure out what part is slow.  How long does the parameterized query take when executed in Management Studio?

Author Comment

ID: 39924671
Turned out it only seem to be  a problem in the preview!  When I deployed it the thing ran great.

Any thoughts on why preview is so unworkable compared to production?
LVL 37

Expert Comment

ID: 39925863
There can be a lot of reasons.  Different hardware, different data, ...  But as long as your query is fast in SSMS and you're not returning hundreds of thousands of records to the report, the report itself should be fast too, even preview...
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


Author Comment

ID: 39932409
Yes, this is what is frustrating me.  These are not large record sets and the report takes just a few seconds to run when deployed to by DB.

As I am developing on my laptop everything is the same!  Identical hardware hitting the same data source which consists of less than 2000 rows.  Any thoughts on how to work out what is wrong with my preview?
LVL 37

Accepted Solution

ValentinoV earned 500 total points
ID: 39939002
No idea to be honest, so far I've always encountered the opposite!  Do you have the latest service packs for both Visual Studio and SQL Server installed?  If not, that might be good to try.  It's always interesting to have the latest version of those installed anyway...

Author Comment

ID: 39945924
That could be my problem...the last sql sp is not installed and I am having trouble getting it installed.  I'll persevere with that and let you know if it resolves the issue.

Author Closing Comment

ID: 39946292
ok...installed sql server 2012 sp and performance seems to be better. I'll give this a try and open a new issue if it recurs.

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL: launch actions one before the other 10 23
sql 2014,  lock limit 5 32
Simple SQL query from two tables 13 54
SQL - Copy data from one database to another 6 19
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question