How optimize sqlite query with dates & substr?


How optimize sqlite query with dates & substr?



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.


iPhone OpenGL ES incorrect alpha blending

1:

Does a NSURLConnection retain its delegate?
I have no idea how the performance compares, although ananother way to write the first query there is. Which button was tapped… iPhone and Obj-C question
SELECT Id FROM [Table] WHERE Name LIKE "a%" 
for the reason this a% matches any string beginning with a or A, followed by any number of characters.. Connection See http://www.sqlite.org/lang_expr.html#like for more reference.. Can UILabel's drawTextInRect method be overridden to change the size of UILabel's text? Second, I'm guessing this you're taking a performance hit on repeated calls to datetime. Attaching another modem to an iPhone or gPhone 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:. Making a UITableView scroll when text field is selected
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.. What does this syntax error mean? [closed]

2:

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


48 out of 100 based on 18 user ratings 68 reviews