Planned maintenance has been done but it did not solve the problem. So work will continue on this and a new time for trying updates will be announced asap.

SQLite and QTableView: Custom sorting is slow



  • Hello.

    My primary goal is simple - to show SQLite table on QTableView. My secondary goal is to add ability for user to move rows up and down. I call it custom sorting. So this is why I use special 'position' column for sorting. So to move any row up I need just to swap it's position with row above it. And it works. But the thing is it works very slow even for very simple table like in the example below.

    Question 1: Why is it so slow? Can it be faster?
    Question 2: Can you suggest better/faster/different approarch for custom sorting?

    Thanks in advance.

    My environment: Windows 10 x64, Qt 5.10.1 msvc2015 32bit

    UPD:
    Maybe it is better to give up on SQLite and use QTableWidget, std::map, data structures and serialization instead. It is not so convenient compared to SQL database but at least it should work fast.

    Sample code:

    /*
    File name: main.cpp
    
    Project file:
    QT       += core gui sql
    greaterThan(QT_MAJOR_VERSION, 4): QT += widgets
    TARGET = MySqlTest
    TEMPLATE = app
    DEFINES += QT_DEPRECATED_WARNINGS
    SOURCES += \main.cpp
    HEADERS +=
    */
    
    #include <QApplication>
    #include <QWidget>
    #include <QVBoxLayout>
    #include <QHBoxLayout>
    #include <QPushButton>
    #include <QTableView>
    #include <QHeaderView>
    #include <QSqlTableModel>
    #include <QSqlDatabase>
    #include <QSqlQuery>
    
    class MainWidget: public QWidget
    {
        Q_OBJECT
    public:
        MainWidget(QWidget* parent = Q_NULLPTR)
            : QWidget(parent)
        {
            // Setup database
            db = QSqlDatabase::addDatabase("QSQLITE");
            db.setDatabaseName("test.db");
            db.open();
            QSqlQuery query;
            query.exec("DROP TABLE items");
            query.exec(
                        "CREATE TABLE items ("
                            "id       INTEGER PRIMARY KEY UNIQUE NOT NULL,"
                            "position INTEGER NOT NULL,"
                            "name     VARCHAR NOT NULL DEFAULT ''"
                        ")"
                        );
            query.exec("INSERT INTO items ([position], name) VALUES (1, 'apple')");
            query.exec("INSERT INTO items ([position], name) VALUES (2, 'biscuit')");
            query.exec("INSERT INTO items ([position], name) VALUES (3, 'coconut')");
            query.exec("INSERT INTO items ([position], name) VALUES (4, 'donut')");
            query.exec("INSERT INTO items ([position], name) VALUES (5, 'egg')");
            query.exec("INSERT INTO items ([position], name) VALUES (6, 'fig')");
            query.exec("INSERT INTO items ([position], name) VALUES (7, 'garlic')");
            query.exec("INSERT INTO items ([position], name) VALUES (8, 'hamburger')");
    
            // Setup GUI
            setLayout(&layout);
            layout.addLayout(&layoutButtons);
            layoutButtons.addWidget(&buttonUp);
            buttonUp.setText("Move current up");
            connect(&buttonUp  , &QPushButton::clicked, this, &MainWidget::onMoveUp);
            connect(&buttonDown, &QPushButton::clicked, this, &MainWidget::onMoveDown);
            layoutButtons.addWidget(&buttonDown);
            buttonDown.setText("Move current down");
            layout.addWidget(&view);
    
            // Setup model and view
            model = new QSqlTableModel(this, db);
            model->setTable("items");
            model->setSort(1, Qt::AscendingOrder); // sort by position
            model->select();
            view.setModel(model);
            view.hideColumn(0); // hide id
            view.hideColumn(1); // hide position
            view.horizontalHeader()->hide();
            view.setSelectionMode(QAbstractItemView::SingleSelection);
            view.setSelectionBehavior(QAbstractItemView::SelectRows);
            model->setEditStrategy(QSqlTableModel::OnManualSubmit);
        }
        void onMoveUp()
        {
            int row = view.currentIndex().row();
            if (row > 0) // Can't move first row up
                swapRows(row, row-1);
        }
        void onMoveDown()
        {
            int row = view.currentIndex().row();
            if (row < model->rowCount()-1) // Can't move last row down
                swapRows(row, row+1);
        }
        void swapRows(int row1, int row2)
        {
            QModelIndex index1 = model->index(row1, 1);
            QModelIndex index2 = model->index(row2, 1);
            uint position1 = model->data(index1).toUInt();
            uint position2 = model->data(index2).toUInt();
            model->setData(index1, position2);
            model->setData(index2, position1);
            model->submitAll();
            view.setCurrentIndex(model->index(row2, 0));
        }
    private:
        QSqlDatabase db;
        QVBoxLayout layout;
        QHBoxLayout layoutButtons;
        QPushButton buttonUp;
        QPushButton buttonDown;
        QTableView view;
        QSqlTableModel* model;
    };
    
    #include "main.moc"
    
    int main(int argc, char *argv[])
    {
        QApplication a(argc, argv);
        MainWidget w;
        w.show();
    
        return a.exec();
    }
    
    


  • I created custom model which works fast enough. It fetches and writes data using queries. Seems like working solution.