libpqxx: How to bind array of parameters

Question

I'd like to insert many parameters in postgres table as quick as possible.

Now I'm wasting too much time for binding parameters one by one. Code looks almost like this:

pqxx::connection my_connection(c_string);
my_connection.prepare("insert_to_db", "INSERT INTO t (id, name) VALUES ($1, $2));

pqxx::work W(my_connection);
for (int i = 0; i < 10000; i++)
{
  W.prepared("insert_to_db")(i)("Max").exec();
}
W.commit();

As I can see, commit 10 000 elements take 0.001 s or even less, but binding takes about 10 seconds.

I'd like to bind all parameters as an array of values. How to do it using pqxx? Or there is a better approach for minimizing time?

[Functions Associated with the COPY Command](https://www.postgresql.org/docs/current/static/libpq-copy.html) — Abelisto, Feb 02 '17 at 13:40
For those who are struggling with new version of pqxx which has no pqxx::prepare::invocation class in the library, here is the solution: https://stackoverflow.com/a/66832803/2395663 — Oğuzhan Türk, Mar 27 '21 at 15:58

Csuszmusz · Answer 1 · 2018-05-26T12:03:55.757

Use pqxx::prepare::invocation where you can, and bind more values before execution, because it's more stable and error preventative, but there is a faster way as I describe it below. In your example you execute the prepared statement each time, so you unnecessarily communicate with the database.

I. With invocation:

pqxx::nontransaction W(C);
std::string m_insertCommand = "INSERT INTO tableforperftest(column1, column2) VALUES";


unsigned  int m_nCurrentRow = 32767;

for (size_t i = 0; i < m_nCurrentRow; i++)
{
    unsigned int countOf$ = i * 2;
    for (unsigned int i = 0; i < 2; ++i)
    {
        if (i == 0)
        {
            m_insertCommand += "(";
        }
        else
        {
            m_insertCommand += ", ";
        }
        m_insertCommand += "$";
        std::stringstream ss;
        ss << countOf$ + i + 1;
        m_insertCommand += ss.str();
    }
   if(i < m_nCurrentRow - 1)
    m_insertCommand += ") ,";
}
m_insertCommand += ")";

C.prepare("insert_into_db", m_insertCommand);
pqxx::prepare::invocation inv = W.prepared("insert_into_db");

for (size_t i = 0; i < m_nCurrentRow; i++)
{
    inv(i)(i);
}

inv.exec();

II. With stored procedure which gets more values for parameters:

CREATE OR REPLACE FUNCTION insertintoboosted(valuesforinsert TEXT) RETURNS VOID AS
$$ 
BEGIN
     EXECUTE 'INSERT INTO tableforperftestproof(column1, column2) VALUES (' || valuesforinsert || ')';
END;
$$
LANGUAGE plpgsql;

Code:

for (size_t i = 0; i < m_nCurrentRow; i++)
{

    if (i == 0)
        ss  << i << "," << i;
    else
        ss << "(" << i << "," << i;

    if (i < m_nCurrentRow - 1)
        ss << "),";
}

C.prepare("prep2", "select insertintoboosted($1::text)");

W.prepared("prep2")(ss).exec();

III. With parameter bindings and execution for each time:

std::string m_insertCommand3 = "INSERT INTO tableforperftest(column1, column2) VALUES ($1, $2)";
C.prepare("insert_into_db3", m_insertCommand3);
for (size_t i = 0; i < m_nCurrentRow; i++)
{
    W.prepared("insert_into_db3")(i)(i).exec();
}

To compare the solutions with 32767 inserts:

Invocation:                              --> Elapsed:    0.250292s
Stored Proc:                             --> Elapsed:    0.154507s 
Parameter binding + execution each time: --> Elapsed:    29.5566s

score 1 · Answer 2 · answered Jan 15 '18 at 11:10

1

pqxx::connection c;
pqxx::work w(c);
c.prepare("prep", "select stored_proc($1::text[])");
auto r = w.prepared("prep")("{v1, v2}").exec();

answered Jan 15 '18 at 11:10

ilya b.

443
5
9

1

Thank you for this code snippet, which might provide some limited, immediate help. A [proper explanation](https://meta.stackexchange.com/q/114762) would greatly improve its long-term value by showing why this is a good solution to the problem and would make it more useful to future readers with other, similar questions. Please [edit](https://meta.stackoverflow.com/posts/360251/edit) your answer to add some explanation, including the assumptions you’ve made. [ref](https://meta.stackoverflow.com/a/360251/8371915) – Alper t. Turker Jan 15 '18 at 14:05

libpqxx: How to bind array of parameters

2 Answers2