[Kmymoney-devel] Anyone good with SQL?

[Fernando Vilas]

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
> queries.
> 
> 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.

Good Luck.

-- 
Thanks,
Fernando Vilas
fvilas at iname.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part.
URL: <http://mail.kde.org/pipermail/kmymoney-devel/attachments/20110811/050232a3/attachment.sig>