CODEWITHSUNDEEP
iphonesqlitequery-optimizationfmdb
MuTaTeD
MuTaTeD

Reputation: 871

iPhone SQLite (FMDB) query takes excessive time


I am trying to execute a query that has group by clause in it.
The query executes in 0.012243 seconds
However when I execute [resultset next], it takes more than 5 seconds the first time it is called.

Example

NSMutableArray *recordsArray = [[NSMutableArray alloc] init];

NSString * query = @"select count(id), SUBSTR(purchase_date, 0,11) as purchase_date from sales where purchase_date LIKE '2012-12%' group by purchase_date order by purchase_date asc";

NSDate * date1 = [NSDate date];

FMResultSet * resultset = [database executeQuery:query];

NSDate * date2 = [NSDate date];
NSTimeInterval timeTaken = [date2 timeIntervalSinceDate:date1];
NSLog(@"TimeTaken: %f", timeTaken);     //outputs around 0.012


while ([resultset next])
{
    [recordsArray addObject:[resultset resultDictionary]];
}

date2 = [NSDate date];      
timeTaken = [date2 timeIntervalSinceDate:date1];
NSLog(@"TimeTaken 2: %f", timeTaken);   //outputs around 5.5 seconds

I have also been able to determine that all the time taken is during the first time [resultset next] is called.

I have also tried modifying the query to remove the group by clause by generating a UNION'ed query like 

NSString * query2 = @"select * from 
(
    select count(id), SUBSTR(purchase_date, 0,11) as purchase_date from sales where purchase_date = 2012-12-01
    UNION
    select count(id), SUBSTR(purchase_date, 0,11) as purchase_date from sales where purchase_date = 2012-12-02
    UNION
    select count(id), SUBSTR(purchase_date, 0,11) as purchase_date from sales where purchase_date = 2012-12-03
    ....
    UNION
    select count(id), SUBSTR(purchase_date, 0,11) as purchase_date from sales where purchase_date = 2012-12-31
) where purcase_date is not NULL order by purchase_date asc";

Executing this query also takes 0.2 seconds but the first call to [resultset next] and the time shoots to 7+ seconds.

Other Info
The table currently has 8000+ rows but that number can go as high as 100K in case of some of my users.

I am using this data to plot a graph for the sales trends for the given month.

On the simulator, this query executes in less that 0.5 seconds but on the device it takes a lot of time.

Question
Can you guide me how to bring down the time for this query?

Upvotes: 1

Views: 975

Answers (1)

MuTaTeD
MuTaTeD

Reputation: 871

I determined that the largest bottleneck was the SUBSTR and Group By clauses and executing & processing a simple query like the following only took around 0.02 seconds

Select purchase_date from sales where purchase_date LIKE '2012-12%' order by purchase_date asc;

So I introduced it as an an inner query 

Select count(purcahse_date) as count, SUBSTR(purchase_date, 0, 11) as purchase_date from
(
    Select purchase_date from sales where purchase_date LIKE '2012-12%'
)
group by purchase_date, order by purchase_date;

Although the data generated was same as the initial query the time again sky-rocketed to around 5.5 seconds as before.

So finally I decide to bite the bullet and my final solution till now is to get all the purchase_date records for the given month and process them by myself.

So Now the code looks like this

while ([resultset next])
{
    [recordsArray addObject:[resultset resultDictionary]];
}
[resultset close];

[self closeDB];
        //release the db lock at this point

int array[31];    //maximum days in a month
bzero((void *)array, 31 * sizeof(int));     //initialize the month array

for(NSDictionary * d in recordsArray)    //read the records received from the db and add daily sales count
{
    NSRange r;
    r.location = 8;
    r.length = 2;
    int dDate = [[[d objectForKey:@"purchase_date"] substringWithRange:r] intValue];
    array[dDate-1]++;
}

[recordsArray removeAllObjects];

//refDate contains @"2012-12"
for(int i=0; i<31; i++)     //now populate the final array again
{
    if(array[i] > 0)
    {
        NSDictionary * d1 = [NSDictionary dictionaryWithObjectsAndKeys:[NSString stringWithFormat:@"%@-%02d", refDate, i+1], @"date", [NSNumber numberWithInt:array[i]], @"count", nil];
        [recordsArray addObject:d1];
    }
}

return recordsArray;

I hope it helps someone else also stuck in a similar situation or some db guru might suggest some better alternative than this ugly solution.

Upvotes: 2

Related Questions