Combobox Delegate 25.000 records

[aekilic]

Dear All

I am having a problem with a combo box that I use in a tablewidget with a delegate. Combobox has the contents from a sql quary that has 25.000 lines of records. When I press the column in the table that has the combo, it takes nearly 10 sec to display the combo.

Does any body could help for to make it faster!

[patrik08]

Dear All
Combobox has the contents from a sql quary that has 25.000 lines of records.
Does any body could help for to make it faster!

From mysql or sqlite?

I have a 15000 contact mysql table if i run from from intranet the loading is 3-4 second
if i copy on a ( therad process ) mysql table on a sqlite3 memory table it display on 1 second..

[aekilic]

I use postgres, but the solution could be help full, could you please tell me how you did it?

I am a bit new to Qt...

[patrik08]

I write the network table to sqlite :memory: ram..

Simply here a sample ....
ad work on 2 connection cache & intranet

/* open big database !!!! */
    /* i use sheme = "mysql://username:pass@host:port/database"  */
    QUrl dns(sheme,QUrl::TolerantMode);
    const QString dbase = dns.path().replace("/","");
    QStringList drivers = QSqlDatabase::drivers();
    type = "Q"+dns.scheme().toUpper();
	  std::cout << "######### type   " << qPrintable(type ) << "\n" << std::endl;
    if (!drivers.contains(type)) {
    std::cout << "######### " << qPrintable(tr("Unable to Load Driver %1").arg(type)) << "\n" << std::endl;
    return false;      
    }
    ////  .....user port and so .............
		db = QSqlDatabase::addDatabase(type,QString("master_mysql_%1").arg(trac));
    /* open QSQLITE on ram */
    cachedb = QSqlDatabase::addDatabase("QSQLITE");
    cachedb.setDatabaseName(":memory:");
     if (!cachedb.open()) {
        QMessageBox::critical(0, qApp->tr("Cannot open local database"),
            qApp->tr("Unable to establish a cache database connection.\n\n"
                     "Click Cancel to exit."), QMessageBox::Cancel);
        return false;
    }
 
    LoadCache();
 
  /* fill memory table to quick load and runn query */
  /* update i send to db mysql if needed relaod LoadCache() */
void DB_Manager::LoadCache()
{
    QSqlQuery query("",cachedb);
    QApplication::setOverrideCursor(QCursor(Qt::WaitCursor));
    query.exec("DROP TABLE IF EXISTS catememo");
    query.exec("create table catememo (id INTEGER PRIMARY KEY AUTOINCREMENT,root_id INTEGER,name varchar(110),lft INTEGER,rgt INTEGER,oldid INTEGER,xmlattribute BLOB)");
    QStringList line_data;
    QSqlQueryModel *mod = new QSqlQueryModel();
    mod->setQuery(QString("SELECT * FROM %1 ").arg(TABELLA_CATES),current());
    const int summline = mod->rowCount();
 
    for (int e = 0; e < summline; ++e) {
        line_data.clear();
        QSqlRecord r = mod->record(e);
               for (int x = 0; x < mod->columnCount(); ++x)  {
                   QString cellTxt = "'"+G_Quote(r.value(x).toString())+"'";
                   line_data.append(cellTxt);
               }
        QString cainsert = QString("insert into catememo values (%1)").arg(line_data.join(","));
        bool mcis = query.exec(cainsert);
    }
 
    QApplication::restoreOverrideCursor(); 
}

/* open big database !!!! */
    /* i use sheme = "mysql://username:pass@host:port/database"  */
    QUrl dns(sheme,QUrl::TolerantMode);
    const QString dbase = dns.path().replace("/","");
    QStringList drivers = QSqlDatabase::drivers();
    type = "Q"+dns.scheme().toUpper();
	  std::cout << "######### type   " << qPrintable(type ) << "\n" << std::endl;
    if (!drivers.contains(type)) {
    std::cout << "######### " << qPrintable(tr("Unable to Load Driver %1").arg(type)) << "\n" << std::endl;
    return false;      
    }
    ////  .....user port and so .............
		db = QSqlDatabase::addDatabase(type,QString("master_mysql_%1").arg(trac));
    /* open QSQLITE on ram */
    cachedb = QSqlDatabase::addDatabase("QSQLITE");
    cachedb.setDatabaseName(":memory:");
     if (!cachedb.open()) {
        QMessageBox::critical(0, qApp->tr("Cannot open local database"),
            qApp->tr("Unable to establish a cache database connection.\n\n"
                     "Click Cancel to exit."), QMessageBox::Cancel);
        return false;
    }
    
    LoadCache();
    
  /* fill memory table to quick load and runn query */
  /* update i send to db mysql if needed relaod LoadCache() */
void DB_Manager::LoadCache()
{
    QSqlQuery query("",cachedb);
    QApplication::setOverrideCursor(QCursor(Qt::WaitCursor));
    query.exec("DROP TABLE IF EXISTS catememo");
    query.exec("create table catememo (id INTEGER PRIMARY KEY AUTOINCREMENT,root_id INTEGER,name varchar(110),lft INTEGER,rgt INTEGER,oldid INTEGER,xmlattribute BLOB)");
    QStringList line_data;
    QSqlQueryModel *mod = new QSqlQueryModel();
    mod->setQuery(QString("SELECT * FROM %1 ").arg(TABELLA_CATES),current());
    const int summline = mod->rowCount();
    
    for (int e = 0; e < summline; ++e) {
        line_data.clear();
        QSqlRecord r = mod->record(e);
               for (int x = 0; x < mod->columnCount(); ++x)  {
                   QString cellTxt = "'"+G_Quote(r.value(x).toString())+"'";
                   line_data.append(cellTxt);
               }
        QString cainsert = QString("insert into catememo values (%1)").arg(line_data.join(","));
        bool mcis = query.exec(cainsert);
    }
    
    QApplication::restoreOverrideCursor(); 
}

To copy to clipboard, switch view to plain text mode 

[aekilic]

Should I have to write this to delegate?

Second thing is first you disconnect with the current server and than you copy them to sqlite?

[patrik08]

Should I have to write this to delegate?

No, i have make a extra DBconnect manager to call other utility
after first connect i fill cache...

Second thing is first you disconnect with the current server and than you copy them to sqlite?

If connection is open from server you can reload table any time...


a extra DB manager i send instance to other widged or class to manage and fill combobox or export mysql dump ecc..


here is a piece on dir
http://ppk.ciz.ch/qt_c++/cms_layer/
http://ppk.ciz.ch/qt_c++/cms_layer/DB_Manager.h
http://ppk.ciz.ch/qt_c++/cms_layer/DB_Manager.cpp

[aekilic]

Hello everybody,

I have connected 2 database for the program,

db = QSqlDatabase::addDatabase("QPSQL");
 		db.setHostName("10.0.0.11");
 		db.setPort(5432);
		db.setDatabaseName(comboDonem->currentText());
		db.setUserName(lineKullanici->text());
		db.setPassword(lineSifre->text());

db = QSqlDatabase::addDatabase("QPSQL");
 		db.setHostName("10.0.0.11");
 		db.setPort(5432);
		db.setDatabaseName(comboDonem->currentText());
		db.setUserName(lineKullanici->text());
		db.setPassword(lineSifre->text());

To copy to clipboard, switch view to plain text mode 

and the other one is

QSqlDatabase::database("in_mem_db", false).close();
	QSqlDatabase::removeDatabase("in_mem_db");
	cachedb = QSqlDatabase::addDatabase("QSQLITE", "in_mem_db");
	cachedb.setDatabaseName(":memory:");
	if (!cachedb.open())
	{
		QMessageBox::warning(this, tr("Unable to open database"), tr("An error occured while opening the connection: ") + cachedb.lastError().text());
		return false;
	}

QSqlDatabase::database("in_mem_db", false).close();
	QSqlDatabase::removeDatabase("in_mem_db");
	cachedb = QSqlDatabase::addDatabase("QSQLITE", "in_mem_db");
	cachedb.setDatabaseName(":memory:");
	if (!cachedb.open())
	{
		QMessageBox::warning(this, tr("Unable to open database"), tr("An error occured while opening the connection: ") + cachedb.lastError().text());
		return false;
	}

To copy to clipboard, switch view to plain text mode 

I could load the cache and select from cache. But the problem I have is when I go to another page(another .h file), I could not connect to cache. I could only connect db. (PQSQL)

I want to know How could I connect the cache and select from there.

[patrik08]

simple point your cache connecion on query

QSqlQuery query("",cachedb);

like sql example

connect / query to first db is
QSqlQuery query();
connect / query to sqlite memory
QSqlQuery query("",cachedb);

you can now query to all db listed on
QStringList QSqlDatabase::connectionNames () [static]

if you not like to save db pointer:

QSqlDatabase QSqlDatabase::database ( const QString & connectionName = QLatin1String( defaultConnection ), bool open = true ) [static]

[aekilic]

Dear Patric

Thank you for your reply again, but it didnt solve my problem actually.

Should I have to connect to cachedb on main.cpp?

Because what I do is on main cpp starts a entrance .ui which you log on the postgresql. In which i also connect to cachedb. After that you go to the mainpage.h. The problem I have is I could not connect to cache in the mainpage.h

[aekilic]

Dear Patric,

I am trying mybest. But when I close the form that I created cachedb, and open another form, I lost connectiong to the cachedb. But default db connection(PQSQL) stays. Please help me...