PDA

View Full Version : Combobox Delegate 25.000 records



aekilic
23rd May 2008, 11:41
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
23rd May 2008, 12:55
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
23rd May 2008, 13:11
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
23rd May 2008, 14:13
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::WaitCu rsor));
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();
}

aekilic
24th May 2008, 13:24
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
24th May 2008, 17:53
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
27th July 2008, 23:28
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());

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;
}

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
28th July 2008, 09:37
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
28th July 2008, 21:02
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
29th July 2008, 13:26
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...