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!

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

For .jpg and .png compression: TinyPNG

About 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.

Credits

Music for SQL Server in the News by Mansardian

*Untranscribed introduction*

Carlos:             Compañeros, welcome to another edition of the SQL Data Partners Podcast. My name is Carlos L Chacon, your host. This is Episode 148, and I’m glad you’re here, compañeros. we have another very interesting conversation for you today. We’re joined by Bert Wagner out of Ohio. Bert’s going to be talking to us about High Performance in SSRS, so he shares a couple of insights, and as always, I am joined by Kevin Feasel and Eugene Meidinger. We’re anxious to get into this conversation.

We do have a couple of compañero shout-outs. The first to Matthew Compton, Jared Poche, Michael Alexander, Christopher Wolff and Monica Rathbun for following up with me after the tornados actually came through Richmond. Luckily, I was unaffected, but they were very close to my house and tore up some buildings and things. But I am okay, compañeros, and as they say, the show must go on.

A little SQL Trail news: it’s in two weeks! We hope to see you there! October 10^th. We’re going to have it whether you’re there or not, so we hope you come.

Okay, a little SQL Server in the News! We’ve been kind of neglecting this, as of late, mostly because it’s been a little bit quiet. There have been some updates, I guess, that we haven’t passed along, but now that conference season is in session, they have announced SQL Server 2019 will be available. I don’t know whether that they’ve given a date, yet, but they did announce that that would be coming. It is actually available for download, so they’ve made release candidates available already. So, that’s interesting, there are a couple of things that we’ll be going over in future episodes about what’s coming down the pike.

Today’s show notes are going to be available at sqldatapartners.com/ssrs or at sqldatapartners.com/148. With that, let’s go ahead and get into the conversation.

Carlos:             Bert, welcome to the podcast again.

Bert:                Hey, thanks for having me back, Carlos.

Carlos:             We appreciate you taking some time to chat with us. Your other episode was really great, and so we appreciate you taking some more time to chat with us about, ultimately,SQL Server Reporting Services performance. And gosh, I feel like this is kind of a wide-open topic.

Bert:                Oh yeah, you could go lots of directions with it.

Carlos:             Yeah, that’s right. Is there anything else we need to say besides “upgrade to the latest version and use Chrome”?

Bert:                That’ll solve all your problems.

Carlos:             Oh, so you’re saying there’s a couple more things we could get into, here?

Bert:                Yeah, there’s a few we could look at.

Carlos:             Okay. So maybe, to help frame the conversation, there are a couple of layers, here. So, give us the nickel tour so that we set the stage for the different pieces and how they play into the Reporting Services. Does that makes sense?

Bert:                Sure, yeah. When I look at SSRS, performance tuning, or rather just building efficient reports to begin with, I really break it out into three big sections. The first is your SQL Server side, all your queries and indexing and all the basic things you would think about when discussing query performance. Those are definitely their own category and honestly, I usually start there, because there is usually a lot of low-hanging fruit of fixing poor running queries. Then there’s the SSRS side of things, and ultimately, 99% of the time with SSRS, it all comes down to rendering. Trying to minimize how much work SQL Server Reporting Services has to do in displaying your report data on the screen to the user. Any way you can minimize how much work SSRS has to do, you will benefit in performance. Then finally, there’s always edge cases that are maybe not the best practices, but in certain scenarios, where you need to get every ounce of performance out of a report, there’s certain things you can do that will get you there. Those are kind of the high level three steps that I always go through when trying to improve performance of my SSRS reports.

Kevin:              Even before that, where would I go to find out which of my reports is performing poorly?

Bert:                Well, that’s a great question, Kevin.

Kevin:              Totally not a leading question.

Bert:                Yeah, you want to figure out where to focus, and the best thing to do is SSRS, when you install it, has an option to log all of your performance metrics into a database, and in particular, there’s a view, I think it used to be just called the ExecutionLog, and at some point there was an ExecutionLog2, and I think now they’re up to 3. Basically, you just pick whichever view in the SSRS reporting database is the highest number one, and that’ll give you a breakdown of where your report is spending time working. Specifically, I think there’s three columns there: one that shows you how much time it takes to get the data from the database, one that shows you how much time is being spent by Reporting Services to actually process that data in the report. Then the final column of data there is showing you how much time is spent rendering that data onto the screen, so, if you go and look at that data in SQL Server, it makes it very easy to be able to query it, find your long-running reports, find what’s running well, what’s not running well, and then when something’s not running well, should you be focusing on the SQL side of the house or should you be focusing on the reporting side of the house?

Kevin:              Okay, yeah, it is ExecutionLog3. So, you mentioned processing and rendering times. I get the data retrieval, that’s the amount of time that it took my SQL query to run. What is the difference, then, between the processing side and the rendering side?

Bert:                Yeah, the way to think about it is that your time processing is all of the work that SSRS has to do to get your data ready to display, so things like sorting the data in a Tablix or filtering out rows of data in SSRS, those are considered processing. Those are actually some of the easiest things to reduce, because a lot of those things you can just take care of in your SQL query. Assuming you have a fast SQL Server and you want to be able to sort your data there, be able to utilize indexes, which are going to return data already sorted or things like that are all, filtering that data will be faster, most likely on the SQL Server side of things, so that’s what the processing eliminates.

Kevin:              Okay, and the rendering is just the amount of time that it takes to spit out the html or the pdf document or whatever?

Bert:                You got it.

Kevin:              Does that also include things like conditional formatting, or is that considered processing?

Bert:                That’s a great question. I have no idea, Kevin.

Kevin:              Neither do I.

Bert:                We’d have to test it out to see exactly where that goes.

Carlos:             Sounds like something for the show notes. Stay tuned! I’m curious, those are time stamps in there. Are you just basically just using DateDiffs?

Bert:                They’re second counts, yeah.

Carlos:             Oh, they’re actually second counts, okay.

Bert:                Seconds or milliseconds, yep.

Carlos:             I guess I feel like it’s been a little while since I’ve looked at that.

Kevin:              Yeah, it’s nice and easy to sum up so that if you have a set of reports that are run frequently, you can look to see the distribution of times to see if there’s one which is skewed heavily, maybe it’s a parameter problem. Or if they all tend to be about the same length, the same frequency, in terms of data retrieval time or processing time, then you know that a change that you make is probably going to affect the report every single time someone calls it. Whereas, if you have a highly skewed distribution, you may be helping out some of the cases, but might not be making as much of a benefit as you think.

Bert:                Yeah, that’s a really good point. There’s just plenty of logging data in there, like the time of day these reports are running, who the users are, so if you’re having to help maintain a server with lots of different users and you might not necessarily be the author of those reports, you can quickly figure out who knows what they’re doing and who maybe needs a little bit of help to get the reports running faster. Or if there’s reports running heavily at 8am, maybe there’s certain things you can do to reduce the load on those, versus reports that are running all day long.

Carlos:             Yeah, this can also be handy, because not so much from a performance perspective, but just to know who’s running what and how frequently. Particularly when you get into those environments where all of a sudden, you have reporting sprawl. It can help you understand, “well, you know what? This report hasn’t been used in the last 6 months. Maybe it’s okay for us to retire that.” Or “we don’t need to move it” or whatever else.

Bert:                Yeah, definitely.

Carlos:             Now, I guess I’m curious, we’ve talked about things you can take a peek in the database, I think for a lot of times, we’re also talking about stored procedures, and then we say, that’s in the database and you can start your tuning there. There’s lots of different options, I think. We can go a million different ways with some of that. But what about some of the SSRS specific options?

Bert:                Sure. There’s lots of different solutions inside SSRS that you can do directly. The first, and this is kind of a SQL Server-related one, but I see it so often, it’s hard not to bring it up. If you’re doing a lot of reporting, if you create a reporting database, a reporting set of tables for your data, that can make a huge difference, because not only are you reducing the load in your queries, but whatever additional work your reports have to do, if your data’s already in the correct format. You can kind of offload that work to some non-peak times, run it at night or something, prepare it so it’s easier to consume by a report, that definitely makes a huge difference.

Carlos:             Now you’re saying that we actually have to take a look at our data before we start reporting on it?

Bert:                Sometimes.

Carlos:             Yeah, and I think, spoiler alert, for those of you who are looking to get into PowerBI, although today is ultimately talking about SSRS, that problem doesn’t go away in the new reporting environment.

Bert:                No, you always, always want to reduce as much of that data, get it formatted, as close to the final output as you can. It’ll definitely make your life easier, long-term.

Eugene:           As the resident PowerBI expert on the podcast, I do want to say one thing in its defense, which would be a really cool feature for SSRS is query folding. One of the things that’s really nice is if you’re doing some data manipulation with Power Query, there’s a lot of things that it’s smart enough to push back to the SQL, even if you didn’t specify that.

Carlos:             Oh, interesting.

Eugene:           I mean, you still absolutely have to be thoughtful about your data, but let’s say in Power Query, if you’re using just the GUI, and you filter something, unless you’ve changed the privacy settings for your data sources, unlike SSRS, it’ll actually modify the native query that it’s running against the database. As long as you went through the GUI to pick which tables you wanted originally or which views you wanted. If you wrote your own SQL, it’s just going to trust you, but if you went through the GUI and said, “I want these tables, I want a filter on this, and I want to combine these columns” or whatever, there’s a lot of cases where it’s smart enough to push that back to SQL. The most interesting case is a demo I’ve done for the different privacy settings you can set, is if you do a JOIN, if you do a MERGE between an Excel file with like a single value and SQL, it’ll push that single value from Excel into the SQL query, which, if that was, instead, someone’s social security number or credit card number, that’s where you want to set the privacy settings so that stuff doesn’t leak. But it’s interesting, some of the performance pieces they’ve put into the Power Query piece.

Carlos:             Yeah, that sounds like a topic for a different episode.

Eugene:           Yeah, no, absolutely.

Carlos:             Now, I am curious, so, I feel like Tablix was a newer edition, but I don’t feel like it was there in the older versions of Reporting Services. But at the same time, I can’t think if there has been that many changes.

Kevin:              Yeah, it’s been there since 2008. 2005 they had the table and matrixes separate and then the Tablix as your combination in 2008 and basically didn’t change at all until 2016.

Carlos:             Gotcha. So then, are there any differences from that perspective, I guess, like you mentioned, if you’re using like 2008 to 2014, not too much has changed. Do you find that there are any differences going between table and Tablix or any of the other data set or Any of the other pieces or components in SSRS?

Bert:                For me, personally, no, not too much. I started working with SSRS 2008, so I’ve never touched a table or matrix component. It’s always been the Tablix. As for newer things, it’s nice having additional graphing/charting options in the newer versions, but it’s been relatively the same for as long as I’ve been using it.

Carlos:             Okay. Now, we did mention earlier, just using Chrome, but one of the things you talked about was not rendering as html. What does that mean?

Bert:                Yeah, by default, when you run your SSRS report, and it opens up in your browser, whether Chrome or Edge or IE9 or something old that maybe you’re forced to use at work, it’s rendering–

Carlos:             We should clarify, even in 2014, the default, you’re kind of stuck to Internet Explorer, right?

Bert:                Yeah, I don’t think it was until the 2016 that they officially supported Chrome. I think they officially support it.

Carlos:             Right.

Eugene:           It kind of worked in 2012 and 2014 and the reason I know that is because it definitely did not work in 2008. Because back when I was supporting 2008 R2, people would try to access the reports in Chrome, and they would run, but nothing would be rendered to the screen, because of just CSS shenanigans, but 2014, I know when I was managing that, that worked fine in Chrome. It probably wasn’t officially supported, but it worked.

Bert:                But regardless of what browser you use, if you’re rendering to html, that’s kind of the easiest way, maybe, for someone to consumer report. They click on a link and it just opens up for them. They don’t need any software installed or anything like that. But it is data heavy, like in terms of the html that SSRS is generating, there’s lots of html code that it needs to generate specifically, like when you’re displaying a table of data, it’s just a lot of data. So, what you can do instead, is if there’s certain reports where you know a user doesn’t really care if they’re getting an html version of the report or maybe something like Excel, you can specify a report to render directly to Excel by passing in a parameter in the url. What that will do is, especially in the newer versions, if you’re using the .xlsx file format where you get a lot of compression, a lot of savings there, and SSRS renders those Excel files significantly faster than it does in rendering the html to the page. So if your users are okay with it, or especially if your users are opening up the html version of the report, and then exporting to Excel or to a PDF, anyway, why make them go through a two-step process when you can give them the data in the format that they want, right out of the gate?

Carlos:             My question or my thought would be parameters, so what happens when you have a report that has several parameters that they could choose from? You then have to create a link for each of the parameter options, right?

Bert:                Well, it depends. There’s two ways of passing parameters around in SSRS. You can do it through a url or you can do it via post just through the body of the report itself. So, if you are passing your parameters around through a url, it makes it really easy to just tack on an extra format parameter to export directly to Excel. If you’re doing it other ways, you kind of have to do some things where maybe you save default sets of parameters in a table, and so you generate some links for them to click on to show those reports and have them rendered directly to Excel.

Carlos:             Sure, and I can see, particularly in environments where Reporting Services might be new, because believe it or not, there are still some places where Reporting Services is being introduced for the first time, that that conversion to Excel might be preferable. Because that’s exactly what they want to do. They want to get the data and then play around with it in Excel or do whatever with it.

Bert:                I mean, any time I’m building a report that’s rendering more than just a few high-level dashboard-type pages of data, you don’t always want to be creating reports that are 100 pages long if no one’s looking at that data. But if you’re generating reports where that data’s important, there’s no question, going directly to Excel is the way to go.

Kevin:              Yeah, and as far as parameterization goes, you could also throw a web front-end in front of what generates those links to Reporting Services. I actually had to work on a project like that a while ago, where there were a lot of different reports, and it was easier for users to have us generate the forms and have them select elements from the forms that then go post to some more generic Reporting Services reports.

Carlos:             Gotcha. Yeah, you had to use a little creativity there, then.

Kevin:              It was a scary project. But it worked.

Carlos:             Well, there you go.

Kevin:              So Bert, when it comes to the reports, themselves, what things can we do, aside from, say, rendering to Excel, to minimize that rendering time or that time that it takes for the report to actually load?

Bert:                One of the biggest things is, when you drag and drop a picture into your report, maybe it’s a company logo or something like that, and a designer sent you a logo that’s meant for a billboard, it’s like 10,000 pixels wide, and then you throw it in SSRS and then click and drag a corner to make it this tiny little thumbnail-size logo in the top corner, SSRS won’t compress or resize that image, by default. So, if that original image was like 5MB or 10MB or something really high res, if you don’t resize it or compress it, it’s going to be loading that 5, 10MB image every single time that report runs. So, the biggest bang for your buck, if you use images, is to go through and compress them down.

Carlos:             Just the good old-fashioned way, right?

Bert:                Yeah, it just depends what your tools are. When I’m limited, I put the image in PowerPoint and then resize it, compress it, save it. That works. You could use Paint if you want to be really primitive. If you have other tools that’ll maybe better optimize the compression, that’s good, too. It really comes down to how much do you value having fast performance and how much time do you want to save?

Kevin:              Yeah, so on that, there is a site called TinyPNG where it will take .jpg or .png files and compress them down. It’s really good at compression without much artifacting, so I’ll make sure that’s in the show notes, but it’s tinypng.com and it is a free service.

Carlos:             There you go.

Bert:                That’s great. Some other things you could do with your reports, besides just the images is, basically all of the different properties on your report, on different cells, on different texts that you’re rendering, if you open up the properties for those settings, you’ll see a lot of auto-type settings. Where, for example, you can have a cell be auto growth to grow it, or you can have an image be auto sized to fit inside a cell or text alignment can just be auto instead of specifying left or right. Any time that you leave those settings on their default auto setting is you’re creating extra work for SSRS to have to calculate what is that report going to look like. If instead, you just are really explicit with saying, “okay, my cell is going to be this wide, this tall and I don’t want you to grow it at runtime,” that’s one less calculation SSRS has to make. Just setting those properties can make a pretty big impact, too, especially on reports where you are using lots of design elements.

Kevin:              One other thing that I would throw in, this is more of a processing time, rather than rendering time, but minimize the number of formulas you have, and even on the rendering side, the conditional formatting. The more complex that stuff is, the longer that it takes, and it happens on a per row basis, as I recall.

Bert:                Ah, so you did know which category that falls into, huh?

Carlos:             Yeah, just happened to have that.

Kevin:              My lawyers advise me not to answer that question.

Carlos:             Another angle that gets talked about quite a bit is snapshots, so you can save that data, but there’s a tradeoff, right?

Bert:                Yeah, SSRS implements caching as well as snapshots, so basically if you’re running SSRS reports, if your users are running them, let’s say everyone gets in the office at 8am and everyone’s running the same report. What will happen is the first user that runs it will face the full brunt of the rendering time and everything else of getting the data, but then subsequent users will get a cached version, which is great. The only caveat there is the parameters being passed into your report, if your report uses them, have to be exactly the same in order for the caching to kick in, but it is a good way to save on rendering time. So, what you can do is if you know that there’s always a certain report that everybody in the office runs first thing or at a certain time of day or at the end of month or something like that, you can actually just schedule those reports, create a snapshot so that they actually render in advance, so when your users go to run that report, they get the cached version and it’s much quicker.

Carlos:             But then you have to factor in that they are seeing that cached version, so talking to them about real-time and whatever that means, because it can mean different things to different people.

Bert:                Right, slap some timestamps on their reports, so everyone’s on the same page of what time was this data pulled and it’s not real-time, exactly.

Carlos:             Oh, that’s a great point, so maybe adding to the report to help clarify some of that, could be helpful. So, I guess I’m curious, you talked about setting that up, there’s a bit of administration then, that’s required to– I don’t know if you’re going all the way to set up subscriptions? Do you have to go all the way to that point to do the caching?

Bert:                Yeah, the caching will work by default after the first user runs the report. Any subsequent users with the same set of parameters are going to get the cached version. The problem there is, sometimes if anyone tweaks even the smallest parameter, caching goes out the window, but yeah, you could set up a subscription, then, pretty basic, through the portal, to have a report get cached in advance.

Kevin:              And could also be pushed out to a PDF to a file share some place so that you don’t even have to hit Reporting Services throughout the day. You just say, “hey, people, check the PDF.”

Carlos:             Right, that’s where it is.

Bert:                Exactly, yep.

Carlos:             Yeah, I think that would work well, when you have a folder structure, or there’s a set of reports that everybody wants to see and they can just go there and get them.

Bert:                Yeah.

Carlos:             Saving some bloat on your email server?

Bert:                And one other thing that will reduce bloat is if you’re building reports that return a lot of data, if you’re able to paginate those reports, where the data may be returning 10 different tables of data for auditing, if you put each table of data on a separate report page with pagination, that’ll help your rendering times, as well, some of that bloat. What will happen is SSRS won’t have to render pages 2-10 when you’re looking at page 1, and if a user never ends up never reaching page 10, you save that time that they would have otherwise spent rendering.

Carlos:             Now, I thought pagination was by default, and maybe I’m just thinking about one of a Tablix with, I don’t know, a thousand rows in it, it’s not going to show you all thousand by default. Won’t it page to a certain amount?

Bert:                But there’s settings you can specify regarding where you want to paginate, and you can override and say, “no, no pagination at all,” which is then very painful if you don’t need all that data to load on the screen.

Eugene:           So, a question about the pagination piece. You talked about how it’s really good to be explicit with your properties so it’s not trying to auto grow and auto size and do all this kind of stuff. If you’re just dealing with default pagination, does it have to figure out how everything’s laid out before it can tell you where the page cuts off? You know what I’m saying? Like if you have a 5-page report, does it have to figure out the whole structure before it can say, “here’s just the first page”, or is it able to be smart and just say, “well, I ran out of space, so here’s the first page of information”?

Bert:                Oh man, you’re throwing me stumpers here, Eugene. I’ve never thought about it.

Eugene:           Sorry, no.

Bert:                No, I mean I would hope. If you’re specifying the grouping, it should know in advance, “okay, here’s a new set of data, it’s going to go on the next page.” But I’ve honestly never looked into it.

Eugene:           I would think if you’re doing it by grouping, yeah, but if you’re just doing like what Carlos said, because I know when I would do report writing, I didn’t have so much data that it was worth doing a ton of crazy grouping or anything like that. We would have a report for, “okay, we have two systems and we opened up work orders in both of them, let’s see the ones that don’t link up.” And usually it would be like 150 entries, and so that would be like three pages of data. I was just wondering, for something like that where you just have like 150 rows, does it have to figure out all the layout before it can say, “oh, here’s the first page”?

Bert:                I honestly don’t know, that’ll be something to look into. But in my world, it’s always been a very explicit decision, because I’m used to working with data sets that are huge.

Eugene:           Oh, okay.

Carlos:             Bert only works with the big stuff.

Bert:                Yeah, it hasn’t ever been a question of, “oh, do I want to paginate these 150 rows?” I need to paginate because there’s no other way around it.

Eugene:           That makes sense. I promise this isn’t a Kevin question where I know the answer and I’m just trying to stump you, or anything.

Kevin:              Spoilers, it was. So, on a totally different subject, not actually a Kevin question, let’s talk about sub-reports. Are there any (?) implications from using sub-reports?

Bert:                If you’re just using sub-reports without a purpose, or if you want to be able to reuse code, stay dry, sub-reports can have a negative performance impact if you’re not thoughtful about them. In general, I would say don’t use them for easier types of simpler reports, because it is some additional overhead that SSRS has to do to be able to merge those in and render them. However, there are some solutions where sub-reports can definitely improve your rendering time.

Kevin:              How so?

Bert:                One example is if you’re displaying data that’s maybe variable in width, maybe one time, on one set of parameters, you’re going to show a table with 10 columns and another time you’re going to show a table with 300 columns, and anywhere in between. If you build a Tablix that’s 300 columns wide to support your largest table and 99% of the time you are going to be hiding 290 of those columns because they’re not getting populated with your query, maybe you have a dynamic SQL query or something that’s returning the results. That’s going to be a lot of those expressions that are going to have to get ran to hide those 290 columns. So, in those cases, you might be better off having a single sub-report for displaying 10 columns of data and then maybe another sub-report for displaying 300 columns of data. Obviously, that’s a very large difference, there, for an example, but you might want to break it out into 4 or 5 different reports, for 10 columns, 50 columns, 150 columns and so on. Then basically just use an expression on the visibility property of those sub-reports to either hide or show the correct ones. The thing to be aware of is that if SSRS, on those sub-reports, if you pass in all the parameters, even if the sub-report isn’t visible, it’s going to go ahead and render it. What you want to be sure to do is, in addition to changing the visibility expression on any of those sub-reports, is also don’t send all your parameters, or send a different value for a parameter so those sub-reports don’t all render. You only want the one report that you’re wanting to display to actually render. I hope that wasn’t too confusing to visualize. It made sense to me.

Kevin:              I got it.

Eugene:           No, I think I get it, yeah. I’d also advise, you said there’s an overhead, definitely don’t put a sub-report inside of a Tablix, because from personal experience, it can take a long time to render.

Carlos:             Yeah, why don’t you tell us about that story, Eugene?

Eugene:           Oh yeah. Again, I was young and didn’t know what I was doing, but we had to print out work orders for the technicians for fire protection side of the business, and they wanted two different types of reports. They wanted the general work order and then something called a swap sheet, which basically, you have these 15 fire extinguishers, we’re going to swap in and out these ones. For every single work order, they just wanted one giant batch of the month’s work, just all printed out together because they would give them out to each of the different technicians and the technician would figure out his schedule for the month. The way that we did this is we had two sub-reports and we would hide the one, depending on whether or not there was data. But we had that inside of a Tablix that had a row for every single work order, so it took a very, very long time to print those out, so you do have to be a little bit careful with sub-reports.

Carlos:             Yeah, that’s an interesting question. Actually, we were looking at some Reporting Services today, and I guess just like any performance tuning idea is that you may have to rethink how you structure the data or how you get it to that report, because there’s lots of different ways to do that.

Bert:                Definitely, especially if you have a report that’s evolved over time, what worked on day one, when you had small data sets or just test data sets may have worked great, but now in a full-production environment with lots more data, you may have to rearchitect how all of that works.

Carlos:             Interesting. Okay, I know we’re kind of getting here to the end of our time, but there’s a couple of hybrid solutions. You want to talk really quick about some of those?

Bert:                Yeah, and so I would consider sub-report switching one of those hybrid solutions, but another one that I use a lot, well, not a lot, it gets used in desperation, I guess, is– and a lot of people don’t know this, but you can actually perform INSERT, UPDATE and DELETEs in SSRS. You might be wondering, “why would you want to do that?” but that’s right, if you have a data set in SSRS, it doesn’t only need to be a SELECT statement, you can have it modify data as well, which, in the beginning, I think I mentioned that some of these hybrid solutions can be a bad idea. This definitely falls into that category if you don’t use it wisely. But what you can end up doing is, things that I’ve used it for is imagine setting user preferences for something, or if your data’s changing as a user views different reports. If a user wants a default sort order or something like that, that you could then build into a dynamic SQL query to maybe put some of that load onto the SQL Server, which will do it more efficiently, you can maybe save those user’s preferences by putting an INSERT statement or an UPDATE statement into a data set. Obviously, this solution is the worst, in terms of if you have access to a webserver to build a nice fancy form for users or if you can connect to SharePoint or something, but like I said, in moments of desperation where the only tool you have is SSRS, it is something that is possible to do. It’s not pretty, but you can modify data and so if you can modify data to help reduce the amount of data that gets brought back to a report or help in retrieving that data from SQL Server, I’ve definitely used it to help in those scenarios, and it works great.

Carlos:             I’m curious, I have never done that. Anybody else, you guys ever use Reporting Services to INSERT or modify data?

Kevin:              Thankfully, I can say, no I have not.

Bert:                This is definitely one of those solutions that you pull out from up your sleeve when you need it.

Carlos:             Sure, sure, sure.

Bert:                The one thing to be aware of, is if you do end up using an INSERT, UPDATE, DELETE operation in a data set, you want to make sure if you go into your data source properties, there is a single transaction property. So by default, SSRS executes data sets in whatever order it feels like, which will not work if you are depending on a row to get inserted before displaying it on your report screen. So when you turn on that single transaction property on your data source, it forces the data sets to execute serially, which I just, 99% of the time, that’s what I need to happen when doing those types of operations.

Carlos:             Right, okay. Well, very cool. lots of different things, and again, we kind of glossed over some of the SQL stuff, which you can get into. But I think it is interesting, and perhaps those SSRS solutions could be valuable for folks out there that are looking to get a little bit more bang for their report buck. Last thoughts?

Eugene:           I think it was all good stuff. I think the simplest way that I’ve always described it, at least in my opinion is SSRS is the presentation layer, and so I try to keep the stuff that involves the actual presentation in SSRS and export as much of the work to SQL Server as humanly possible, because that’s what it’s designed for.

Bert:                Yeah, and my final thought is just that if you’ve never used those SSRS logging tables, they’re a lifesaver for knowing what reports to focus on first, which ones are causing you the biggest problems. Because sometimes users may say, “oh, my report’s slow,” but what does that mean compared to everyone else’s reports, if you’re managing a large instance?

Carlos:             Right, right. Good stuff. Should we go ahead and do SQL Family?

Bert:                Yeah, sounds good.

Carlos:             All-time favorite movie?

Bert:                I’m not a huge movie fan. I used to be, as a kid, so I’m kind of still stuck in that era, and so growing up I wanted to be a director. My favorite director at the time was this guy, Robert Rodriguez. You might know some of his movies, From Dusk Til Dawn, Once Upon a Time in Mexico, Planet Terror he had more recently, but I’ve always been a fan of his movies, because he does everything on those films. He writes, he directs, he figures out the clever stunt sequences, he composes the music, he edits. He’s kind of a jack-of-all-trades guy, so just watching his movies has always just been really impressive to me, that a single person can put all of that together.

Carlos:             So, is there one of those movies that stands out to you above the others?

Bert:                His first one that kind of became popularized is called El Mariachi, which I think he shot for like a $500 budget. It became widely released, so that was pretty– I think he spent like seven grand on developing film and like $500 on actually filming the movie, so I’d go with that.

Angela:           I love that movie.

Bert:                Oh, you’ve seen it?

Angela:           I have. Probably one of like 25 people in the world, so yeah.

Carlos:             City or place you most want to visit?

Bert:                Recently I’ve really been wanting to go to England. Never been, I’m not really a city kind of person, but there’s a guy on YouTube that I like watching. He’s a photographer called Thomas Heaton, who’s always in the national parks around England and the UK and they’re just absolutely beautiful looking, so I’d love to go there.

Carlos:             Very cool. Food that reminds you of your childhood?

Bert:                Alright, so when I saw this question, I started thinking of my favorite foods, but then that’s not what the question’s asking. It’s asking what reminds you of your childhood, so to me, I think the thing that reminds me the most of my childhood is probably head cheese.

Angela:           Ew!

Carlos:             I’m not sure that I know what that is.

Bert:                It’s a gelatin. It’s a savory gelatin with meat in it. And so I have very distinct memories, growing up, of my mother cooking like a pork leg or whatever, to get the gelatin goodness out of that and then taking all of the spare parts of a pig or cow or whatever was around and cheap and kind of just letting it set in the gelatin and then covering it in vinegar to eat it.

Kevin:              This doesn’t sound like cheese.

Bert:                It is not cheese.

Angela:           It is not cheese. My grandmother, we used to have headcheese sandwiches when I lived with my grandmother. So I’m very– but you know, it was cold, so savory is not the word I would use.

Bert:                It’s good, though. I mean, I like it.

Carlos:             Well, there you go.

Bert:                If you eat it and you don’t know what it is, you’re better off.

Angela:           Yeah.

Carlos:             Well, there you go, that’s interesting. I feel like, not that I’m a complete foody, but at least in the United States, I feel like I know most of the foods, so having heard of a food that I’ve not tried or even knew about is interesting to me. How did you first get started with SQL Server?

Bert:                I started with SQL or SQL, I guess, with just MySQL, running websites, back in the 90’s and then when I got my first job out of college, it was a Microsoft shop and I said, “yeah, I know all about writing SQL queries” and so they put me on SQL Server and I’ve been learning ever since.

Carlos:             Now, if there was one thing you could change about SQL Server what would it be?

Bert:                Oh, I’m super excited about this. The last time I was on the show, I think I complained about the error messages just being useless. Like when you have data truncation occurring, it doesn’t give you any kind of hint or–

Carlos:             Right, and I noticed there was a video that you posted after that about finding the error messages in the line.

Bert:                Yeah, and so I don’t know, this was maybe a month ago, I got an email from UserVoice or whatever the new platform is, saying that particular UserVoice item was being reviewed or in process or something, like they’re actually going to fix it. So, I’m not fully convinced that it’s going to happen, but boy am I really excited if it happens, so with that, I mean, I have nothing else to complain about if that, if that thing gets fixed, I’ll be so happy.

Carlos:             All your worries will be over.

Kevin:              It does narrow it down to string or binary data, so all you have to do is look for all of the string and binary data.

Bert:                Yeah, my databases are full of binary data. That’s all I store.

Carlos:             Best piece of career advice you have received?

Bert:                I think I’d still stick with what I said last time and just kind of “fake it ‘til you make it”, or “make it ‘til you make it”. Work hard and do it and then eventually you’ll be doing what you want.

Carlos:             And Bert, our last question for today’s episode, if you could have one superhero power what would it be and why do you want it?

Bert:                This is going to sound lame. I want to be able to jump perfectly in between time I’ve scheduled for focusing and working and times that I’ve scheduled to just relax, because I usually find myself flipping in and out between both. It’s not something cool like flying, but right now, in this moment in time, boy would I love to get organized with that.

Carlos:             There you go. Bert, thanks so much for being on the program today.

Bert:                Thanks for having me back, Carlos, and thanks for everyone sitting in.

Carlos:             And they approve.

Kevin:              Thank you, Bert.

Carlos:             It’s been great having you.

Carlos: Again, big shout-out to Bert. Thanks for joining us and it was ironic, interestingly enough, using that ExecutionLog3 table, I did actually did need to use that fairly quickly, fairly in short order after we recorded that episode, and so I was glad that we had the conversation and it was fresh in my mind. Always interested in talking with him and thanks again for coming on. Compañeros, I think that’s going to do it for today’s episode. It’s nice having you aboard. You can reach out to me on social media. I look forward to things you want to talk about, topics you might want to hear. On LinkedIn, I am at Carlos L Chacon, and I’ll see you on the SQL Trail.