Kristian Nielsen wrote:
Jay Pipes <[email protected]> writes:
Eric Day wrote:
We've decided to add the prepared statement API sooner than later,
I've always felt that the prepared statement APIs (all of them) are
overly complex... I mean, just look at MySQL's C PS API and ODBC's
API:
Unless I am sorely mistaken, a "regular" developer -- i.e. a developer
of an application that is just trying to Get Things Done -- is
generally working on doing one the following:
1) Get data out of the database
2) Put data into the database
For #1 in 14 years of programming, I've never worked on any
application where I have had the need to "prepare" a SELECT statement
that would be executed more than once in a single connection. Never.
If I were to do so, I would ask myself why I wasn't doing a single
SELECT statement to begin with....
For #2 prepared statements can indeed come in handy, particularly when
doing bulk operations.
For what it's worth ...
I _always_ use placeholders in any SQL statement I use from an application, in
preference to interpolating:
$dbi->do("INSERT INTO t(a,b) VALUES (?,?)", undef, $a, $b);
It just does not make sense to me to go the round-about way of quoting,
interpolating, and then parsing stuff out. So much more complexity of
semantics and potential for bugs (eg. sql injection etc.)
(I even blogged about this (http://kristiannielsen.livejournal.com/7817.html),
sorry about the excess irony in there...)
Whether this uses a prepared statement API against the server behind the scene
or not is another matter of course. To me, using server-side prepared
statements is only a matter of performance optimisation.
In fact, I think the complexity and issues with having client-side references
to server-side prepared statements just are not worth it. It should be very
close to just as efficient, and much simpler in terms of semantics, to do what
Oracle does: Keep a hash table (statement cache) in the server mapping raw SQL
string to prepared statement for that string. Then we get all the performance
benefit of prepared statements for just the cost of a hash lookup of the SQL
string to execute, and avoid the cost of an extra roundtrip for prepare. And
we can server-side tune effectively how big a statement cache to keep, purge
least used statements etc.
Or put another way, use the text of the SQL statement itself as the
client-side handle to the server-side prepared statement.
Of course to do this effectively, we need the SQL string received from the
client to use placeholders! So that we can cache these as one:
["INSERT INTO t(a,b) VALUES (?,?)", (1,"foo")]
["INSERT INTO t(a,b) VALUES (?,?)", (2,"bar")]
["INSERT INTO t(a,b) VALUES (?,?)", (3,"baz")]
rather than occupy three different cache slots for these:
"INSERT INTO t(a,b) VALUES (1,'foo')"
"INSERT INTO t(a,b) VALUES (2,'bar')"
"INSERT INTO t(a,b) VALUES (3,'baz')"
That would be my prefered way of doing prepared statements. Concentrate on
getting placeholders right in the protocol so _every_ type/value can be passed
as a placeholder (no LIMIT limitation).
(A statement cache like Oracle has is also extremely useful for performance
tuning, as you can put all sorts of nice per-query statistics into it, like
execution counts, cpu time spent, physical I/O reads, execution plan,
etc. etc. Oracle does this and it is extremely useful.)
- Kristian.
One of the most troublesome issues for PreparedStatement users is the
coding of the IN clause.
e.g. Select * from Table1 where A in (1,2,3,4,5) and B=6 and C=7
It would be nice if I could do this:
n =1;
ps.setArray(n, Object[] v);
ps.setInt(n+v.length, 6);
ps.setInt(n+v.length+1, 7);
I usually write a wrapper around the PS to do the above. It would be
really nice if the driver supports it out of the box.
- Srini
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
