Over a million developers have joined DZone.

SQL Performance Disasters: Your DBA Just Wants to Help

· Performance Zone

Discover 50 of the latest mobile performance statistics with the Ultimate Guide to Digital Experience Monitoring, brought to you in partnership with Catchpoint.

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?


Is your APM strategy broken? This ebook explores the latest in Gartner research to help you learn how to close the end-user experience gap in APM, brought to you in partnership with Catchpoint.

Topics:

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}