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

Question

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: annotation: 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?

I'm not a DBA so I can't comment on performance. I do know that IN clauses are sometimes replaceable with joins, and might use joins against temporary tables under the covers. — Merlyn Morgan-Graham

Paul Tiarks Paul Tiarks · Accepted Answer · 2011-06-19T14:10:51

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.

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

2 Answers