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

SignalFx is the only real-time cloud monitoring platform for infrastructure, microservices, and applications. The platform collects metrics and traces across every component in your cloud environment, replacing traditional point tools with a single integrated solution that works across the stack.

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?


SignalFx is built on a massively scalable streaming architecture that applies advanced predictive analytics for real-time problem detection. With its NoSample™ distributed tracing capabilities, SignalFx reliably monitors all transactions across microservices, accurately identifying all anomalies. And through data-science-powered directed troubleshooting SignalFx guides the operator to find the root cause of issues in seconds.

Topics:

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}