Qt 10000 inserts

I want to make above 10000 inserts in database. I use this simple code:

    int i = 0;
    for (i = 0; i < model-开发者_如何学运维>rowCount(); i++)
    {
        query.clear();
        query.prepare("INSERT INTO item (title, x, y, z) VALUES (:title, "
                      ":x, :y, :z);");
        query.bindValue(":title", title);
        query.bindValue(":x", model->data(model->index(i, 0)));
        query.bindValue(":y", model->data(model->index(i, 1)));
        query.bindValue(":z", model->data(model->index(i, 2)));

        if (! query.exec())
            return;
    }

but it is too slow while too many record are in model found. How can I speed it up? May be there are some packet insertion abilities available in Qt?

I am using: Qt 4.7.0, PostgreSQL 8.4.8 and Qt SQL driver.

---

Just prepare once, execute many? I'm not at all familiar with QT, but this might speed the whole process up:

int i = 0;
query.prepare("INSERT INTO item (title, x, y, z) VALUES (:title, "
   ":x, :y, :z);");

for (i = 0; i < model->rowCount(); i++)
{
    query.bindValue(":title", title);
    query.bindValue(":x", model->data(model->index(i, 0)));
    query.bindValue(":y", model->data(model->index(i, 1)));
    query.bindValue(":z", model->data(model->index(i, 2)));

    if (! query.exec())
        return;
}

---

Create a transaction, insert the values, then commit.

---

@Berry Langerak's solution is the correct one for the Qt side of things.... if you've successfully started a transaction.

Failing that, you might want to look in to COPY, but I don't think that's your problem.

A quick change to your postgresql.conf settings might be a quick fix if you can't figure out how to BEGIN a transaction. If you are outside of a transaction, every INSERT is resulting in an fsync(2) call.

synchronous_commit = off

There's a host of performance tuning information online if you find that PostgreSQL is the bottleneck, but I'm guessing you're doing your work out of a transaction.