Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Performance Disasters: Your DBA Just Wants to Help

DZone's Guide to

SQL Performance Disasters: Your DBA Just Wants to Help

· Performance Zone
Free Resource

Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

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?


Learn tips and best practices for optimizing your capacity management strategy with the Market Guide for Capacity Management, brought to you in partnership with BMC.

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 }}