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?

For those who are struggling with new version of pqxx which has no pqxx::prepare::invocation class in the library, here is the solution: stackoverflow.com/a/66832803/2395663 — Oğuzhan Türk
– Oğuzhan Türk, Commented Mar 27, 2021 at 15:58

Csuszmusz · Accepted Answer · 2018-05-26 12:03:55Z

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

ilya b. · Accepted Answer · 2018-01-15 11:10:55Z

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, 2018 at 11:10

ilya b.

4435 silver badges10 bronze badges

1 Comment

Alper t. Turker Over a year ago

Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation 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 your answer to add some explanation, including the assumptions you’ve made. ref

Collectives™ on Stack Overflow

libpqxx: How to bind array of parameters

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related