Clint Byrum wrote:
On Oct 13, 2009, at 6:56 AM, Jay Pipes wrote:
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....
The "Nibble" algorithm used in Maatkit (mk-archiver and I think
mk-checksum-table as well) has a nice example of a good usage of running
the same select many, many times. In short, the algorithm has you
finding the row with PKcolumn = min(PKcolumn), then selecting like this:
select PKcolumn,x,y,z from table where PKcolumn > :lastpk order by
PKcolumn limit 10
This allows you to traverse an entire table 10 rows at a time, avoiding
locks, and providing the ability to sleep between them to ease the
burden on the database and prevent your query from taking the entire
buffer pool. This is essentially a bulk "get data out of the database"
operation.
Also there are plenty of applications, like asynchronous backend process
subscribers, that will run in an infinite loop and be fed arguments and
return data on the same connection over and over. The less repetitive
work these have to do, the better.
I agree that uint8_t * is the way to go for the args. Essentially what
the prepared statement API is doing is building a parsed sql tree with
the data nodes empty... so the actual query execution method would just
fill in those nodes then feed the query to the next step in the
execution chain. For most language bindings, its going to be pretty
natural to provide a uint8_t * and length. Doing it w/ multiple types
would just make the code more complex for a very small client side win
of not having to convert your ints.
The above does indeed make sense to me. Thank you for your clear
explanation and thorough response, Clint.
FWIW, in my email response I wasn't necessarily arguing that preparing a
SELECT statement should not be *allowed*, but that the API should make
it clear and easy to do a simple SELECT (and even lots of modifications)
without having to bumble through an awkward bind and prepare API.
This is why my proposed API was this:
drizzle_statement_st *drizzle_create_statement
(
drizzle_con_st *connection,
drizzle_statement_st *statement,
const char *query,
drizzle_ret_t *return_code,
uint8_t** records
);
#define drizzle_select(A,B,C,D) drizzle_create_statement( \
(A), \
(B), \
(C), \
(D), \
NULL)
If you look at my original response, the point was to make things
simple, and have a simpler API that hid the binding and preparation in a
cleaner, easier API.
Cheers,
Jay
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
