StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Query minimizing/caching in a C++ application
text
Body
copied!<p>I'm writing a project in <strong>C++/Qt</strong> and it is able to connect to any type of <strong>SQL</strong> database supported by the QtSQL (<a href="http://doc.qt.nokia.com/latest/qtsql.html" rel="nofollow noreferrer">http://doc.qt.nokia.com/latest/qtsql.html</a>). This includes <strong>local servers and external</strong> ones.</p> <p>However, when the database in question is external, the speed of the queries starts to become a problem (slow UI, ...). <strong>The reason</strong>: Every object that is stored in the database is <strong>lazy-loaded</strong> and as such will issue a query every time an attribute is needed. On average about 20 of these objects are to be displayed on screen, each of them showing about 5 attributes. This means that for every screen that I show about 100 queries get executed. The queries execute quite fast on the database server itself, but the overhead of the actual query running over the network is considerable (measured in seconds for an entire screen).</p> <p>I've been thinking about a few ways to solve the issue, the most important approaches seem to be (according to me):</p> <ol> <li>Make fewer queries</li> <li>Make queries faster</li> </ol> <h3>Tackling (1)</h3> <ul> <li>I could find some sort of way to delay the actual fetching of the attribute (start a <em>transaction</em>), and then when the programmer writes <em>endTransaction()</em> the database tries to fetch everything in one go (with SQL UNION or a loop...). This would probably require quite a bit of modification to the way the lazy objects work but if people comment that it is a decent solution I think it could be worked out elegantly. If this solution speeds up everything enough then an elaborate caching scheme might not even be necessary, saving a lot of headaches</li> <li>I could try pre-loading attribute data by fetching it all in one query for all the objects that are requested, effectively making them <strong>non-lazy</strong>. Of course in that case I will have to worry about stale data. How would I detect stale data without at least sending one query to the external db? (<em>Note: sending a query to check for stale data for every attribute check would provide a best-case 0x performance increase and a worst-caste 2x performance decrease when the data is actually found to be stale</em>) </li> </ul> <h3>Tackling (2)</h3> <p>Queries could for example be made faster by keeping a <strong>local synchronized copy</strong> of the database running. However I don't really have a lot of possibilities on the client machines to run for example exactly the same database type as the one on the server. So the local copy would for example be an SQLite database. This would also mean that I couldn't use an db-vendor specific solution. <strong>What are my options here? What has worked well for people in these kinds of situations?</strong></p> <h3>Worries</h3> <p>My primary worries are:</p> <ul> <li><strong>Stale data</strong>: there are plenty of queries imaginable that change the db in such a way that it prohibits an action that would seem possible to a user with stale data.</li> <li><strong>Maintainability</strong>: How loosely can I couple in this new layer? It would obviously be preferable if it didn't have to know everything about my internal lazy object system and about every object and possible query</li> </ul> <h3>Final question</h3> <p>What would be a good way to minimize the cost of making a query? Good meaning some sort of combination of: maintainable, easy to implement, not too aplication specific. If it comes down to pick any 2, then so be it. I'd like to hear people talk about their experiences and what they did to solve it.</p> <p>As you can see, I've thought of some problems and ways of handling it, but I'm at a loss for what would constitute a sensible approach. Since it will probable involve quite a lot of work and intensive changes to many layers in the program (hopefully as few as possible), I thought about asking all the experts here before making a final decision on the matter. It is also possible I'm just overlooking a very simple solution, in which case a pointer to it would be much appreciated!</p> <p><em>Assuming all relevant server-side tuning has been done (for example: MySQL cache, best possible indexes, ...)</em></p> <p>*Note: I've checked questions of users with similar problems that didn't entirely satisfy my question: <a href="https://stackoverflow.com/questions/2330477/suggestion-on-a-replication-scheme-for-my-use-case">Suggestion on a replication scheme for my use-case?</a> and <a href="https://stackoverflow.com/questions/2217786/best-practice-for-a-local-database-cache">Best practice for a local database cache?</a> for example)</p> <p>If any additional information is necessary to provide an answer, please let me know and I will duly update my question. Apologies for any spelling/grammar errors, english is not my native language.</p> <h3>Note about "lazy"</h3> <p>A small example of what my code looks like (simplified of course):</p> <pre><code>QList<MyObject> myObjects = database->getObjects(20, 40); // fetch and construct object 20 to 40 from the db // ...some time later // screen filling time! foreach (const MyObject& o, myObjects) { o->getInt("status", 0); // == db request o->getString("comment", "no comment!"); // == db request // about 3 more of these } </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