[SOLVED] QtSql database execution SLOW

[Chrisw01]

Hi everyone, I wish to pull a json file from the web and stick its content into a SQL database of type QSQLITE which I have done successfully its just super slow. I have read that you can nest the commands within a 'begin' and a 'end' statement to eliminate repetitive executions here is a snip of one of the loops.

@
for(int loop2 = 0; loop2 < (int)(sizeof(displayLocationElements) / sizeof(QString)); loop2++) {
QString str("display_location.");
str.append(displayLocationElements[loop2]);
sql.bindValue(":var", str);
sql.bindValue(":value",QString(jsonSubMap[displayLocationElements[loop2]].toString()));
sql.exec();
}
@

If I comment out all the sql commands it takes less than 1/2 seconds to go through this whole function parsing the json code, with the sql commands it takes 12-14 seconds which I think is terrible since there is only 60-70 elements being processed. What is the best way to process all the data and only execute once completed.

Thanks in advance...

Chris

[JKSH]

Hi,

You already mentioned the solution:
[quote author="Chrisw01" date="1399996287"]you can nest the commands within a 'begin' and a 'end' statement to eliminate repetitive executions[/quote]Add BEGIN and END to your code:
@
sql.exec("BEGIN");
for(...) ...
sql.exec("END");
@

It has nothing to do with Qt SQL. SQLite transactions are very slow (see http://www.sqlite.org/faq.html#q19 ). If you use BEGIN and END, you only make one transaction to INSERT everything. If you don't, then you make many transactions, because every INSERT makes a new transaction.

[Chrisw01]

Thanks for pointing that out, I only had 1/2 the answer, when I used the BEGIN/END part I had commented out the internal execs and it wasn't updating the database, you still have to call .exec within the for loop it just doesn't process the transactions until the END is called.