3 ms·

Improve the speed and security of your SQL queries

10 points by acangiano 17y ago

Femur 17y ago
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)
- ElmosEverywhere 17y ago
  FACE!
samuel 17y ago
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 17y ago
  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.
moron4hire 17y ago
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 17y ago
  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 17y ago
    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...

10 points by acangiano 17y ago

Femur 17y ago

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)

ElmosEverywhere 17y ago
FACE!

samuel 17y ago

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 17y ago
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.

moron4hire 17y ago

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 17y ago
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 17y ago
  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...