Re: Prepared Statements

On 16/07/2003 21:24 Julien Le Goff wrote:
> Hello everyone,
>
> I have a question regarding the efficiency of Prepared Statements. I'm
> working on a project, and my task now is to decide whether it's worth
> it to use PS. This problem came up when, beginning to implement jdbc
> classes, we noticed that we would need a lot of PS - something like 40
> per class. Each PS would be a class variable, and it sounds weird to
> have 40 class variables... We could have a more elegant system using
> normal statements, but would it be much less efficient?

I use PreparedStatements all the time. They don't have to be class
variables so whoever is telling you really ought to learn to program in
Java.

>
> I started doing some very simple tests: inserting 1000 elements to a
> table, doing 1.000.000 simple queries, then 1.000.000 queries with a
> join... But suprisingly, Prepared Statements didn't give better results
> than normal statements. Before warning the world that prepared
> statements are a big lie, I wanted to have your opinion. Has anyone
> done a reliable test showing the difference between PS and normal
> statements? Does anyone know "how" better PS are supposed to be?

I think you're correct that there's currently no performance benefit with
PS although this may change in some future release.
>
> Then, concerning my test, what the hell could be wrong in what I did?
> The query is the following:
>
> String theJoinQueryPrepared =
> "SELECT tr.text FROM truc tr, test te " +
> "WHERE tr.id = te.id AND te.id = ?";
>
> for a Prepared Statement, and
>
> String theJoinQuery = "SELECT tr.text FROM truc tr, test te  " +
>  WHERE tr.id = te.id AND te.id = ";
>
> for a Statement.
>
> Then I just do:
>
>     for(int j = 0; j < 1000; j++)
>     {
>      for(int i = 0; i < 1000; i++)
>      {
>         thePS.setInt(1, i);
>         ResultSet theResultSet = thePS.executeQuery();
>
>      }
>     }
>
> and
>
>     for(int j = 0; j < 1000; j++)
>     {
>         for(int i = 0; i < 1000; i++)
>         {
>             ResultSet theResultSet =
>                 theStatement.executeQuery(
>                         theJoinQueryPrepared + i);
>         }
>     }
>
> I realize that this test is ridiculously simple, but shouldn't the first
> loop be more efficient? On my server both are equally fast...
>
> Ok, I hope this message wasn't too long / too stupid. Thanks in advance,

Forget performance for a moment and consider database security. Lets
imagine that you have an address book table called address_book

CREATE TABLE address_booK
(
     name        varchar(30),
     address     text
);

and you want to select a row by name. You might write

String query = "SELECT * from address_book WHERE name = "+strName

where strName was typed in by the user. What would happen if the user
typed:

joe;delete from address_book

This is a security hole known as SQL injection. If you are using a normal
Statement then your users can probably delete whole tables from the
database but with a PreparedStatement you would write

String query = "SELECT * from address_book WHERE name = ?"

and the command actually passed over to the database would be

SELECT * from address_book WHERE name = 'joe;delete from address_book'

I'm sure you can see the difference. Maybe PreparedStatements will have a
performance gain in some future release but at the moment they have a
vital role to play in database security.

HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+