CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordrails-activerecord
Code father
Code father

Reputation: 605

How to make the select query comparatively faster in rails?

I was looking for best approach to get this query faster in rails. Currently it takes lots of time due to Geocoder calculations.

Vehicle.includes(:user)
       .where.not('users.id' => nil)
       .where(country: 'United Kingdom')
       .where.not(name: [nil, ''])
       .select { |vehicle| 
         vehicle.longitude.present? &&
         Geocoder::Calculations.distance_between(
           [vehicle.latitude,vehicle.longitude], 
           [location.latitude,location.longitude]
         ).between?(0, 200)
       }

Note: Currently the db used is PostgreSQL 9.5.14.

Upvotes: 1

Views: 830

Answers (1)

Marcin Kołodziej
Marcin Kołodziej

Reputation: 5313

  1. Move vehicle.longitude.present? to database level:

    .where.not(longitude: [nil, ''])

  2. Your distance calculation has to be moved to be on database level. Loading all records and calculating distance for each of them in Ruby will be too slow. Simplest solution is to add PostGIS:

    1. Add PostGIS extension to your database.
    2. (Optional) add an adapter that will make it easier to work with the data.
    3. Migrate your existing latitudes/longitudes to st_point.
    4. Change your select to a scope which will leverage the ST_Distance function.

  3. As an alternative to 2), you may consider moving your distance querying to a search engine like ElasticSearch. I'd only consider that if you will have any performance problems with the complex query you're creating.

Upvotes: 3

Related Questions