File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes To Lynn Beighley : What are the most common mistakes developers do ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "To Lynn Beighley : What are the most common mistakes developers do ?" Watch "To Lynn Beighley : What are the most common mistakes developers do ?" New topic
Author

To Lynn Beighley : What are the most common mistakes developers do ?

Edisandro Bessa
Ranch Hand

Joined: Jan 19, 2006
Posts: 584
Hi Lynn,

As an expert you are, in your opinion, what are the most common mistakes developers do when submitting SQL statements to database and how can we avoid that ?

and ...

In your whole career, I'm just curious to know what is the most bizar SQL mistake you have ever seen.

Best Regards,
Edisandro Bessa.
[ October 19, 2007: Message edited by: Edisandro Bessa ]

"If someone asks you to do something you don't know how to, don't tell I don't know, tell I can learn instead." - Myself
Lynn Beighley
author
Ranch Hand

Joined: Sep 12, 2007
Posts: 82
It's not the SQL statements that are submitted that are usually a problem. If your tables are designed correctly, in general there aren't all that many options for how to get at your data. The variations in SQL commands can cause your processes to be slower, but that's something that can be tested and fine tuned.

I think the biggest problem many developers encounter when first using SQL is not taking the time to understand normalization and having a tendency to build ugly, unnormalized tables that cause them pain as they actually try to use them. For example, there's a thread somewhere on this board from yesterday when someone was asking how to remove duplicate records, something that can only happen when your table is poorly designed.

And those of you who have taken over a database from someone who didn't know what they were doing also get to feel the pain of poor execution.

Nothing particularly bizarre stands out, except for a few really badly designed tables. Recently I encountered duplicate data, non-atomic data, miscast data types, all in a single, misbegotten table. Horrible!


Lynn Beighley<br />Author, Head First SQL
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30136
    
150

Originally posted by Lynn Beighley:
It's not the SQL statements that are submitted that are usually a problem. If your tables are designed correctly, in general there aren't all that many options for how to get at your data. The variations in SQL commands can cause your processes to be slower, but that's something that can be tested and fine tuned.

Definitely!

Even on a well designed table, I find lack of indexes to be a problem. In a table with millions of rows, the difference can be significant. Some developers know SQL, but not databases and aren't aware of this or consider it the DBA's job.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Lynn Beighley:
For example, there's a thread somewhere on this board from yesterday when someone was asking how to remove duplicate records, something that can only happen when your table is poorly designed.


Actually while he had duplicate data, he did not have duplicate keys... that doesn't necessarily imply he had encountered a data base design problem.
Lynn Beighley
author
Ranch Hand

Joined: Sep 12, 2007
Posts: 82
You are correct, Paul.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: To Lynn Beighley : What are the most common mistakes developers do ?
 
Similar Threads
To Lynn Beighley - Where did you get inspiration from ?
Help! classpath problem
What do you think will become the next coming SQL trends ?
To Lynn Beighley - Does HF SQL cover multi-dimensional queries ?
The new 7 wonders of the world. Winner's list.