Episode 148
| October 03, 2018
| 40 minutes
High Performance SSRS
SSRS is great — it comes with SQL Server, provides quite a bit of functionality, but sometimes doesn’t always perform well. In this episode we welcome back Bert Wagner as he chats with us about some of the ways he has improved his under-performing reports. Our conversation focuses on SSRS itself — we are going to assume you can tune the queries in SSMS from previous episodes or the interwebs. What do you think of our list? Did we miss one? Let us know!
For .jpg and .png compression: TinyPNG
Episode Quotes
“Ultimately, 99% of the time with SSRS, it all comes down to rendering. Any way you can minimize how much work SSRS has to do, you will benefit in performance.”
“If there’s certain reports where you know a user doesn’t really care if they’re getting an html version of the report, you can specify a report to render directly to Excel by passing in a parameter in the url.”
“All of the different properties on your report, on different cells, on different texts that you’re rendering, you’ll see a lot of auto-type settings…setting those properties can make a pretty big impact, especially on reports where you are using lots of design elements.”
Listen to Learn
00:40 Intro
01:10 Compañero Shout-Outs
01:33 SQL Trail
01:43 SQL Server in the News
02:15 Intro to the guest and topic
04:39 How to find out where your reports are spending their time
06:04 The difference between processing and rendering
09:13 Some SSRS-specific solutions
10:40 Eugene’s defense of Power Query
13:13 Discussion on browsers and why rendering in html isn’t preferable
17:34 What else can you do to minimize rendering and loading times?
20:49 SSRS implements snapshots and caching
23:14 Pagination can help with rendering speeds, as well
26:05 Using sub-reports may not be a good idea, with a caveat
28:33 Eugene’s horror story about putting a sub-report in a Tablix
29:37 You may have to rethink how you structure your data, a couple of hybrid solutions & last thoughts
33:50 SQL Family Questions
39:01 Closing Thoughts
Credits
Music for SQL Server in the News by Mansardian
Our Guest
Bert Wagner
Bert Wagner is a BI developer who loves optimizing SSRS reports and SQL Server for fast performance. He shares his SQL learnings every week on his blog and YouTube channel at https://bertwagner.com.
Meet the Hosts
Carlos Chacon
With more than 10 years of working with SQL Server, Carlos helps businesses ensure their SQL Server environments meet their users’ expectations. He can provide insights on performance, migrations, and disaster recovery. He is also active in the SQL Server community and regularly speaks at user group meetings and conferences. He helps support the free database monitoring tool found at databasehealth.com and provides training through SQL Trail events.
Eugene Meidinger
Eugene works as an independent BI consultant and Pluralsight author, specializing in Power BI and the Azure Data Platform. He has been working with data for over 8 years and speaks regularly at user groups and conferences. He also helps run the GroupBy online conference.
Kevin Feasel
Kevin is a Microsoft Data Platform MVP and proprietor of Catallaxy Services, LLC, where he specializes in T-SQL development, machine learning, and pulling rabbits out of hats on demand. He is the lead contributor to Curated SQL, president of the Triangle Area SQL Server Users Group, and author of the books PolyBase Revealed (Apress, 2020) and Finding Ghosts in Your Data: Anomaly Detection Techniques with Examples in Python (Apress, 2022). A resident of Durham, North Carolina, he can be found cycling the trails along the triangle whenever the weather's nice enough.
Want to Submit Some Feedback?
Did we miss something or not quite get it right? Want to be a guest or suggest a guest/topic for the podcast?