Load big database into model to be shown in table view

Davide Guimaraes

Hi,
I'm using QSqlTableModel to load data from database (lots of records with images) and show it in QTableView. The problem is that when fetching more data, it loads to memory, and eventually it produces a memory leak. So i want to load just a part of data to be displayed. Is there a way to fetch just a block of data using QSqlTableModel?
I even tried to make my own model derived from QAbstractTableModel which has its own cache QVector<QSqlRecord> containing a set of records depending on tableView.verticalScrollBar. The problem remained the same. I set QSqlQuery::setForwardOnly(true) but with this i can seek previous records without querying again the database.
What is the best approach for this?
Thanks in advance,
Davide

Christian Ehrlicher

@Davide-Guimaraes said in Load big database into model to be shown in table view:

and eventually it produces a memory leak.

It does not.

Is there a way to fetch just a block of data using QSqlTableModel?

No, you have to write it by your own as you already did.

saulos

You probably need to manually page the data you get from the DB, like
select * from tablexx where id >x and id <y
where x - y is the row you display in the table view.

Davide Guimaraes

@Christian-Ehrlicher said in Load big database into model to be shown in table view:

No, you have to write it by your own as you already did.

In my model i have the same problem. I store the records (QSqlRecord) in a vector and cleaning the vector, the data is still stored in memory. It can be solve by using setForwardOnly(true) but i can't used it as i want to access previous data.

@saulos said in Load big database into model to be shown in table view:

You probably need to manually page the data you get from the DB

I already thought about using pagination but i prefer to have a full table view instead.

Christian Ehrlicher

@Davide-Guimaraes said in Load big database into model to be shown in table view:

the data is still stored in memory.

No except you do something wrong. Please provide a minimal example to show a memleak here.

Davide Guimaraes

@Christian-Ehrlicher said in Load big database into model to be shown in table view:

No except you do something wrong. Please provide a minimal example to show a memleak here.

Here is part of the small project that i created. Using this the application memory increased every time it needs to fetch more data. QSqlQuery don't free the allocated memory

QObject::connect(view->verticalScrollBar(), SIGNAL(valueChanged(int)), model, SLOT(scrollBarChanged(int)));
--
class MyTableModel : public QAbstractTableModel
{
    Q_OBJECT
    int currentOffset;
    QSqlQuery* selectQuery;
    QVector<QSqlRecord> recordList;
    ...
}
--
MyTableModel::MyTableModel() : currentOffset(-1)
{
    selectQuery = new QSqlQuery();
    //selectQuery->setForwardOnly(true);
    selectQuery->exec("select * from person");
    scrollBarChanged(0);
}
QVariant MyTableModel::data(const QModelIndex& _index, int _role) const
{
    int row = _index.row() - currentOffset;
    if (_role == Qt::DisplayRole)
    {
        return recordList.at(row).value(_index.column());
    }
    return QVariant();
}
Q_SLOT void MyTableModel::scrollBarChanged(int _value)
{
    if (currentOffset == _value)
    {
        return;
    }
    int endValue = qMin(_value + CACHESIZE, rowCount());
    emit layoutAboutToBeChanged();
    recordList.clear();
    selectQuery->seek(_value);
    for(int i = _value; i < endValue-1; i++)
    {
        recordList.append(selectQuery->record());
        selectQuery->next();
    }
    currentOffset = _value;
    emit layoutChanged();
}

JonB

@Davide-Guimaraes
I can see you clearing your own recordList. However, as you go selectQuery->next() the query is still left with all the previously-read records, so memory used increases, what else do you expect?

It can be solve by using setForwardOnly(true) but i can't used it as i want to access previous data.

Yep, it's one or the other. If you want to go back to previous buffers-worth, you need those records retained either in the query or in your copied record list, and that occupies memory.

Christian Ehrlicher

You have to recreate your query every time and have to remember which records are in the needed range. Then only query those records.