[OSM-dev] Improving the /map call with cursors

[Richard Fairhurst]

Prompted by some comments on IRC about a half-baked 'find_in_batches'  
feature in Rails 2.3, which looks like it'll be precisely 0 use for  
us, I had a little - ultimately fruitless - play with the /map call.*

In brief, one of the performance problems we have is that when you ask  
for all the data within an bbox, Rails retrieves all the nodes from  
the db in one hit, creating an object for every single node. If you  
have 5000 nodes in one bbox, that's quite a memory footprint.

But if you could retrieve them in batches of (say) 100, you'd never  
have more than 100 nodes in memory at once.

The Rails 2.3 feature only appears to work if you're searching by id,  
and obviously we're not, we're searching by lat/long (via quadtiles).  
So that's not a lot of use.

There is, however, an ActiveRecord Enumerable plugin which does the  
same job, properly. It uses native database cursors to step through  
the result set. You can download it at http://rubyforge.org/projects/ar-enumerable/ 
  (then copy ar-enumerable/ to vendor/plugins/).

I had a play at using this in the /map call. Unfortunately, MySQL only  
appears to support cursors within stored procedures; and rewriting as  
a stored procedure was far too much work for a Tuesday evening (I had  
vague thoughts about writing a procedure to do the whole sql_for_area  
thing using Tom's tile_for_point function but realised I hadn't a clue  
what I was doing). So that's as far as I got.

If you were using a more competent database like Postgres, I guess it  
should work out of the box.

So if anyone with Postgres installed wants to take it further, my  
altered api_controller.rb for 0.6 is at
	http://www.systemeD.net/osm/api_controller.rb

(If you remove :use_cursor => true from line 140, it'll work on MySQL,  
but using OFFSET and LIMIT which probably defeats the performance  
gains of the 'batched' approach.)

cheers
Richard


* Potlatch's whichways is roughly the same, of course.