On Thursday 11 August 2011 13:09:14 David Houlden wrote: > On Thursday 11 August 2011 08:13:47 Eric A. Bonney wrote: > > I need help with speeding up this query. This query is run in my app > > when the user wants to display the ledger view after selecting the > > account. It takes about 3-5 seconds so far to run in my emulator on a > > sqlite database that has about 14k splits, 6500 transactions and 150 > > accounts. > > > > Here is the sql query that I am running: > > "SELECT transactionId AS _id, payeeId, valueFormatted, memo, postDate, > > name FROM kmmSplits, kmmPayees WHERE (kmmSplits.payeeID = kmmPayees.id > > AND accountId = ? AND txType = 'N') UNION SELECT transactionId, payeeId, > > valueFormatted, memo, postDate, checkNumber FROM kmmSplits WHERE payeeID > > IS NULL AND accountId = ? AND txType = 'N' ORDER BY postDate DESC LIMIT > > 50" > > First thing I would do is identify if there is a part of the query which is > taking most of the time. Run the two selects separately without the union > and without the order by and get timings for each select. Then, still > keeping the selects separate, run them again with the order by. What do > those timings tell you? > > > The '?' get filled in with the actual accountId the user has selected. I > > am currently limiting the return to 50 records as you can see but this > > does nothing to speed up the query, so it might as well be dropped. Is > > there anything I can do make this faster or am I just stuck with this > > being a limitation to the sqlite database? > > I've never done anything with sqlite so don't know its limitations. I have > used other relational databases and sql in the past though. > Databases I have used in the past have a way of examining the data in a table so that the query optimizer can make good decisions about how best to access the data such as using a full table scan versus using an index. I have just done some reading and sqlite has this feature. Try running an analyze on your database and see if it makes any difference. See http://www.sqlite.org/lang_analyze.html
Regards, Dave. _______________________________________________ KMyMoney-devel mailing list KMyMoney-devel@kde.org https://mail.kde.org/mailman/listinfo/kmymoney-devel
