In

017: Making Your Database 10,000X Faster By Focusing on Performance – Eric Strom
Unstoppable Talk Interviews

 
 
00:00 / 29:24
 
1X

Often when we walk into a client with an existing software system, it’s quickly evident that their database design leaves something to be desired.  In this interview, Eric Strom from Covering Data Consulting and I discuss when it’s time to tune up your database, and some of the concepts you can implement when you’re creating a new software application and working with a database expert.





Sam Schutte:                In today’s show we have Eric Strom. Eric is with Covering Data Consulting in Minneapolis. I met Eric through a consulting mastermind group and we’re going to talk about best practices for database design and architectures. Eric, welcome to the show.

Eric Strom:                    Thanks for having me.

Sam Schutte:                Absolutely. Maybe a good place to get started is tell us how you began your work in this field and how you started your company.

Eric Strom:                    Yeah, yeah, so actually it’s been quite awhile, almost 20 years now, but originally I had started, I would say I had some interest in computers and I ended up getting a corporate gig where I started just doing some really basic tools based coding at a smaller company. What ended up happening is I started playing with the database a little bit, finding little tricks that made the performance go a lot better and really started getting into that.

Eric Strom:                    I was actually finishing up my computer science degree at the time and what I found is that, hey, these little things I was learning about maintenance and whatnot, made this database go a lot faster and take up a lot more space. It was just really exciting to be able to do that. And at the same time I was taking some upper division courses over at the University of Minnesota, so I ended up focusing on databases and operating systems. So it kind of all came together and that’s how I became a database administrator back in like 2000, 2001. I’ve been pretty much doing databases ever since.

Sam Schutte:                Okay. What was your first big break for a customer that got you into business?

Eric Strom:                    Well, actually the first big break I would say came from, I was a DBA for about 10 years before I became a an FTE over at a couple of consulting companies locally here just to work with some very sharp database gurus in the area. And I ended up on a project where I met a bunch of indie software developers and just kind of learned more about what it’s like to be an independent and all that kind of stuff. And from there I ended up getting a gig over at the archdiocese of Minneapolis and St. Paul as a database architect and I’ve been doing independent database work ever since.

Sam Schutte:                So tell us a little bit about the exact services you provide and why should customers and clients care about improving in this space?

Eric Strom:                    Yeah, that’s a good question. It’s actually kind of funny because clients tend to really know when they need a database guy, especially when it’s performance related. So you might have some software that you’ve been running for awhile. It has a database in the backend like all software does, and things start going slower and slower and you just have far more problems and it looks like the software’s down.

Eric Strom:                    So a lot of times it’s, when you need a database guy, you really need a database guy. So it’s kind of a break fix situation. But building on that, especially when people start to get the database is a pretty important component of software systems, they start looking for that kind of service or proactively. And that’s, both of those situations I’ve run into places that really appreciate having a good database design because they’ve been burned before, or they’ve also got software systems that are just really having performance problems, and they just don’t have the level of expertise on staff that they need to make the software work fast again.

Sam Schutte:                What do you think at a real core you would say is some of the main reasons why people’s databases are slow or what do you, what do you all, what are textbook examples when you walk in that you see?

Eric Strom:                    Yeah, well to be frank, most software development projects, this is software that’s built in house. People don’t really understand that they need somebody with a decent database expertise to build the software systems. So what they’ll end up having to do is just finding out after it’s built that hey, this is really crappy, or they’re losing data, or we can’t do reports off of this or things of that nature. So that’s essentially when it really becomes apparent that yeah, we need somebody who knows more about this product and kind of how it works under the covers.

Sam Schutte:                Gotcha. And so what kind of ROI are folks getting, do folk typically get when they come in? I mean, what, I know on some of your a webpages and website, you talk about thousand times speed improvements. So is that typical or what do people typically see?

Eric Strom:                    You know, frankly, once you, from a perspective of a person who does what I do, once you learn some of the techniques, once you really get that experience under your belt, 1000, 10,000 times faster actually isn’t that hard to achieve. So yeah, let’s say you have have a webpage that on your custom built software that’s taking like a minute to load and you’re finding that, let’s say 50 seconds of that 60 seconds is actually happening on the database end. I can use some techniques that just right off the bat I can get it under a second versus 50 seconds.

Eric Strom:                    So it’s really a skill that you have to develop over time. But once you understand the main reasons why these kind of problems happen, you can apply it pretty regularly and get results pretty regularly.

Sam Schutte:                Gotcha. Where do, and when you come into a project, if it’s not working, what are some of the problems you run into that makes it sort of harder or a project like this would struggle?

Eric Strom:                    Yeah. What I find is, well when I come into a place that’s having a lot of performance issues you’ll have … This would probably be a smaller place. You’ll have developers who maybe can do a little bit of Googling and try to figure out, like a lot of people assume that the magic button on databases is hey, I’m going to add an index and suddenly everything will get better, which is far from the case. A lot of times you can add too many or the index will just straight up be ignored.

Eric Strom:                    And then you also have sys admins who will be able to kind of see what the issues are and they’ll see all the, like say on a server, the memory getting taken up and they’ll find that, hey, if I reboot this, this will get better for a while. It’s just basically just the symptom, not the problem.

Sam Schutte:                Can you talk a little bit about, for folks who might not be database experts, what is an index and when is it good? When is it bad?

Eric Strom:                    Yeah. Yeah, that’s a good question. So a lot of people use it as an analogy for an index in a book, right? If you need to find something in your book, you go to the back, you’ll look at the index. It says go to page XYZ. So it’s not really all that different. I mean databases are just where people put all their data, right? So to be able to find your data quickly, you have an index. And the thing about it is though, you have to understand why an index is used, which means you have to understand how the database works under the covers, because I could add a thousand indexes in a database and make everything worse and not actually have them used at all, so again it does come down to the database fundamentals and understanding under the covers.

Sam Schutte:                Do you typically, when you’re looking at an application, do you or you kind of have a belief that for every query in the application, every database query, there should be a matching index that supports it? Is that kind of how you approach when you’re looking at a slow application?

Eric Strom:                    No, not at all. My opinion is that its indexes are kind of a fine tuning. So as you understand kind of how the queries are working and what’s hitting them, if you have hotspots that can be addressed by indexes, in other words, it’s just one tool in your tool box. If it’s clear that this particular tool will help the situation, then go ahead and do that. But actually, funny enough, a lot of times I end up removing indexes to make things faster again too. So it goes both ways.

Sam Schutte:                Sure, sure. So what do you think at a high level, what makes a good database design versus a bad database design and what are your sort of, some of your key tenets you’ve got around that?

Eric Strom:                    Yeah, that’s a good question. So I tend to focus on what are called OLTP databases. That means online transaction processing. And really from a layman’s perspective, that’s basically application databases. Unlike big data warehouses where you have to do a bunch of analytics. So when playing in that space there’s somebody who came up with some design principles back in the late 70s, E.F. Codd and people tend to hear about it if they’re in the technical space of database normalization. So having a good understanding of those techniques and why you use it, use normalization, helps you design better databases. And in addition to that, understanding how, in my case I use a software called SQL server, which is a Microsoft product. So understanding kind of how things work under the covers with SQL server also affects my physical design as well. So just just understanding some of those principles and also the implication of your choices on the database software. Both of those are, I would say, pretty important for good database design.

Sam Schutte:                So obviously you come in and if you’re trying to fix an application, there’s going to be other developers, system administrators, other parties involved. How do you sort of work with them to, as a team, to make that happen?

Eric Strom:                    Yeah, yeah, that’s, that’s a good question. So a lot of different places have … Well, I should say a lot of technical staff have different impressions of what a database person might be able to do. So I used to be more sys admin like in how I approached things. So I’ve worked a lot with sys admins. I have an appreciation of their jobs and so it’s easy to talk to them about that. But actually more recently I’ve been doing a lot of hybrid developments. So I’ve been wearing that hat myself. And so as such I can more appreciate some of the challenges that come with software development too, which is more beyond having the perfect database. Ultimately you need to deliver software. Right?

Sam Schutte:                Sure. And then how you work on these projects, just kind of like from a logistical standpoint? Are you going on site? Are you remote for all these projects or how does that work?

Eric Strom:                    Yeah, I primarily go on site. I really like to interact with customers. My opinion is being there in person and understanding their challenges, being able to work with the staff, that’s my personal sweet spot. Some people don’t mind doing it remotely, but I like being outside as much as possible and being able to get to know the people and I find I work best when I’m working with the team versus just working in a basement somewhere with the lights down all by myself. So that’s how I like to do things.

Sam Schutte:                Gotcha. So what are some of the sweet spots in new areas you’re getting into outside of performance tuning, and application tuning, and database design. What are some new areas you’re looking to learn more about and get into?

Eric Strom:                    I’m very, very interested in some of the cloud solutions, specifically Microsoft Azure. I really like, I like all of the tools and widgets that you can do there. So not only is there a lot of interesting things going on within the database space with the different database offerings on Azure. I am also finding a lot of joy in doing the occasional micro services in Azure, like Azure functions and just being able to … You know, compared to 20 years ago when I was just getting started, the ease of which you can spin up an application or a database and get something working, is just there’s no comparison. So that’s what really excites me in that space right now.

Sam Schutte:                Yeah. What’s one of the more rewarding projects you’ve worked on recently that you’ve really gotten a lot out of?

Eric Strom:                    One of the projects I really enjoyed lately was I got to build a dashboarding project. Essentially there’s a software system called the Razors Edge that’s big in the fundraising space, so I got to help build a BI, that is business intelligence dashboard for fundraising customers that really brought the data out on a web application. So I got to be involved in building the database, kind of processing the data, getting the summary stats, and working with the developer to get all those stats out into a nice looking dashboard.

Eric Strom:                    So that was a lot of fun because I both got to build the database from scratch and kind of work with that. But I also got to do some of the development and work with somebody else who is very talented on the front end side of it, so I really did enjoy that.

Sam Schutte:                Cool. If we look at the industry that your customers are in and the different sectors you’re working in, what are some of the key pressures they’re facing that are kind of driving them to build new applications and sort of be concerned about performance?

Eric Strom:                    Yeah, that’s a good question. So right now, I’m focusing mostly in the software as a service space. And the reason is customers in that area, unlike if you have a slow database, let’s say on your accounting software, like [inaudible 00:15:36], you’re accounting staff can wait a little bit. But if you have a database back in for a software service that you’re providing to everybody over the internet and it’s just not performing like you need to, you can lose customers. You know, you can, I mean it has a direct impact on your cashflow, right?

Eric Strom:                    So that’s one area that I like to focus on. And what I typically see in that space is a lot of companies are like a victim of their own success. So they’re trying to really quickly build a product so that they can get market share and become successful from a profit standpoint. But at the same time there’s more and more strain on their database design, which typically you’re not going to have a database architect helping you build it. You’re just, it’s just going to be a few people, a few developers at first trying to build something as quickly as possible.

Sam Schutte:                Yeah. So they get their MVP to market but then scaling that is where they run into trouble.

Eric Strom:                    Yeah, exactly. And so a lot of times the database is the bottleneck and sometimes throwing hardware at it just isn’t good enough.

Sam Schutte:                Yeah. Obviously that sort of move towards the cloud and software as a service is affecting and impacting your customers and their need for more performing databases. What are, are there other technologies that if we look at AI, or big data, or something that are also pushing them to need more performance and more structure?

Eric Strom:                    Yeah, I think as far as AI and big data, I like that a lot of the services are becoming more approachable to the average technologists. As far as what I’m seeing in the industry, there’s a lot more interest in different types of database solutions. So like from a developer perspective, there’s some databases that natively store [inaudible 00:00:17:41], all that kind of stuff. But ultimately it seems to me that relational databases still seem to be the predominant solution for software as a service providers.

Sam Schutte:                Naturally, and have you ever worked too much with or … For those types of applications, sometimes there’s all the no sequel databases and Google style, whatever they call their big table and such. Have you, how do those compare in the market to SQL server and other solutions?

Eric Strom:                    Yeah, that’s a good question. I think what I’ve been seeing is when you need a lot of performance, but some of the things that relational databases are really good at aren’t as important. Like you take Twitter for example, if you might lose the occasional tweets it might not be the end of the world compared to like banking software, for example. So they’re willing to compromise things like consistency for speed.

Eric Strom:                    So that’s what no sequel is. There’s a set of tenets that relational databases are built on and some of those tenets are relaxed to get better things like speed, for example, or make it better distributed. So that being said, a lot of times it’s just a lack of understanding of relational databases that make some developers seek other solutions.

Sam Schutte:                Yeah, because they feel like that if they don’t know how to build a good table structure, if they use no sequel or something, they don’t have to. Then that’s not necessarily the reason to choose it.

Eric Strom:                    Yeah. And then then you end up needing like a MongoDB database administrator, which is like a one and 100 compared to like a sequel DBA.

Sam Schutte:                Yeah. Yeah. Have you ever looked at some of the time series database solutions out there, like Influx DB and others? You ever exposed to those?

Eric Strom:                    I have not. However, I do like what SQL server has been doing in that space. All the relational database providers need to stay competitive. So I really like the idea of being able to easily … Like one of the big problems I’ve always had with getting fast and easy queries for this is like when records change over time and I’ve seen some better solutions from the other SQL server with that, but I haven’t really touched that too much.

Sam Schutte:                Yeah. I guess some of those systems like SQL server for instance, I think it starts to lose a little when you’re dealing with just extremely high speed, massive amounts of data being written or pushed out of say like a factory full of IOT devices. Right?

Eric Strom:                    Yeah.

Sam Schutte:                And you don’t have necessarily really complex queries because you’re just wanting to know like what was the maximum temperature in the last two days. Relatively simple queries. You just need to write a thousand of them a minute or something, you know?

Eric Strom:                    Yeah. I’m with you on that one. That’s one thing I do find interesting. Even if I’m a particular expert in some of these no sequel solutions, it really is neat to see a lot more variety based on specialized needs with data storage, so it’s a pretty interesting space nowadays.

Sam Schutte:                Yeah. It’s just that specialization of databases means that the tools are better suited for what you’re using them for which means they’re more powerful. But of course it also means if you pick the wrong one as a tool, it can be a horrible fit for what you’re doing. Like we said.

Sam Schutte:                How are customers in general sort of finding people to help them with these problems and do you think is there a better way than what folks typically do?

Eric Strom:                    Yeah, so there’s probably two major ways that people find database experts that I see. One is reaching out to your local or sometimes national recruiting company, which is just like, “I need an IT resource. You’ve helped me with IT resources, so what do you got for me?” And there are local companies that do specialize in a database and analytics and stuff like that. So I would say those are the two major ways that people tend to look for database resources. Over just based on experience, recruiting companies can be very hit or miss because they don’t tend to understand. Even within the database space, there’s different specialties. So a guy who can build data warehouses isn’t going to be able to make your app database perform really fast because that’s just not where he’s focused on. So that can be very hit or miss.

Sam Schutte:                Yeah, exactly. Okay. So Eric, what’s one of the best results you’ve gotten in terms of making a database faster?

Eric Strom:                    Actually there was one time when I worked at the state, I was really just trying to help them a bit with a batch process that one of the departments was working on. It was taking eight hours to run and starting to spill it into the normal business hours. So I had this process where the database compartment was taking [inaudible 00:23:27] for that eight hours, and actually I ended up, after looking at it, it was Java code calling SQL queries and I ended up tuning that going from four hours and at first it went down to a minute, then I checked out something else and got it down to 13 seconds. It was a I think a 40,000 times performance improvement

Sam Schutte:                Nice.

Eric Strom:                    Yeah, it was kind fun.

Sam Schutte:                Cool. You are involved upcoming here with SQL Saturday up in Minnesota. Can you tell me a little bit about what that event is and about that?

Eric Strom:                    Yeah. The SQL Saturday is actually a really big thing within our profession, which is our professional organization is called PASS, which is the Professional Association for SQL Server, and they have SQL Saturdays all over the world, and I mean you can find them in India or in New Zealand, Europe, pretty much anywhere you can think of. But the one over in Minnesota is actually coming up October 12th at a place called St. Paul College over in St. Paul, Minnesota. And I’ll be a sponsor there, so I’ll be working the booths over there.

Eric Strom:                    And yeah, it’s a really good opportunity to learn a bunch more about SQL server. It’s a full day. You’ve got experts coming in from different parts of the country as well as some really talented people locally. So it’s really fun and you get to meet a lot of cool people and I just really enjoy it.

Sam Schutte:                And so Eric, what are the, just to summarize, what are the top three problems that a company might have that they should reach out to you about?

Eric Strom:                    Yeah, so a few different use cases. One is if you’re just having a lot of problems with your databases in general, whether it be performance or you’re really trying to solve a complex problem within your databases. I’ve done a deep dive within SQL server, and one of I think six Microsoft Certified Masters in SQL server. It just means that I’m pretty deep into it. But if you have performance problems or you’re trying to get SQL server to do something that you’re just not able to get it to do, I can help with that.

Eric Strom:                    In addition to that, around building new applications. I am very good at building new databases and doing database architecture design, so those are the major areas that I really enjoy helping companies with.

Sam Schutte:                Cool. How can people contact you if they want to reach out?

Eric Strom:                    Sure. Yeah, there’s a few different ways. One, if you want to check out my website, it’s coveringdata.com and I have a blog there as well as it describes my services, but if you want to reach out via email, it’s Eric, E-R-I-C at coveringdata.com. And you can always reach me at phone at 612-991-0623.

Sam Schutte:                Great. Well Eric, thanks so much for coming on the show. Obviously you’ve got a lot of background in databases, and I think when it comes to building systems and applications, a lot of times databases are an afterthought or are sometimes neglected. So I think there’s a lot of folks out there that need your help and it’s definitely good to talk to you.

Eric Strom:                    Yeah, good. Thanks for having me, Sam.

Sam Schutte:                No problem. Thank you.

 

Recommended Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Start typing and press Enter to search