[Kmymoney-devel] Anyone good with SQL?

[David Houlden]

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.