Prepared Statements

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 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?

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,

Julien