fast search + QThread

[solook]

Hi All,
i want to search in large libraries with sql
about 20000 books;
each book is in one table and all book names stored in table named books;

my code for search is :

void MyThread::run()
{
    QStringList bookTables =db->getList("Select booktable From books;");
    QStringList bookNames =db->getList("Select bookNames From books;");
    int rowBook=0;
    int totalResultBooks=0;
    int totalResult=0;
    int allBookCount=bookTables.size();
    emit sendMaxProgress(allBookCount-1);
    while(rowBook<allBookCount )
    {
        QString query = QString("Select count(id) From b%1 Where text LIKE '%%2%'  Limit 0,1 ; ").arg(bookTables.at(rowBook)).arg(searchPatterned);
        QString resultBook =db->getStr(query);
        if(resultBook.toInt()>0)
       {
            totalResult+= resultBook.toInt();
            totalResultBooks++;
            QListWidgetItem *item = new QListWidgetItem;
            item->setText(bookNames.at(rowBook)+" ("+resultBook+") ");
            item->setData(12,bookTables.at(rowBook));
            QString str = "found "+QString::number(totalResult)+"at"+QString::number(totalResultBooks)+"Books";
            emit sendResult(item,str,rowBook);
       }
        msleep(40);
        rowBook++;
    }
 
}

void MyThread::run()
{
    QStringList bookTables =db->getList("Select booktable From books;");
    QStringList bookNames =db->getList("Select bookNames From books;");
    int rowBook=0;
    int totalResultBooks=0;
    int totalResult=0;
    int allBookCount=bookTables.size();
    emit sendMaxProgress(allBookCount-1);
    while(rowBook<allBookCount )
    {
        QString query = QString("Select count(id) From b%1 Where text LIKE '%%2%'  Limit 0,1 ; ").arg(bookTables.at(rowBook)).arg(searchPatterned);
        QString resultBook =db->getStr(query);
        if(resultBook.toInt()>0)
       {
            totalResult+= resultBook.toInt();
            totalResultBooks++;
            QListWidgetItem *item = new QListWidgetItem;
            item->setText(bookNames.at(rowBook)+" ("+resultBook+") ");
            item->setData(12,bookTables.at(rowBook));
            QString str = "found "+QString::number(totalResult)+"at"+QString::number(totalResultBooks)+"Books";
            emit sendResult(item,str,rowBook);
       }
        msleep(40);
        rowBook++;
    }

}

To copy to clipboard, switch view to plain text mode 

my question is :
i used :

msleep(40);

msleep(40);

To copy to clipboard, switch view to plain text mode 

to free 20% of cpu ..

is this way right?
is there any better way ?

[wysota]

First of all you cannot use the same database connection in more than one thread, and that's what you are doing here. Furthermore, with msleep(40) you are not "saving" CPU power, you're just making your search slower. Third of all, you can't access the GUI from within a worker thread and that's what you are doing too.

[ChrisW67]

The search for free text in a large number of books would be greatly assisted by putting them all in one table and using Sqlite's full text search extension (you will need to enable this in Sqlite yourself: plenty of Google sources). It will improve performance so dramatically you will likely not feel the need to farm the work out into a thread (with all the problems that entails) .

Even if you did not use the FTS extension, putting all the books into a single table would still be a performance improvement: one query to prepare and execute versus 20000+.

Random thought: What is the point of a LIMIT clause on a query that can only ever return one row?

[solook]

with msleep(40) you are not "saving" CPU power, you're just making your search slower. Third of all, you can't access the GUI from within a worker thread and that's what you are doing too.

i can access to gui within search in linux but in windows can not access to gui ..

however help me how use multi threaded for that code

---

Added after 13 minutes:

The search for free text in a large number of books would be greatly assisted by putting them all in one table and using Sqlite's full text search extension (you will need to enable this in Sqlite yourself: plenty of Google sources). It will improve performance so dramatically you will likely not feel the need to farm the work out into a thread (with all the problems that entails) .

Even if you did not use the FTS extension, putting all the books into a single table would still be a performance improvement: one query to prepare and execute versus 20000+.

the size of my book in sqlite is larger than 15 gig !
is it goode way to store all books in one table ?
also i want to use regexp faction in query and i don't think fts can do that .

Random thought: What is the point of a LIMIT clause on a query that can only ever return one row?

it jast find matched name books
by clicking in book in list item find all result by this query :
QString("Select text,page From b%1 Where text REGEXP '%2' ; ").arg(item->data(12).toString()).arg(searchPatterned);

[ChrisW67]

the size of my book in sqlite is larger than 15 gig !

So?

is it goode way to store all books in one table ?

They're all in the one Sqlite file already aren't they? Sqlite will have no issue with a single table of that size: the default database limits are typically 1 billion 1k pages and a table can use all that.

also i want to use regexp faction in query and i don't think fts can do that .

Your examples doesn't show that you wanted to use a regular expression. Sqlite doesn't have a regular expression matching ability by default; you have to provide an implementation of that yourself. See http://stackoverflow.com/questions/5...a-sqlite-query.

FTS has a boolean search language that's quite capable but only you know your actual requirement. You still benefit from only executing one query rather than 20000 either way.


Line 12 of your example is a select returning the count(*) aggregate without grouping of any sort. This will always return one row and only one row, but you have a limit clause.

[solook]

thanks..

They're all in the one Sqlite file already aren't they? Sqlite will have no issue with a single table of that size: the default database limits are typically 1 billion 1k pages and a table can use all that.

i tested but this error returned :

database disk image is malformed

database disk image is malformed

To copy to clipboard, switch view to plain text mode 

Your examples doesn't show that you wanted to use a regular expression. Sqlite doesn't have a regular expression matching ability by default; you have to provide an implementation of that yourself. See http://stackoverflow.com/questions/5...a-sqlite-query.

i know Sqlite doesn't have a regular expression by default
i enabled it by myself

QString query = QString("Select count(id) From b%1 Where text REGEXP '%%2%' Limit 0,1 ; ").arg(bookTables.at(rowBook)).arg(searchPatterned);

QString query = QString("Select count(id) From b%1 Where text REGEXP '%%2%' Limit 0,1 ; ").arg(bookTables.at(rowBook)).arg(searchPatterned);

To copy to clipboard, switch view to plain text mode 

Line 12 of your example is a select returning the count(*) aggregate without grouping of any sort. This will always return one row and only one row, but you have a limit clause.

i don't want to show all result in each book
but just find that matched book name and count of result
then by clicking in book list find all result of selected book :

QString("Select text,page From b%1 Where text REGEXP '%2' ; ").arg(item->data(12).toString()).arg(searchPatterned);