[Kmymoney-devel] Anyone good with SQL?

[David Houlden]

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.

Regards,
Dave.