Subject: Re: [boost] sqlpp11: SQL for C++
From: Roland Bock (rbock_at_[hidden])
Date: 2013-11-14 04:11:11
On 2013-11-13 12:09, Dominique Devienne wrote:
> On Wed, Nov 13, 2013 at 7:28 AM, Gavin Lambert <gavinl_at_[hidden]>wrote:
>
>> The GenerateQuery method returns a "query object" that defines the action
>> to be done but not the specific parameter values on which it acts. It is
>> expensive to create (because this is where all the parsing happens) but is
>> (fairly) cheap to copy, and reusable without copying. This is analogous to
>> preparing an SQL query.
>>
> I agree with Gavin's points. Support for prepared queries returning some
> kind of functor / lambda taking as many typed variables as the query has
> placeholders is essential IMHO, because unless you have true binding of
> in-memory data, it's a "toy" to run literal SQL only. (whether it's the SQL
> string literal parsed at compile time, or a compile time DSL matters less
> to me).
>
> But I'd like to flesh out my point that array-binding is essential to
> performance. Most commenters focus on queries, i.e. selects, where you send
> little data (just a few bind variables) and receive lots of data. That can
> be made efficient in Oracle just by turning on prefetching on the statement
> handle, w/o any other change to the client code. It's the OCI client
> library which does the work for you transparently.
>
> But when you insert or update, you have to send a lot more data than you
> receive, and if you do a round-trip to the server on every row, you get
> orders of magnitude performance differences sometimes. For example,
> inserting 10,000 rows in a two-column table (an int ranging from 1 to
> 10,000, and a fixed "payload" string for all rows that read "azertyuiop"),
> doing it the "normal" way takes 0.578 sec, while sending the 10,000 rows at
> once take 0.007 sec. This timing is with the Oracle server and the client
> on the same Windows laptop. (the same test running on Linux, talking to a
> Linux server in the same room over the LAN gives 0.310 sec for scalar
> inserts, < 0.001 sec for array insert). Increase the latency by using a
> server on the WAN, and the difference can be even worse. But unlike the
> select case, there's no prefetching equivalent, you must use separate APIs
> to bind, and it can be done differently too (one array per col, or one
> array of struct with a member for each col, or using dynamic binding via
> callbacks that OCI calls to fill its "buffers" before sending the whole lot
> server-side).
>
> An API like SQLite doesn't need array-binding, because it's not
> client-server, but server round-trips is what makes or breaks an
> application's performance when dealing with databases, at least for those
> which deal with high volume of data, which is often one of the primary
> reason they are coded in C++ in the first place.
>
> An API like sqlpp11 cannot be everything to everyone of course. You're very
> much entitled to decide for yourself what is in-bounds and out-of-bounds.
> But I thought I'd provide more info on the above so that you at least are
> more aware of the problem space as I see it myself.
>
> Cheers, --DD
>
Gavin, Dominique,
Thanks to both of you for clarification and explanations! In fact, I
misread Gavins mail and as I answered to Dominique a few days ago, I
need to read up on prepared statements.
I'll probably come back to you about that.
Best regards,
Roland