iphone - iOS sqlite3_step hold / freeze after last row data -

my sqlite3_step holds 1s after read of last row data. why?

-(nsdictionary*)specificationitemsforconfigurationsids:(nsstring*)configurationsidsstr {     [self databaseopen];      nsstring *query = [nsstring stringwithformat:@"select specitem.id,specitem.name,configurationspec.configuration_id\                    (specitem inner join configurationspec on configurationspec.spec_item_id=specitem.id)\                    (specitem.parent_id=12 or specitem.parent_id=34 or specitem.id=23 or specitem.id=27) , configurationspec.configuration_id in (%@)",configurationsidsstr];  sqlite3_stmt *statement;  nsmutabledictionary* configurationswithspecitems = [nsmutabledictionary new];  if (sqlite3_prepare_v2(database, [query utf8string], -1, &statement, nil) == sqlite_ok) {     while (sqlite3_step(statement) == sqlite_row)     {         int specitemid = sqlite3_column_int(statement, 0);         nsstring* specitemname = [self sqlite3_column_text_asstring_ofstatement:statement                                                                        atcolumn:1];         int configid = sqlite3_column_int(statement, 2);         nsstring* configidnumber = [nsstring stringwithformat:@"%d",configid];          nsmutablearray* specitems = [configurationswithspecitems objectforkey:configidnumber];         if(specitems == nil)         {             specitems = [nsmutablearray new];             [configurationswithspecitems setobject:specitems                                             forkey:configidnumber];         }          specificationitem* specitem = [specificationitem specificationitemwithid:specitemid                                                                             name:specitemname];          [specitems addobject:specitem];         // when read last row data, getting here point 2 takes 1s     }     // point 2     sqlite3_finalize(statement); } [self databaseclose]; return configurationswithspecitems; } 

single read of 1 row takes 2-3ms, after last 1 getting out of while loop takes 1s, me.

explain query plan output query:

0 0 1 scan table configuration (~100000 rows)  0 0 0 execute list subquery 1 1 0 0 search table configuration using automatic covering index (model_id=?) (~7 rows)  0 1 0 search table specitem using integer primary key (rowid=?) (~1 rows)  

there 2 explanations delay; 1 or both might apply:

• all records match @ beginning of configuration table. after last matching record, sqlite still has search through remaining records, none matches.
• sqlite creates temporary index on model_id column because estimates query slower without it. after query has finished, index must deleted again; see time needed synchronize @ end of (automatic) transaction.

create index on model_id column avoiding both of these points.

if possible, should try merge subquery (in configurationsidstr) outer query; instead of:

... configurationspec.configuration_id in (            select configuration_id configuration model_id = 42) 

use this:

... configurationspec.model_id = 42 

avoiding indirection makes easier sqlite optimize query execution.