How optimize sqlite query with dates & substr?

Question

I have some sqlite querys that are slowding my iPhone App.

I have 2 pattern that I repeat in my app. The first is with substr:

SELECT Id FROM [Table] WHERE UPPER(substr(Name,1,1))='A' ORDER BY Name"

(This run for the mayority of the alphabet. Is for a contact list)

The other is a complex one with date:

SELECT Id,customerCode, 
case
when DueDate>=datetime('now','-100 years') AND DueDate<=datetime('now','-1 days') then 'Past due'
when DueDate<datetime('now') then 'Today'
when DueDate>=datetime('now') AND DueDate<=datetime('now','15 days') then '15'
when DueDate>=datetime('now','16 days') AND DueDate<=datetime('now','30 days') then '30'
when DueDate>=datetime('now','31 days') AND DueDate<=datetime('now','45 days') then '45'
when DueDate>=datetime('now','46 days') AND DueDate<=datetime('now','60 days') then '60'
when DueDate>=datetime('now','61 days') AND DueDate<=datetime('now','90 days') then '90'
when DueDate>=datetime('now','91 days') then '> 90'
end As Key, 
COUNT(*) As Total, TimeStamp
FROM debt
GROUP BY customerCode,
case
when DueDate>=datetime('now','-100 years') AND DueDate<=datetime('now','-1 days') then '-1'
when DueDate<datetime('now') then 'Today'
when DueDate>=datetime('now') AND DueDate<=datetime('now','15 days') then '15'
when DueDate>=datetime('now','16 days') AND DueDate<=datetime('now','30 days') then '30'
when DueDate>=datetime('now','31 days') AND DueDate<=datetime('now','45 days') then '45'
when DueDate>=datetime('now','46 days') AND DueDate<=datetime('now','60 days') then '60'
when DueDate>=datetime('now','61 days') AND DueDate<=datetime('now','90 days') then '90'
when DueDate>=datetime('now','91 days') then '> 90'
end

In both case I have index for the date & the varchar field.

The 1st run ok in the simulator, and take 11 seconds in the iPod touch 2 GEN

The second query is slow too in the iPhone simulator (3 sec) & 1 min in the device. The dates are using the ISO date form.

I have tougth of build a cache table (and put trigers for the updates. The data is live and the user can modify it) for the data, but wonder if exist a better way.

Answer 1

I have no idea how the performance compares, but another way to write the first query there is

SELECT Id FROM [Table] WHERE Name LIKE "a%"

because a% matches any string beginning with a or A, followed by any number of characters.

See http://www.sqlite.org/lang_expr.html#like for more reference.

Second, I'm guessing that you're taking a performance hit on repeated calls to datetime. Can you compress it into a single mathematical operation, then compare the difference in days using simple mathematical operators? For example, the number of days since last Christmas:

sqlite> select julianday('now') - julianday('2008-12-05');
212.743649386801

SQLite stores dates as strings internally, so every time you call datetime it has to fully parse the date string, etc.

Answer 2

You don't have to repeat that CASE operator twice -- just GROUP BY CustomerCode, Key will suffice. Also, as @Mark suggests, the CASE can be perhaps be optimized by using differences in julianday, and the name selection by exploiting the fact that LIKE is, by default, case insensitive (as long as you need ASCII letters only -- alas, it's a documented bug in sqlite that this case insensitivity doesn't work properly outside of ASCII).