Welcome!

Video Authors: Stephen Wong, Ben Bradley

Related Topics: SOA & WOA

SOA & WOA: Article

So You Think You Know SQL?

Developers cannot be experts in every language or technology
By Andrew Powell
Article Rating:
April 27, 2008 10:45 AM EDT
Reads:
 6,315

Andy Powell's Blog

I was reading an old post by a buddy of mine about bad SQL being the cause of a lot of application problems and decided to weigh in my two cents.

A lot of developers get forced into writing SQL as part of their jobs. Should they be doing it? I don't think so. It's not necessarily the best of ideas, and in MOST cases should probably be avoided at all costs.

Besides, developers cannot be experts in every language or technology right? Something has got to give somewhere. It's usually their SQL skills that suffer. Developers are, sometimes, forced into situations where they have no choice but to write their own SQL. There is either no DBA, a DBA who isn't interested in helping developers with their queries, or a DBA who isn't even in the development loop (never a good sign). In these cases, developers may have to write their own SQL.

Sometimes developers have to know their limitations when it comes to writing queries, especially complex queries. I don't think a lot of developers do truly know their skill limitations. Yet, these intrepid souls will trudge on thinking they can write SQL just fine. When, in reality, they really and truly do not know the little tricks and tweaks that can make the SQL perform better.


I've seen it a thousand times. It's not an indictment of the developer, just a limitation of their skills that needs to be recognized.

Don't lose hope though, there are ways to combat this and make your SQL as good as it should be, or as good as it can be. What are these answers, then? Personally, I opt for something like Hibernate (Java) or Transfer (ColdFusion) to abstract my SQL for me. What does this mean? It means that my queries will be optimized and I can spend my time focused on developing the business logic rather than spending that time mired in persistence.

It also gives me the agility of being (most of the time) database agnostic. This lets us easily develop in one database locally, and port the same code to the server on a different database with minimal configuration changes. If ORMs are not an option for you and you have access to a good DBA, engage them. Talk to them and mine them for all the information they're worth. It's their job to know the database you're using and how to optimize queries. Make them earn their money by at least helping you write your queries, if not transferring the database functionality to them altogether (good luck).

None of these two options are viable? Well maybe you'll have to engage an outside consultancy to help you with your persistence layer optimizations, or pursue another path for optimizing your queries that has not been mentioned here. The bottom line is this: If you optimize your persistence today, then you'll have to spend less time dealing with it later when it causes you problems tomorrow. You may be able to write SQL, you may be able to do complex joins and create some wicked stored procedures, but are you a SQL expert? If so, then never mind this post. If not, then don't be afraid to swallow your pride and ask for help.

Published April 27, 2008 – Reads 6,315
Copyright © 2008 SYS-CON Media, Inc. — All Rights Reserved.
Syndicated stories and blog feeds, all rights reserved by the author.

More Stories By Andrew Powell

Andrew Powell has been architecting and developing Web applications for over 10 years using ColdFusion, Java, ASP.NET and ASP. His background includes experience running IT Departments for firms in the executive search and aviation consulting fields. You can read his blog on everything ColdFusion, Java, Flex & AJAX at www.infoaccelerator.net.

Comments (3) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

Most Recent Comments
Tony 04/25/08 10:11:57 PM EDT

I spent 6 years at Oracle and did a fair amount of performance tuning. In general, it is not the DBA's job to tune SQL for the applications. Most DBA's have only a vague understanding of those issues. Their job is generally to keep the database running reliably.
Also, relying on a package such as hibernate to do your tuning only proves that your SQL will remain untuned.
Tuning involves examining the use pattern for the application, examining the storage pattern for the underlying database, and choosing among SQL statements based on the patterns of data access you discover. There is a reason why Database experts are well paid.
open honest 04/24/08 06:36:34 PM EDT

The one thing you have to remember is that open source leads to new things and concepts. Just ask Astrum Inc. http://www.astruminc.com what astrum did was to develop the first SUSE based Solution Stack using Novell technology. What they produced and what the independent testing reported was a beast of an appliance and Astrum published the reports on it's website was the first ever Identity based encryption system that can target users who have access to critical data or compliant data and harden policies that are compliance mandated. Lock them down in the appliance and integrate nCipher HSM Encryption Card into eDirectory then developed a key management system that never exposes any part of the key. Now according to nCipher as told to me at RSA this makes the Astrum solution the only solution to meet the up coming FIPS 3 compliance changes and make this appliance very unique in the market space.
The problem:
The concept was presented to Novell under NDA two years ago in 2006 and promises where made protection agreements signed and software groups were worked with to ensure no competitive issues may arise. They did not! So Astrum shared with Novell executives the plan that at the end of the day would map 8 of the PCI requirements to the appliance and all Novell products could sit on top. What happened is Astrum became the first ever to develop and Novell based solution stack using SUSE enterprise in a appliance only to have it stolen from them!.. Hence the following links.
http://sev.prnewswire.com/computer-electronics/20080416/AQW05816042008-1...
http://www.novell.com/linux2/appliance/
So if the solution is so new why expose a concept to a company who will steal it if it has enough market impact and when the solution has ability to change a business direction for a major software company like Astrum did for Novell. Prior to 07 from what I understand Novell couldn't spell compliance much less understand an appliance?
Develop for Novell on SUSE or jeOS, NO WAY!!!
Open Honesty 04/24/08 06:30:22 PM EDT

The one thing you have to remember is that open source leads to new things and concepts. Just ask Astrum Inc. http://www.astruminc.com what astrum did was to develop the first SUSE based Solution Stack using Novell technology. What they produced and what the independent testing reported was a beast of an appliance and Astrum published the reports on it's website was the first ever Identity based encryption system that can target users who have access to critical data or compliant data and harden policies that are compliance mandated and lock them down in the appliance and integrate a eDirectory based HSM encryption card and develop a key management system that never exposes any part of the key. Hence this makes it the only solution to meet the up coming FIPS 3 compliance changes and make this appliance very unique in the market space.
The problem:
The concept was presented to Novell under NDA two years ago in 2006 and promises where made protection agreements signed and software groups worked with to ensure no competitive issues may arise. They did not! So Astrum shared with Novell executives the plan that at the end of the day would map 8 of the PCI requirements to the appliance and all Novell products could sit on top. What happened is Astrum became the first ever to develop and Novell based solution stack using SUSE enterprise in a appliance only to have it stolen from them!.. Hence the following links.
http://sev.prnewswire.com/computer-electronics/20080416/AQW05816042008-1...
http://www.novell.com/linux2/appliance/
Hence, who would expose there concept to a company who will steal it if it has enough market impact and that has ability to change a business direction for a major software company like Astrum did for Novell. Prior to 07 Novell couldn't spell compliance much less understand an appliance?
Develop for Novell on SUSE or jeOS, NO WAY!!!