The distance function for SQLite in iOS 5 is very slow

Question

For a long time I used the C function, which calculates the distance between coordinates in SQLLite.

The fact is that for the same database and different iOS devices (4 and 5), the execution time of the same request is 5 times longer than when iOS 5 was launched.

Does anyone know why?

Here is the code for the distance:

static void distanceFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ // check that we have four arguments (lat1, lon1, lat2, lon2) assert(argc == 4); // check that all four arguments are non-null if (sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL || sqlite3_value_type(argv[2]) == SQLITE_NULL || sqlite3_value_type(argv[3]) == SQLITE_NULL) { sqlite3_result_null(context); return; } // get the four argument values double lat1 = sqlite3_value_double(argv[0]); double lon1 = sqlite3_value_double(argv[1]); double lat2 = sqlite3_value_double(argv[2]); double lon2 = sqlite3_value_double(argv[3]); // convert lat1 and lat2 into radians now, to avoid doing it twice below double lat1rad = DEG2RAD(lat1); double lat2rad = DEG2RAD(lat2); // apply the spherical law of cosines to our latitudes and longitudes, and set the result appropriately // 6378.1 is the approximate radius of the earth in kilometres sqlite3_result_double(context, (acos(sin(lat1rad) * sin(lat2rad) + cos(lat1rad) * cos(lat2rad) * cos(DEG2RAD(lon2) - DEG2RAD(lon1))) * 6378.1) * 1000); } 

and here is an example of how I use it:

 Select distance(Latitude, Longitude,-22.89095,-47.05134) from TABLE 

Please any help would be much appreciated

thanks

Answer 1

OK I found out that this is a problem on iOS 5.0. Even with the final release, slow requests still happened.

I had to remove " order by distance(Latitude, Longitude,-22.89095,-47.05134) ", and the request again accelerated.

Then I did a sort using code ... Not the best way. but...

Answer 2

iOS 5 is still in beta, and beta build performance is usually not a good measure of performance.

Answer 3

I had the same experience with CoreData (using the sqllite container).

Prior to iOS5 with NSFetchRequest, sortDescriptors worked at a reasonable speed.

After upgrading to 5, a very noticeable delay. Setting sortDescriptors to an empty array fixed poor performance, but meant that I needed to manually sort (and group) my results in code.

Was there any hope that update 5.0.1 would fix it, but that still seems to be a problem.

Answer 4

I had to add indexes. Even with this change, it still works slower than before. I skipped all the settings and it looks like something else - the sqlite version is the same. Secret.