Databases
StackOverflow2013
Postsdbo
POOptimize QSql of memory database in multiprocessor-thread
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOptimize QSql of memory database in multiprocessor-thread
    text
    copied!<p>Here's my problem, I have one <code>sqlite</code> <code>memory database</code> by using <code>QSql</code>. I have several threads each handling one different table of this common database. And I use <code>Win API</code> to make sure these threads working on different processor, like this:</p> <pre class="lang-c prettyprint-override"><code>SetThreadAffinityMask (hThread, processorMask); </code></pre> <p>When there's only thread handling one table, it takes 10 seconds and uses 25% of total CPU. But when there're 4 threads handling 4 different tables, it takes nearly 40 seconds and uses only 35% of total CPU. I think the reason is there's some kind of <code>thread-safe</code> sync in one database. But due to different thread reading or writing different table, thread-safe slows my program. How can I optimize it.</p> <p><strong>Update:</strong> The most possible reason is some kinds of lock inside of <code>Qt</code> or/and <code>Sqlite 3</code> slows my program, so is it possible to shutdown or bypass these locks by pre-setting.</p> <p><strong>Update2:</strong> Here's an example. (Maybe a little long, sorry)</p> <pre class="lang-c prettyprint-override"><code>class MultiProcessorThread { public: virtual void run(); bool start() { m_hThread = CreateThread (NULL, 0, MultiProcessorThread::ThreadFunc, this, CREATE_SUSPENDED, NULL); if (m_hThread != INVALID_HANDLE_VALUE) { RunningThreadCount++; m_ProcessorMask = 1 &lt;&lt; ( (RunningThreadCount - 1) % ProcessorCount); SetThreadAffinityMask (m_hThread, m_ProcessorMask); // Make thread working on different processor ResumeThread (m_hThread); return true; } else return false; } protected: static DWORD WINAPI ThreadFunc (LPVOID in); HANDLE m_hThread; DWORD_PTR m_ProcessorMask; static DWORD_PTR ProcessorCount; static DWORD_PTR RunningThreadCount; static DWORD_PTR GetNumCPUs(); }; DWORD_PTR MultiProcessorThread::ProcessorCount = GetNumCPUs(); DWORD_PTR MultiProcessorThread::RunningThreadCount = 0; DWORD_PTR MultiProcessorThread::GetNumCPUs() // Get how many processors on this PC { SYSTEM_INFO m_si = {0}; GetSystemInfo (&amp;m_si); return (DWORD_PTR) m_si.dwNumberOfProcessors; } DWORD WINAPI MultiProcessorThread::ThreadFunc (LPVOID in) { static_cast&lt;MultiProcessorThread*&gt; (in)-&gt;run(); return 0; } class Run : public MultiProcessorThread { public: void run() { int i = 0; QString add = "insert into %1 values(1)"; add = add.arg (table); QString sel = "select a from %1 "; sel = sel.arg (table); QString del = "delete from %1 where a=1"; del = del.arg (table); while (++i) // read and write database { query.exec (add); query.exec (sel); query.exec (del); } } QSqlQuery query; QString table; }; int main (int argc, char *argv[]) { QCoreApplication a (argc, argv); QSqlDatabase db = QSqlDatabase::addDatabase ("QSQLITE", "test"); db.setDatabaseName (":memory:"); // All threads working on the same memory database. db.open(); QSqlQuery q (db), q1 (db), q2 (db); q.exec ("create table A (a)"); q1.exec ("create table B (a)"); q2.exec ("create table C (a)"); // All threads working on different table. Run b[3]; b[0].query = QSqlQuery (q); b[0].table = "A"; b[1].query = QSqlQuery (q1); b[1].table = "B"; b[2].query = QSqlQuery (q2); b[2].table = "C"; b[0].start(); b[1].start(); b[2].start(); return a.exec(); } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload