title: Improve performance with SQLite
body: <p>I programmed a C++ executable with Qt to calculate data based on a SQLite database and store it within the database again.
  With Qt I am executing a <code>SELECT</code>, calculating data and storing it again via <code>UPDATE</code>. Initially I tested this with chunks of about 5000 lines and it worked quite fast (ca. 1ms per line). Now that I implemented everything I need to apply the same calculation on databases with around 600000 lines. No the average time per line is around the factor 200 slower. While improving my code, I first verified, that its about the table size and not about calculation specifics and I therefore truncated data from the long table to 100000 and that is only the factor of 20 slower than the small table with 5000 lines.</p>
  
  <p>I already since the beginning have pragmas in the code to improve overall performance:</p>
  
  <pre><code>query.exec("PRAGMA page_size = 16384");
      query.exec("PRAGMA cache_size = 131072");
      query.exec("PRAGMA temp_store = MEMORY");
      query.exec("PRAGMA journal_mode = OFF");
      query.exec("PRAGMA locking_mode = EXCLUSIVE");
      query.exec("PRAGMA synchronous = OFF");
  </code></pre>
  
  <p>And I also tried to make use of transactions in bunches of 100 or 1000 lines, but that did not help:</p>
  
  <pre><code>database.transaction();
  ...
  database.commit();
  </code></pre>
  
  <p>Can anybody suggest what to do? I was already thinking about splitting database files, but is there an easy way to do?</p>
  
  <p><strong>EDIT:</strong>
  As requested, here is a simplified minimal example. The table layout is more complex in my real example (17 columns) and has around 600000 entries, nevertheless this introduces the problem quite well:</p>
  
  <blockquote>
    <p>widget.h</p>
  </blockquote>
  
  <pre><code>#ifndef WIDGET_H
  #define WIDGET_H
  
  #include &lt;QtCore&gt;
  #include &lt;QtGui&gt;
  #include &lt;QtWidgets&gt;
  #include &lt;QtSql&gt;
  
  class Widget : public QWidget
  {
      Q_OBJECT
  
  public:
      Widget(QWidget *parent = 0)
          : QWidget(parent)
      {
          QPushButton *createSmall = new QPushButton("Create Small");
          connect(createSmall, SIGNAL(clicked()), this, SLOT(createSmallDataBase()));
          QPushButton *createBig = new QPushButton("Create Big");
          connect(createBig, SIGNAL(clicked()), this, SLOT(createBigDataBase()));
          QPushButton *calculateSmall = new QPushButton("Calculate Small");
          connect(calculateSmall, SIGNAL(clicked()), this, SLOT(calculateSmallDataBase()));
          QPushButton *calculateBig = new QPushButton("Calculate Big");
          connect(calculateBig, SIGNAL(clicked()), this, SLOT(calculateBigDataBase()));
          QVBoxLayout *layout = new QVBoxLayout();
          layout-&gt;addWidget(createSmall);
          layout-&gt;addWidget(createBig);
          layout-&gt;addWidget(calculateSmall);
          layout-&gt;addWidget(calculateBig);
          this-&gt;setLayout(layout);
      }
  
      ~Widget()
      {
      }
  
      void createDataBase(quint32 size, QString name)
      {
          QSqlDatabase database;
          database = QSqlDatabase::addDatabase("QSQLITE");
          database.setDatabaseName(name);
          if(database.open())
          {
              QSqlQuery query(database);
              query.exec("PRAGMA page_size = 4096");
              query.exec("PRAGMA cache_size = 16384");
              query.exec("PRAGMA temp_store = MEMORY");
              query.exec("PRAGMA journal_mode = OFF");
              query.exec("PRAGMA locking_mode = EXCLUSIVE");
              query.exec("PRAGMA synchronous = OFF");
              qDebug() &lt;&lt; "DROP" &lt;&lt; query.exec("DROP TABLE Scenario");
              qDebug() &lt;&lt; "CREATE" &lt;&lt; query.exec("CREATE TABLE IF NOT EXISTS Scenario(id INTEGER, time REAL, prob REAL)");
              for(quint32 i = 0; i &lt; size; i++)
              {
                  query.exec(QString("INSERT INTO Scenario (id, time, prob) VALUES(%1, %2, %3)").arg(i).arg(i).arg(-1));
              }
          }
          database.close();
          database.removeDatabase("QSQLITE");
      }
  
      void calculateDataBase(QString name)
      {
          QSqlDatabase database;
          database = QSqlDatabase::addDatabase("QSQLITE");
          database.setDatabaseName(name);
          if(database.open())
          {
              QSqlQuery query(database);
              query.exec("PRAGMA page_size = 4096");
              query.exec("PRAGMA cache_size = 16384");
              query.exec("PRAGMA temp_store = MEMORY");
              query.exec("PRAGMA journal_mode = OFF");
              query.exec("PRAGMA locking_mode = EXCLUSIVE");
              query.exec("PRAGMA synchronous = OFF");
  
              query.exec("SELECT MAX(id) FROM Scenario");
              quint32 maxID = 0;
  
              if(query.next())
              {
                  maxID = query.value(0).toUInt();
              }
  
              for(quint32 id = 0; id &lt;= maxID; id++)
              {
                  query.exec(QString("SELECT id, time, prob FROM Scenario WHERE id = %1").arg(QString::number(id)));
                  if(query.first())
                  {
                      double prob = query.value(0).toDouble();
                      query.exec(QString("UPDATE Scenario SET prob = %1 WHERE id = %2").arg(qSqrt(prob)).arg(QString::number(id)));
                  }
              }
          }
          database.close();
          database.removeDatabase("QSQLITE");
      }
  
  public slots:
      void createSmallDataBase()
      {
          QTime time;
          time.start();
          createDataBase(1000, "small.sqlite");
          qDebug() &lt;&lt; "Create Small" &lt;&lt; time.elapsed()/1000.0;
      }
      void createBigDataBase()
      {
          QTime time;
          time.start();
          createDataBase(10000, "big.sqlite");
          qDebug() &lt;&lt; "Create Big" &lt;&lt; time.elapsed()/10000.0;
      }
      void calculateSmallDataBase()
      {
          QTime time;
          time.start();
          calculateDataBase("small.sqlite");
          qDebug() &lt;&lt; "Calculate Small" &lt;&lt; time.elapsed()/1000.0;
      }
      void calculateBigDataBase()
      {
          QTime time;
          time.start();
          calculateDataBase("big.sqlite");
          qDebug() &lt;&lt; "Calculate Big" &lt;&lt; time.elapsed()/10000.0;
      }
  };
  
  #endif // WIDGET_H
  </code></pre>
  
  <blockquote>
    <p>main.cpp</p>
  </blockquote>
  
  <pre><code>#include &lt;QApplication&gt;
  
  #include "widget.h"
  
  int main(int argc, char *argv[])
  {
      QApplication a(argc, argv);
      Widget w;
      w.show();
  
      return a.exec();
  }
  </code></pre>
  
  <p>On my machine the difference between calling <code>calculateSmallDataBase()</code> and <code>calculateBigDataBase()</code> is from 0.518ms/line to 3.0417ms/line and this is only from 1000 to 10000 lines! So I am already reaching factor 6 in between these.</p>
  
  <p>Looking forward to your suggestions.</p>