[Kmymoney-devel] Anyone good with SQL?
fvilas at iname.com
Fri Aug 12 02:21:26 UTC 2011
On Thursday, August 11, 2011 20:15:43 Colin Wright wrote:
> > 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"
> For a start, always use UNION ALL if you know that the two parts of the
> query are distinct. UNION is like a set union as performs a DISTINCT to
> eliminate duplicates. That DISTINCT operation can significantly slow down
> In this case, since it will be sorting due to the ORDER BY anyway, the hit
> won't be as large as it might have been.
> Also, it's actually an OUTER JOIN and should be rewritten as one unless you
> need to support an old version that doesn't support outer joins.
> It looks like it could be really slow in a large data set. If this is done
> a lot you'd want an index on kmmSplits(accountid, postDate) (or
> kmmSplits(accountid, txType, postDate) if it does lots of type-based
> searches and N is uncommon). That way it can do a range scan descending,
> preferably with 50 row limits and concatenate the results.
Yes to the OUTER JOIN rewrite:
SELECT transactionId AS _id, payeeId, valueFormatted, memo, postDate, name
FROM kmmSplits LEFT OUTER JOIN kmmPayees
ON kmmSplits.payeeID = kmmPayees.id
WHERE (accountId = ? AND txType = 'N')
ORDER BY postDate DESC LIMIT 50
That should get you close, but I would check the results against your earlier
query for correctness.
Depending on the number of times you want to execute this, make sure to
prepare the query only once and bind the accountId as needed.
I do not remember if SQLite supports indexing or EXPLAIN, but putting an index
on kmmSplits.payeeID and/or accountId may help. Overuse of indexing can hurt
in some cases, so check out your other queries after you do this. The EXPLAIN
functionality will tell you if the DB is using an index or a full table scan
to make things happen.
fvilas at iname.com
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 198 bytes
Desc: This is a digitally signed message part.
More information about the KMyMoney-devel