Executing sqlite groupby and select statements with 50.000 rows in IPhone 3G

anybody experience with grouping datatables with a lot of rows (50k)? My IPhone 3G 开发者_高级运维takes 11seconds!!! for executing a group by statement.

  2011-06-11 09:20:50.719 crmclient[1040:307] Begin Select count(*) as count,  substr(upper(Pers_LastName),1,1) as identifier From contact_sync2 WHERE 1=1 GROUP BY substr(upper(Pers_LastName),1,1) order by Pers_LastName COLLATE NOCASE 

  2011-06-11 09:20:50.736 crmclient[1040:307] End

 2011-06-11 09:21:05.047 crmclient[1040:307] RSEnd

2011-06-11 09:21:05.534 crmclient[1040:307] Begin Select  Pers_PersonId, Pers_LastName || ', ' || Pers_FirstName AS Pers_FullName  From contact_sync2 WHERE 1=1  order by Pers_LastName COLLATE NOCASE  LIMIT 0,140

 2011-06-11 09:21:05.546 crmclient[1040:307] End

2011-06-11 09:21:13.426 crmclient[1040:307] RSEnd

The first one gives me the structure of the whole table to show on iphone. The second gives me the first 140 rows. RSEnd is printed when the sqlite.step is done.

Thank you very much. I think 50.000 rows is not that much.

Maybe is better in your case to keep in a separate column the first letter. In this way you could eliminate the 2 functions (substr and upper) from your query as these takes time.

The select will be:

Select 
  count(*) as count, FirstLetter 
From 
  contact_sync2 
GROUP BY 
  FirstLetter 
ORDER BY 
  Pers_LastName COLLATE NOCASE 

Also put an index on FirstLetter.

If you can't create indexes how fast would it be without using ORDER BY, and then sorting using Objective-C?