<html>
<body>
<div style="font-family: Verdana, Arial, Helvetica, Sans-Serif;">
<table bgcolor="#f9f3c9" width="100%" cellpadding="12" style="border: 1px #c9c399 solid; border-radius: 6px; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tr>
<td>
This is an automatically generated e-mail. To reply, visit:
<a href="https://git.reviewboard.kde.org/r/119647/">https://git.reviewboard.kde.org/r/119647/</a>
</td>
</tr>
</table>
<br />
<p>Ship it!</p>
<pre style="white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;"><p style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: inherit;">I like the direction you are going, and that someone else is taking a look at the database code.</p>
<p style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: inherit;">I would recommend making the row counts into a view so you can use a JOIN rather than the long statement you created. This has the advantage of letting the DBMS cache the answer when any index changes. That probably has more of an effect before the change to index all the tables, but may be useful anyway.</p></pre>
<br />
<p>- Fernando Vilas</p>
<br />
<p>On August 7th, 2014, 2:57 p.m. UTC, Christian David wrote:</p>
<table bgcolor="#fefadf" width="100%" cellspacing="0" cellpadding="12" style="border: 1px #888a85 solid; border-radius: 6px; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tr>
<td>
<div>Review request for KMymoney.</div>
<div>By Christian David.</div>
<p style="color: grey;"><i>Updated Aug. 7, 2014, 2:57 p.m.</i></p>
<div style="margin-top: 1.5em;">
<b style="color: #575012; font-size: 10pt;">Repository: </b>
kmymoney
</div>
<h1 style="color: #575012; font-size: 10pt; margin-top: 1.5em;">Description </h1>
<table width="100%" bgcolor="#ffffff" cellspacing="0" cellpadding="10" style="border: 1px solid #b8b5a0">
<tr>
<td>
<pre style="margin: 0; padding: 0; white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;"><p style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: inherit;">MyMoneyStorageSql stored the row count of several tables in the table<br style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: normal;" />
kmmFileInfo. But this is error-prone and bad style.</p>
<p style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: inherit;">Now this information is read from the database directly. Usually a database<br style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: normal;" />
caches the row count of tables anyway. Also all tables have an index so even<br style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: normal;" />
a <code style="text-rendering: inherit;color: #4444cc;padding: 0;white-space: normal;margin: 0;line-height: inherit;">count(*)</code> is really fast. The result is still cached within<br style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: normal;" />
MyMoneyStorageSQL as it did before.</p></pre>
</td>
</tr>
</table>
<h1 style="color: #575012; font-size: 10pt; margin-top: 1.5em;">Testing </h1>
<table width="100%" bgcolor="#ffffff" cellspacing="0" cellpadding="10" style="border: 1px solid #b8b5a0">
<tr>
<td>
<pre style="margin: 0; padding: 0; white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -pre-wrap; white-space: -o-pre-wrap; word-wrap: break-word;"><p style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: inherit;">All test were done using SQLite. But all command I use are available in all other databases as well.</p>
<ol style="padding: 0;text-rendering: inherit;margin: 0 0 0 2em;line-height: inherit;white-space: normal;">
<li style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: normal;">Tested the used SQL query in a (huge) database using sqlite:</li>
</ol>
<p style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: inherit;"><div class="codehilite" style="background: #f8f8f8"><pre style="line-height: 125%">SELECT (SELECT count(<span style="color: #666666">*</span>) from kmmAccounts) AS accounts, (SELECT count(<span style="color: #666666">*</span>) FROM kmmCurrencies) AS currencies, (SELECT count(<span style="color: #666666">*</span>) FROM kmmPayees) AS payees, (SELECT count(<span style="color: #666666">*</span>) from kmmTags) AS tags, (SELECT count(<span style="color: #666666">*</span>) FROM kmmTransactions) AS transactions, (SELECT count(<span style="color: #666666">*</span>) FROM kmmSplits) AS splits, (SELECT count(<span style="color: #666666">*</span>) FROM kmmSecurities) AS securities, (SELECT count(<span style="color: #666666">*</span>) FROM kmmCurrencies) AS currencies, (SELECT count(<span style="color: #666666">*</span>) FROM kmmSchedules) AS schedules, (SELECT count(<span style="color: #666666">*</span>) FROM kmmPrices) AS prices, (SELECT count(<span style="color: #666666">*</span>) FROM kmmKeyValuePairs) AS kvps, (SELECT count(<span style="color: #666666">*</span>) FROM kmmReportConfig) AS reports, (SELECT count(<span style="color: #666666">*</span>) FROM kmmBudgetConfig) AS budgets UNION ALL SELECT accounts, currencies, payees, tags, transactions, splits, securities, currencies, schedules, prices, kvps, reports, budgets FROM kmmFileInfo;
</pre></div>
</p>
<p style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: inherit;">Result:</p>
<p style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: inherit;">9|185|6330|0|99999|199998|0|185|0|22|10|0|0 ( <= the new query )<br style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: normal;" />
9|185|6330|0|99999|199998|0|185|0|22|10|0|0 ( <= what is stored in kmmFileInfo )</p>
<ol style="padding: 0;text-rendering: inherit;margin: 0 0 0 2em;line-height: inherit;white-space: normal;">
<li style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: normal;">
<p style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: inherit;">Saved a .kmy file and into a database (to test the INSERT routine), inspected database by hand</p>
</li>
<li style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: normal;">
<p style="padding: 0;text-rendering: inherit;margin: 0;line-height: inherit;white-space: inherit;">Changed something and inspected database by hand afterward.</p>
</li>
</ol></pre>
</td>
</tr>
</table>
<h1 style="color: #575012; font-size: 10pt; margin-top: 1.5em;">Diffs</b> </h1>
<ul style="margin-left: 3em; padding-left: 0;">
<li>kmymoney/mymoney/storage/mymoneystoragesql.h <span style="color: grey">(5e148756739fcbdc3b9ffb6e11751ea035209c2b)</span></li>
<li>kmymoney/mymoney/storage/mymoneystoragesql.cpp <span style="color: grey">(6e7a0715842da5ccb6d40f5f4a512e3433196ce6)</span></li>
</ul>
<p><a href="https://git.reviewboard.kde.org/r/119647/diff/" style="margin-left: 3em;">View Diff</a></p>
</td>
</tr>
</table>
</div>
</body>
</html>