Multi-row insert with prepared statement about sqlpp11 HOT 15 CLOSED

It's dbms dependent. In postgres it should look like this:
INSERT INTO table("name", "feature")
VALUES ($1, $2),($3, $4),($5, $6);

from sqlpp11.

The main benefit is that a dbms doesn't have to parse your statement on each insert. Beside you don't have to cast input values to string, instead driver transmits raw data.
Typically I use such approach when I need to insert huge chunk of data. I split it on small batches (~few hundred rows) and insert them with prepared statements. Depending on data you can get 20%-40% performace improvement

from sqlpp11.

Great! Let me know if you need any help!

from sqlpp11.

Is there any update on this issue? Thank you

from sqlpp11.

No. It requires one or two days of spare time by me or somebody who wants to prepare a pull request. I cannot currently allocate that time for this issue. Thus, I am hoping for a pull request :-)

from sqlpp11.

Hi,

I assume you want to use parameters in some/all of the rows?

Today that is not possible, I think. Could you give me an example of how this should look like?

Best,

Roland

from sqlpp11.

Hi! Sorry for late reply
Maybe something like this:

std::array<std::string, 3> names;
std::array<true, 3> features;

auto pi = db.prepare(insert_into(p).set(p.name = parameter(names), p.feature = parameter(features)));
names[0] = "Joe";
names[1] = "Bill";
names[2] = "Andy";
features[0] = true;
features[1] = true;
features[2] = false;
db(pi);

from sqlpp11.

Hi,

OK, and what would the corresponding SQL look like? Sorry if that might seem like a silly question. I tried to search that quickly, but did not find anything appropriate so far.

Thanks,

Roland

from sqlpp11.

Got it. So this would be a prepared multi-row insert with a fixed number of rows.

It certainly is doable. But

• is there really a benefit (e.g. performance) compared to the existing multi-row insert?
• I admit I have trouble thinking of a compelling use case. Why would I need a prepared statement so that I can insert multiple chunks of exactly N rows?

Best,

Roland

from sqlpp11.

That certainly makes sense, then.

I would like to have a some kind of a row struct for that, so that you would set the values like this:

auto pmi = db.prepare(insert_into(t).columns<16>(t.name, t.feature));
pmi.values[0].name = "Joe"; pmi.values[0].feature = true;
pmi.values[1].name = "Bill"; pmi.values[1].feature = false;
...
pmi.values[15].name = "Hans"; pmi.values[1].feature = true;

OK?

It will take some time to get there, though (or would you like to give it a shot?)

Best,

Roland

from sqlpp11.

multiple inserts instead of single huge insert can totally kill performance

from sqlpp11.

to take maximum performance with insert/updates with big data it can be a good idea to write a helper so you can write maximum possible data instead of writing them in fixed size batches of rows -

For mysql it looks something like this: query the connection/driver for max_allowed_packet (during init)
SizeOfQueryBody + // like a header size in binary file
sizeOfAllElementsToInsert
numOfElements*2 + // parenthesis around every row
numOfElements-1 // commas between elements of every row

If the resulting packet is too big - compute needed size and throw away excess data.
That way you can push any amount of data with maximum performance without thinking about limits of connection.

from sqlpp11.

I do support that request strongly :) I have just started using sqlpp11 with MySQL and already misses my own C++ templated code for PostgreSQL (sqlpp11 being way superior for about everything else). It is critical to insert performance when accessing a remote RDB with latency (e.g. AWS instance). Inserting 4 rows is a 4x performance boost. I used to prepare power-of-2 statements like insert32Rows, insert16Rows, ... insert1Rows then decompose any random-length input vector of chunks of 32, then 16, etc. Would be awesome for sqlpp11 to support that, though I am not sure whether this is readily available in SQLite.

from sqlpp11.

@petitgrf Just to be sure: Are you aware that there is multi-row insert? This issue is purely about adding support for prepared statements.

That being said: The path is clear, see above (#68 (comment)), but I currently need to focus on other issues outside of this project.

Using the same technique for creating a struct with appropriately named member variables should not be too hard, which is why I am still hoping for a pull request :-)

from sqlpp11.

Putting this on the wish list.

from sqlpp11.