Skip to main content

Very slow query where table have many records



I' m using sqlite on iphone. I have table with 200 000 records. It' s size is small, only 2 MB. I' m doing standard select(3 collumns) with two simple condtions. I' m operating only on integer values( in return and condtion). All data are unique. I'm get usually only 10-50 records from database.





In spite of that my query take 3 sec. It' s a lot of time. How i can improve this ?





Edit: I can't load my table with other thread.







NSString * query = [ NSString stringWithFormat:@"SELECT rRozklad.hour, rRozklad.minute, rRozklad.marks FROM rRozklad.idOkres=%i AND rRozklad.idPolaczeniaLinie=2 ORDER by rRozklad.hour, rRozklad.minute", okres];



NSLog(@"%@", query);



const char * querystring = [ query UTF8String ];

sqlite3_stmt * statment;



if(sqlite3_prepare_v2(database, querystring, -1, &statment, nil) == SQLITE_OK){

while(sqlite3_step(statment) == SQLITE_ROW){

NSMutableArray * temp = [[NSMutableArray alloc] initWithCapacity:2];

[temp addObject:[NSNumber numberWithInt: sqlite3_column_int(statment, 0)]];

[temp addObject:[NSNumber numberWithInt: sqlite3_column_int(statment, 1)] ];

[temp addObject:[ NSString stringWithUTF8String: (const char *)sqlite3_column_text(statment, 2)]];



[wyniki addObject:temp];

[temp release];



}

}




Comments

  1. It looks like your SQL is missing the table name and the WHERE keyword.

    SELECT rRozklad.hour, rRozklad.minute, rRozklad.marks
    FROM rRozklad.idOkres=%i AND rRozklad.idPolaczeniaLinie=2
    ORDER by rRozklad.hour, rRozklad.minute


    Do you have your columns indexed? Especially idOkres, idPolaczeniaLinie, hour, and minute?

    ReplyDelete

Post a Comment

Popular posts from this blog

Why is this Javascript much *slower* than its jQuery equivalent?

I have a HTML list of about 500 items and a "filter" box above it. I started by using jQuery to filter the list when I typed a letter (timing code added later): $('#filter').keyup( function() { var jqStart = (new Date).getTime(); var search = $(this).val().toLowerCase(); var $list = $('ul.ablist > li'); $list.each( function() { if ( $(this).text().toLowerCase().indexOf(search) === -1 ) $(this).hide(); else $(this).show(); } ); console.log('Time: ' + ((new Date).getTime() - jqStart)); } ); However, there was a couple of seconds delay after typing each letter (particularly the first letter). So I thought it may be slightly quicker if I used plain Javascript (I read recently that jQuery's each function is particularly slow). Here's my JS equivalent: document.getElementById('filter').addEventListener( 'keyup', function () { var jsStart = (new Date).getTime()...

Is it possible to have IF statement in an Echo statement in PHP

Thanks in advance. I did look at the other questions/answers that were similar and didn't find exactly what I was looking for. I'm trying to do this, am I on the right path? echo " <div id='tabs-".$match."'> <textarea id='".$match."' name='".$match."'>". if ($COLUMN_NAME === $match) { echo $FIELD_WITH_COLUMN_NAME; } else { } ."</textarea> <script type='text/javascript'> CKEDITOR.replace( '".$match."' ); </script> </div>"; I am getting the following error message in the browser: Parse error: syntax error, unexpected T_IF Please let me know if this is the right way to go about nesting an IF statement inside an echo. Thank you.