
Improve the speed and security of your SQL queries - acangiano
http://antoniocangiano.com/2009/09/09/improve-the-speed-and-security-of-your-sql-queries/
======
samuel
I know I'm going to burn Karma, but, Parametrized SQL queries on HN's front
page? really?

BTW, Does anybody knows why the question mark is used instead of something
more descriptive? Oracle's JDBC driver allows the use of ":param" syntax and I
can't imagine why is not the JDBC's standard way.

~~~
gaius
The simple answer is that Sun panicked when Java failed to gain any traction
for applets vs Flash and JDBC was rushed out of the door as they flailed about
trying to rebrand it as "Enterprise" by people who had very little experience
of writing database apps.

------
Femur
This article does not touch on speed that much, but saving your database from
reparsing the same SQL over and over is a great thing. Bind variables should
be used in the place of literals whenever possible.

There is a gotcha with this though. Say that you have a table with a column
having three distinct values with the following distribution:

"John" - 80%

"Sally" - 10%

"Mary" - 10%

If the first time your database parses a statement where the bind variable has
the value of "John", then the shared SQL will have an execution plan optimized
for values of "John". Obviously, in this case, using an index to search on
that column for values of "John" would not be worth it. However, you
absolutely would want an index when searching for values of "Mary". So beware!
Your prepared SQL can be suboptimal!

(Note: I speak to the Oracle DBMS of versions 9.2 through 11 only)

------
moron4hire
Were SQL developers never taught the importance of reducing code duplication?

Seriously, I see it a lot "in the wild". Databases that are filled with
duplicated data, duplicate procedures, duplicate views. It's a bit sickening.

~~~
tom_b
I think it is hard to underestimate how much SQL code and database design is
an afterthought on many projects. I tend to see lots of ad-hoc relational
table/view/procedure creation by developers on the fly, without much thought
put into how to best use a relational database and SQL to reduce the amount of
code needed to implement a simple and straight-forward solution.

So I would argue that you're not seeing the work of SQL developers, but rather
the outcome of letting the db design and SQL code be done by inexperienced
developers with backgrounds in imperative languages.

Part of the problem is that SQL developers (and I'm in that group, both from
personal viewpoint and job title) are not necessarily seen as necessary for a
software development project, even when there is a significant relational db
design and SQL code component in the project.

Add in the fact that most of the db backend work is poorly understood and
documented in the first place, and you really quickly get into exactly the
situations you describe.

~~~
moron4hire
I agree with you in principle, that SQL developers are definitely needed, I
just think that anyone espousing to be a programmer should be capable of
proper database design and programming. Well, I guess the people who aren't
are usually not that great at imperative programming either...

