Fix hanging when deleting large number of records (sqlitebrowser#856)

When trying to delete all records or large number of them (~5000) (selecting them and pressing Delete Record on Data tab), it takes too much time to execute this operation due to large number of queries like `DELETE * FROM 'table' WHERE '_rowid_' = id`.
prutz1311 · Nov 16, 2016 · 3265c84 · revolter · Dec 21, 2016 · 3265c84
1 parent c6f1373
commit 3265c84
Show file tree

Hide file tree

Showing 4 changed files with 22 additions and 13 deletions.
diff --git a/src/MainWindow.cpp b/src/MainWindow.cpp
@@ -585,8 +585,11 @@ void MainWindow::deleteRecord()
 
         int old_row = ui->dataTable->currentIndex().row();
         while(ui->dataTable->selectionModel()->hasSelection())
-        {
-            if(!m_browseTableModel->removeRow(ui->dataTable->selectionModel()->selectedIndexes().first().row()))
+	{
+	    int first_selected_row = ui->dataTable->selectionModel()->selectedIndexes().first().row();
+	    int last_selected_row = ui->dataTable->selectionModel()->selectedIndexes().last().row();
+	    int selected_rows_count = last_selected_row - first_selected_row + 1;
+	    if(!m_browseTableModel->removeRows(first_selected_row, selected_rows_count))
             {
                 QMessageBox::warning(this, QApplication::applicationName(), tr("Error deleting record:\n%1").arg(db.lastErrorMessage));
                 break;

diff --git a/src/sqlitedb.cpp b/src/sqlitedb.cpp
@@ -865,15 +865,20 @@ QString DBBrowserDB::addRecord(const QString& sTableName)
     }
 }
 
-bool DBBrowserDB::deleteRecord(const QString& table, const QString& rowid)
+bool DBBrowserDB::deleteRecords(const QString& table, const QStringList& rowids)
 {
     if (!isOpen()) return false;
     bool ok = false;
 
-    QString statement = QString("DELETE FROM %1 WHERE %2='%3';")
+    QStringList quoted_rowids;
+    foreach(QString rowid, rowids)
+    {
+        quoted_rowids.append("'" + rowid + "'");
+    }
+    QString statement = QString("DELETE FROM %1 WHERE %2 IN (%3);")
             .arg(sqlb::escapeIdentifier(table))
             .arg(sqlb::escapeIdentifier(getObjectByName(table).table.rowidColumn()))
-            .arg(rowid);
+            .arg(quoted_rowids.join(", "));
     if(executeSQL(statement))
         ok = true;
     else

diff --git a/src/sqlitedb.h b/src/sqlitedb.h
@@ -85,7 +85,7 @@ class DBBrowserDB : public QObject
      * @return An sqlite conform INSERT INTO statement with empty values. (NULL,'',0)
      */
     QString emptyInsertStmt(const sqlb::Table& t, const QString& pk_value = QString()) const;
-    bool deleteRecord(const QString& table, const QString& rowid);
+    bool deleteRecords(const QString& table, const QStringList& rowids);
     bool updateRecord(const QString& table, const QString& column, const QString& rowid, const QByteArray& value, bool itsBlob);
 
     bool createTable(const QString& name, const sqlb::FieldVector& structure);

diff --git a/src/sqlitetablemodel.cpp b/src/sqlitetablemodel.cpp
@@ -410,15 +410,16 @@ bool SqliteTableModel::removeRows(int row, int count, const QModelIndex& parent)
 
     bool ok = true;
 
+    QStringList rowids;
     for(int i=count-1;i>=0;i--)
     {
-        if(m_db->deleteRecord(m_sTable, m_data.at(row + i).at(0)))
-        {
-            m_data.removeAt(row + i);
-            --m_rowCount;
-        } else {
-            ok = false;
-        }
+        rowids.append(m_data.at(row + i).at(0));
+        m_data.removeAt(row + i);
+        --m_rowCount;
+    }
+    if(!m_db->deleteRecords(m_sTable, rowids))
+    {
+	ok = false;
     }
 
     endRemoveRows();