why is python faster than c ++ when inserting into a database?

Question

I have an array of 100 elements called "items"

Python (take about 1 sec):

for item in items:
           cur.execute("INSERT INTO list VALUES (?)",(item,))
db.commit()

C++ (9 sec):

for ( auto it = items.begin(); it != items.end(); ++it  ) {
    qry.prepare("INSERT INTO list VALUES (?)");
    std::string item = *it;
    qry.addBindValue(item);
    qry.exec();

C++ without prepare (9 sec):

for ( auto it = items.begin(); it != items.end(); ++it  ) {
    std::string item = *it;
    qry.exec("INSERT INTO list VALUES ('"+item+"')");

Basically my question is whether there is a way to use insert in C++ as fast as in Python.

The fact that you `commit` once at the end of your python and don't seem to be running in a transaction in c++ might account for the difference. — François Andrieux, Sep 13 '18 at 18:44
`qry.exec("INSERT INTO list VALUES (*item)");` isn't actually going to do what you want. Depending on what `*item` is you need something like: `qry.exec("INSERT INTO list VALUES (" + *item + ")");` — NathanOliver, Sep 13 '18 at 18:47
Remember, you can prepare once and bind/execute multiple times. — tadman, Sep 13 '18 at 18:57
Even the python method isn't optimised because you could be using `executemany`. I suspect your benchmark is flawed — roganjosh, Sep 13 '18 at 18:58
Your updated code is still flawed. `qry.exec("INSERT INTO list VALUES (item)");` will insert the word "item" for every record. You need `qry.exec("INSERT INTO list VALUES (" + item +")");` or possibly `qry.exec(("INSERT INTO list VALUES (" + item +")").c_str());` — NathanOliver, Sep 13 '18 at 19:01
You don't normally prepare a statement every time you are about to use it. Prepare it once, use many times. — Galik, Sep 13 '18 at 19:01
You may want to remove the database out of your profiling. Have each language version output the SQL text to a file. Measure. Next, take the SQL files and input them into the database, measure. Determine if you can benchmark the programs talking directly to the database and measure. My belief is that the speed doesn't have to do with the languages, but the interface or communications with the database. — Thomas Matthews, Sep 13 '18 at 19:17

score 2 · Accepted Answer · answered Sep 13 '18 at 22:50

2

This is a correct way for fast batch inserts in C++

Take less than 1 sec:

db.transaction();

for ( auto it = items.begin(); it != items.end(); ++it  ) {
    std::string item = *it;
    qry.exec("INSERT INTO list VALUES ('"+item+"')");
}

db.commit();

answered Sep 13 '18 at 22:50

Robin pilot

447
6
15

why is python faster than c ++ when inserting into a database?

1 Answers1