On 3/26/2015 10:16 AM, Renaud wrote:
Thanks a lot George, for this insightful reply.
Your idea about FFI explaining 95% of the overhead looks good... but i wonder:
in this case, shouldn't we also see a high cost in bind-prepared-statement
alone?
Hi Renaud,
I'm not sure exactly what you are asking, so forgive me if I'm telling
you what you already know.
There is some overhead for every function call, and for foreign
functions there is some additional FFI translation cost which is
relative to the amount and types of data involved. In Racket [and
generally in Lisp family languages] most data has embedded type tags
that must be removed to pass arguments to a foreign call, and conversely
tags must be added to data returned from the foreign call.
SQL normally is compiled to bytecode for execution. Sqlite simply
interprets the bytecode, but many larger DBMS will further JIT compile
to native code when a statement is prepared.
If you only use a prepared statement only once, you save nothing and, in
fact, there will be a slight loss vs immediate execution due to the
addition of the separate argument binding step. It also costs memory
to cache the compiled code in the DBMS (which for sqlite is in your own
process space). However, the more times you reuse a prepared statement,
the less will be the amortized cost of compiling it, and because you
send the SQL statement only once, you also progressively move less data.
So if, e.g., you convert 10 immediate statements into 1 prepared
statement with 10 executions:
* you avoid 9 sends and compiles of the SQL statement
* you add 10 bind function calls, but the net amount of argument and
response data remains the same.
The number of bind calls that balances the send and compile overhead is
statement dependent. However, every send/compile you avoid buys you
that many more bind calls, so once you pass that initial balancing
point, you have a net performance gain from using the prepared statement.
I know this is little help in analyzing your situation, but I hope it at
least answers your question :-)
George
--
You received this message because you are subscribed to the Google Groups "Racket
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
