Database Performance: What to do when problems arise

In this podcast Rob Steward explains the first steps to take when confronted with a database application performance issue. The podcast runs for 4:00.

Click on the following link to listen to the podcast: http://blogs.datadirect.com/media/RobSteward_FirstStepsWhenDataAccesIssuesArise.mp3

Rob Steward:

So great question; really if you think about it, that’s really what you want to get out of reading The Data Access Handbook. We’ve all through our careers – and the reason you may read this book to begin with – is you’ve rolled out some application that has performance problems, that has a bottleneck that you have to solve because the application doesn’t perform well enough for your company to use it, or for your end users to be able to use it.

Sometimes we’re lucky enough that we are the developers of the original application. We find the bottlenecks when we are testing before we rollout the application, and what do we do about it there? But often what happens is you may take up responsibility for an application which you didn’t write, and all of a sudden you get the privilege of maintaining that application. Suddenly, for whatever reason, maybe it was working fine last week when there were only 50 users and now there are 75 users and it doesn’t work well. What is my advice on how you start to look when that happens? My end users call up all of a sudden and say, ‘hey, this was okay last week, but it doesn’t work anymore.’ What’s the first thing I do?

The very first thing you want to do in that scenario, when it was running fine in production and now it’s not, what you’ve got to identify is what’s changed. It seems like a simple thing to say, but that actually can be a lot more complicated. Because often times it’s the programmers or the DBAs or whoever gets that call that things are just going too slow, we don’t know everything that has changed. Sometimes it is obvious. We’ve done an upgrade. We’ve gone in, changed the code, added some functionality; we’ve fixed some bugs, whatever it was – for whatever reason the code has changed. Obviously if that’s changed, you want to look at those particular sections of code. Sometime maybe the database changed. You do want to look at the database and say, ‘maybe the queries that we issue now are no longer using indexes,’ or something to that degree.

Let me say this too; there are a whole lot of things that we cover in The Data Access Handbook that could be the cause of performance issue because it’s changed. But again, what you’ve got to identify is what’s changed. Has the code changed? Your network typology may have changed. You need to find out where there is digital routers added, were MTU sizes changed on your network? The database itself may have changed. Again, you may not be using indexes that you were using a week before. The key part here is identify what’s changed, and then start to look closer.

I think probably the easiest one for a programmer is to address would be if we’ve changed code. A couple of things you want to look at there – if you’ve changed the code, changed the data that you retrieve, changed the SQL statement; look to see if all of a sudden you’ve started retrieving more columns on a particular query than you were before. And you may not even be returning them to the application, but look for that kind of thing. Did you change the way you execute a statement? Did you change from using a prepared statement to using an unprepared statement? Those kinds of things, and again we cover literally 100s of these things in the book to look for. The key part is, what’s changed?

Now if you’re in test, and you built an application, and this is the first time you’re testing the performance, and it’s not accessible, then really you’ve got to look at your code. You’ve got to look at your databases to make sure that your schemas and your tables and your indexes are all correct. And look at your coding technique, which again we cover many coding techniques in The Data Access Handbook.

If it is already out there, you’ve got to identify what changed. If you’re still in test and it is the first time you’ve done your performance testing, then you’ve just got to look at fundamental data access.

gdzhj9k7r5