You’re new to the SQL game. Maybe you just got hired for a new a job with corporate. Maybe you’re running your own business. Maybe someone is just hitting you up for the customary Hey, you’re a tech guy kind of questions. In any case, someone may be asking you to optimize a system you’re just now familiarizing yourself with. And if you’re reading this, then the problem you’re facing is that an SQL server is underperforming (read: slow as snails). But before you go buying a bunch of new hardware for the machine, consider that the problem might be a little more mundane.
Garbage In, Garbage Out
The human error is probably the most common issue for all computer problems (how many times have you had to tell grandma to plug in the printer cord?). It’s natural for us to make mistakes, but sometimes those mistakes are hard to pin down and rectify. It’s this way with a database.
SQL servers are built on databases, which are built on tables, which are built out of information values. When the information is wrong, untidy, old, or redundant, the tables reflect the sloppy groundwork. Then the databases fail to organize things properly or give you the information you want when they are queried. If hard work didn’t go into the foundation of an SQL system, then it will reflect both in the return values, and in the performance, when operated by a user.
Double checking the information in the database will help maintain the effectiveness and efficiency of the system. Particularly in regards to speed, cleaning up redundant indexes will help reduce redundant query returns, reducing the information that needs to be returned and speeding up the processing time.
Another common issue is when parameters have not been properly filtered. Sometimes, you’re searching through the whole database, or at least a large portion of a big one, when that’s not really necessary. That adds processing time. Making sure that you don’t spend all your process cycles in the wrong index can help keep the pace up.
The Scientific Method
Now, problems with the data itself are not the only things that can go wrong, as you’ll see in that longer article, but they are common. The way to tell if you’re fixing the right thing is to use the scientific method. Specifically, two tactics from said method.
First, only change one thing at a time. If you go tweaking things all over the place, you’ll quickly find that you have no idea what’s impacting the performance and what’s not. So go slow. Change one thing, then check again. Modify, then monitor, then repeat. If the process time improves, you’re headed in the right direction. If not, try something else.
The other tactic that will help with this is recording results. It’s a lot harder to tell if something is working if you don’t have a baseline to reference (in science, they would call this the control ), so whenever you make a change, record the effect, and reference your previous numbers. It will dramatically speed up the diagnostic process. In the words of Adam Savage, of Mythbusters fame, “Remember kids, the only difference between Science and screwing around is writing it down.”
