Predicate "IN" is very slow on device (83 times difference)

I have a predicate

predicate = [NSPredicate predicateWithFormat:@"character.id IN %@", indexs];

it generates the following SQL:

CoreData: sql: SELECT t0.Z_ENT, t0.Z_PK, t0.Z_OPT, t0.ZCHARACTERID, t0.ZMEANING, t0.ZREADING, t0.ZRADICAL, t0.ZSTROKECOUNT, t0.ZCHARACTER, t0.ZFREQUENCY, t0.ZGRADE, t0.ZJLPTLEVEL, t0.ZKUNREADING, t0.ZHWUNMEANING, t0.ZROMAN, t0.ZHWUN, t0.ZHWUNMORE, t0.ZPHONETICID FROM ZCHARACTERINFO t0 LEFT OUTER JOIN ZCHARACTER t1 ON t0.ZCHARACTER = t1.Z_PK WHERE ( t1.ZID IN (?,?,?,?,?,?,?) AND t0.Z_ENT = ?) ORDER BY t0.ZSTROKECOUNT, t1.ZUNICODE

In simulator I got the following result:

CoreData: annotation: sql connection fetch time: 0.0478s CoreData: annotation: total fetch execution time: 0.0483s for 7 rows.

VS on device (iPhone 3G)

CoreData: annotation: sql connection fetch time: 3.9627s CoreData: annotati开发者_StackOverflowon: total fetch execution time: 3.9789s for 7 rows.

SQLite explain query plan shows I'm using the index (ZID has index defined in model):

selectid    order       from        detail                                                                                      
----------  ----------  ----------  ---------------------------------------------------------------------------------------------
0           0           0           SEARCH TABLE ZCHARACTERINFO AS t0 USING INDEX ZCHARACTERINFO_Z_ENT_INDEX (Z_ENT=?) (~10 rows)
0           1           1           SEARCH TABLE ZCHARACTER AS t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)                 
0           0           0           EXECUTE LIST SUBQUERY 1                                                                     
0           0           0           USE TEMP B-TREE FOR ORDER BY                                                                
CPU Time: user 0.000395 sys 0.000184

In database, I have 20,900 rows in character table and 62,000 rows in characterinfo table.

I'm wondering why it is so slow on device. Did I missing something? Is there a way to improve? Thanks.

UPDATES: I was suspecting the join is the problem. So I tried to move t1.ZID into t0 table so that I can get rid of the join table. The new query became:

SELECT t0.Z_ENT, t0.Z_PK FROM ZCHARACTERINFO t0 WHERE ( t0.ZCHARACTERID IN (?,?,?,?,?,?,?) AND t0.Z_ENT = ?) ORDER BY t0.ZSTROKECOUNT

The new query used around 1.8s. It was improved but it still seems slow. Is it suppose to be like that slow?

The slowness from your first query was more than likely stemming from the LEFT OUTER JOIN.

Based on the size of your db that you reported, I'd say 1.8 seconds is really fast, especially on an iPhone 3G.

If that's still not fast enough, I'd recommend using a lower level db framework which will allow you to directly manipulate the schema of your db and use other tools to speed up your queries.

Thanks all your help. Resolved the problem.

1. The join is pretty slow. Eliminated join and the time down to 1.8~2s. (see the update above)
2. The query didn't use index or only used index on Z_ENT. For ZCHARACTERID, it still do the full table scan. So I added "CREATE INDEX CID_Z_ENT_INDEX ON ZCHARACTERINFO (ZCHARACTERID, Z_ENT). The query time reduced to 0.05s.

NOTE: Somehow after added the new index, explain query plan still shows it uses Z_ENT index. Have to drop Z_ENT index so CID_Z_ENT_INDEX can be used. Not sure why.