Solved

SSRS Report very slow with added parameters

Posted on 2014-03-09
7
1,359 Views
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.
0
Comment
Question by:canuckconsulting
  • 4
  • 3
7 Comments
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
First you need to figure out what part is slow.  How long does the parameterized query take when executed in Management Studio?
0
 

Author Comment

by:canuckconsulting
Comment Utility
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?
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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...
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:canuckconsulting
Comment Utility
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?
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
Comment Utility
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...
0
 

Author Comment

by:canuckconsulting
Comment Utility
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.
0
 

Author Closing Comment

by:canuckconsulting
Comment Utility
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now