> On Aug. 11, 2014, 12:51 vorm., 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). > > Thomas Baumgart wrote: > 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.
That is a good idea, Thomas. I'll do that. But before that I want to solve the issue in the upgrade progress (see mailing list). Because this patch is affected in the same way. - Christian ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://git.reviewboard.kde.org/r/119647/#review64213 ----------------------------------------------------------- On Aug. 7, 2014, 4:57 nachm., 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 nachm.) > > > 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 > >
_______________________________________________ KMyMoney-devel mailing list KMyMoney-devel@kde.org https://mail.kde.org/mailman/listinfo/kmymoney-devel
