SQL Zone is brought to you in partnership with:

Alec is a Content Curator at DZone. He lives in Raleigh and spends his free time writing and programming. Alec is a DZone Zone Leader and has posted 530 posts at DZone. You can read more from them at their website. View Full User Profile

SQL Performance Disasters: Your DBA Just Wants to Help

08.07.2014
| 5782 views |
  • submit to reddit

Your DBA just wants to help. And when you're working with large sets of data - or any sets of data, really - sometimes that's the help you need, because small mistakes can quickly balloon into large ones. Shaun M. Thomas illustrates the idea in this recent post with an example of a fairly simple insert job of a basic dataset using a script written in Python. The problem, Thomas says, is that conventional programming wisdom can sometimes lead you astray, and may not translate into database work as cleanly as one might hope:

Ever since I changed my career from programming to database work, I’ve still occasionally dabbled in my former craft. As such, I believe I can say this with a fair amount of accuracy: programmers don’t understand databases.

Thomas then demonstrates a couple of ways to handle this import job and measures the performance changes between them. Finally, however, he arrives at one scenario in which each insert takes place in a separate transaction - a realistic possibility for some ORMs, or for an inexperienced or naive developer, Thomas says - and performance is suddenly reduced by 250 times.

Mistakes are easy to make, particularly for a developer who is not necessarily trained in the specifics of a database and knowledgeable when it comes to the nuances of optimization. But that's where DBAs come in, Thomas says:

If my database is being abused, it’s my job as a DBA to try and alleviate the situation any way I can. Sometimes, that means telling people what they’re doing wrong, and how they can fix it. I certainly didn’t know all of this ten years ago when I was primarily a coder. But I would have appreciated being pointed in the right direction by someone with more experience in the field.

It's a good thing to keep in mind: nobody knows everything, and nobody needs to. At least developers working with relational databases have DBAs to help them, right?