[prev in list] [next in list] [prev in thread] [next in thread]
List: kmymoney-devel
Subject: Re: [Kmymoney-devel] Review Request 119647: Database backend does not store row count in a table any
From: "Thomas Baumgart" <tbaumgart () kde ! org>
Date: 2014-08-15 7:55:37
Message-ID: 20140815075537.14667.4963 () probe ! kde ! org
[Download RAW message or body]
--===============3702612019084646058==
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit
> On Aug. 11, 2014, 12:51 a.m., Fernando Vilas wrote:
> > I like the direction you are going, and that someone else is taking a look at the \
> > database code.
> > 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.
>
> Christian David wrote:
> I recommend we should try it without a view and optimize it if necessarey. Also the \
> function is not call that often and the query is very fast already. On my large \
> test file I could not notice any delay (maybe it is even faster than before).
Looks good to me, though I am not a DB expert at all. For backward compatibility we \
should make sure to keep the values in kmmFileInfo for the next major release and \
update them when the DB is closed. This way, a user is not stranded when he falls \
back to a previous version of the application code.
- Thomas
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://git.reviewboard.kde.org/r/119647/#review64213
-----------------------------------------------------------
On Aug. 7, 2014, 4:57 p.m., Christian David wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://git.reviewboard.kde.org/r/119647/
> -----------------------------------------------------------
>
> (Updated Aug. 7, 2014, 4:57 p.m.)
>
>
> Review request for KMymoney.
>
>
> Repository: kmymoney
>
>
> Description
> -------
>
> MyMoneyStorageSql stored the row count of several tables in the table
> kmmFileInfo. But this is error-prone and bad style.
>
> Now this information is read from the database directly. Usually a database
> caches the row count of tables anyway. Also all tables have an index so even
> a `count(*)` is really fast. The result is still cached within
> MyMoneyStorageSQL as it did before.
>
>
> Diffs
> -----
>
> kmymoney/mymoney/storage/mymoneystoragesql.h \
> 5e148756739fcbdc3b9ffb6e11751ea035209c2b \
> kmymoney/mymoney/storage/mymoneystoragesql.cpp \
> 6e7a0715842da5ccb6d40f5f4a512e3433196ce6
> Diff: https://git.reviewboard.kde.org/r/119647/diff/
>
>
> Testing
> -------
>
> All test were done using SQLite. But all command I use are available in all other \
> databases as well.
> 1. Tested the used SQL query in a (huge) database using sqlite:
>
> ```SQL
> SELECT (SELECT count(*) from kmmAccounts) AS accounts, (SELECT count(*) FROM \
> kmmCurrencies) AS currencies, (SELECT count(*) FROM kmmPayees) AS payees, (SELECT \
> count(*) from kmmTags) AS tags, (SELECT count(*) FROM kmmTransactions) AS \
> transactions, (SELECT count(*) FROM kmmSplits) AS splits, (SELECT count(*) FROM \
> kmmSecurities) AS securities, (SELECT count(*) FROM kmmCurrencies) AS currencies, \
> (SELECT count(*) FROM kmmSchedules) AS schedules, (SELECT count(*) FROM kmmPrices) \
> AS prices, (SELECT count(*) FROM kmmKeyValuePairs) AS kvps, (SELECT count(*) FROM \
> kmmReportConfig) AS reports, (SELECT count(*) FROM kmmBudgetConfig) AS budgets \
> UNION ALL SELECT accounts, currencies, payees, tags, transactions, splits, \
> securities, currencies, schedules, prices, kvps, reports, budgets FROM kmmFileInfo; \
> ```
> Result:
>
> 9|185|6330|0|99999|199998|0|185|0|22|10|0|0 ( <= the new query )
> 9|185|6330|0|99999|199998|0|185|0|22|10|0|0 ( <= what is stored in kmmFileInfo )
>
> 2. Saved a .kmy file and into a database (to test the INSERT routine), inspected \
> database by hand
> 3. Changed something and inspected database by hand afterward.
>
>
> Thanks,
>
> Christian David
>
>
--===============3702612019084646058==
MIME-Version: 1.0
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit
<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 />
<blockquote style="margin-left: 1em; border-left: 2px solid #d0d0d0; padding-left: \
10px;"> <p style="margin-top: 0;">On August 11th, 2014, 12:51 a.m. CEST, <b>Fernando \
Vilas</b> wrote:</p> <blockquote style="margin-left: 1em; border-left: 2px solid \
#d0d0d0; padding-left: 10px;"> <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> </blockquote>
<p>On August 11th, 2014, 1:48 p.m. CEST, <b>Christian David</b> wrote:</p>
<blockquote style="margin-left: 1em; border-left: 2px solid #d0d0d0; padding-left: \
10px;"> <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 \
recommend we should try it without a view and optimize it if necessarey. Also the \
function is not call that often and the query is very fast already. On my large test \
file I could not notice any delay (maybe it is even faster than before).</p></pre> \
</blockquote>
</blockquote>
<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;">Looks \
good to me, though I am not a DB expert at all. For backward compatibility we should \
make sure to keep the values in kmmFileInfo for the next major release and update \
them when the DB is closed. This way, a user is not stranded when he falls back to a \
previous version of the application code.</p></pre> <br />
<p>- Thomas</p>
<br />
<p>On August 7th, 2014, 4:57 p.m. CEST, 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, 4: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: #6666 66">*</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>
--===============3702612019084646058==--
_______________________________________________
KMyMoney-devel mailing list
KMyMoney-devel@kde.org
https://mail.kde.org/mailman/listinfo/kmymoney-devel
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic