CODEWITHSUNDEEP
sqlruby-on-railsjsonrubyactiverecord
Hugo
Hugo

Reputation: 2204

How to solve High Response Time in this method from my Ruby on Rails app

I have this method in my Rails API that keep sending me High Response Time alerts. I have tried to optimize it as much as I could according to my current knowledge but it's still not doing the job apparently.

Any help on how to optimize these queries would be much appreciated:

This is my method to fetch markers and send them over to my API

First I fetch the addresses

  longitude = params[:longitude]
  latitude = params[:latitude]

  @addresses = Address.joins('INNER JOIN users ON users.id = addresses.addressable_id')
                      .joins('INNER JOIN items ON items.user_id = users.id')
                      .where('items.name IS NOT NULL').where("items.name <> ''")
                      .where('items.visibility = TRUE')
                      .where('items.photo IS NOT NULL').where("items.photo <> ''")
                      .where('addresses.latitude IS NOT NULL AND addresses.addressable_type = ? ', "User")
                      .near([latitude, longitude], (params[:distance].to_i + 1000))

Second, I use these addresses to render a JSON object back to my API

I have a checkitem method

def checkitem(item)
  begin
    requests = Request.where('item_id = ? AND created_at < ? AND created_at > ?', item.id, (DateTime.now - 1.day), (DateTime.now - 6.months)).pluck(:status)

    if (requests.exists? && requests.count > 2)
      if requests.count('pending') >= 3 && (item.user.current_sign_in_at.present? && item.user.current_sign_in_at < (DateTime.now - 2.weeks))
        false
      else
        true
      end
    elsif (requests == [] || requests.count <= 2)
      true
    elsif (item.user.current_sign_in_at.present? && item.user.current_sign_in_at > (DateTime.now - 2.weeks)) || item.user.created_at > (DateTime.now - 2.weeks)
      true
    else
      false
    end
  rescue
    true
  end
end

Then I render my JSON

@places = Address.where(addressable_type: 'Item').where.not(type_add: nil).near([latitude, longitude], 10)

render json: {markers: @addresses.uniq.map { |address|
                      [{
                        name: address.user.items.first.name,
                        photo: { uri: address.user.items.first.photo.url },
                        id: Item.where(user_id: address.addressable_id).first.id,
                        latitude: address.latitude,
                        longitude: address.longitude,
                        breed: address.user.items.first.breed.id,
                        innactive: checkitem(address.user.items.first) ? false : true,
                        power: (address.user.items.first.requests.count >= 2 && address.user.items.first.requests.last(3).map(&:status).count('pending') < 1) ? true : false,
                      }]
                }.reject { |e| e.nil? }.flatten.first(100)
              }
end

@address.explain 

    => EXPLAIN for: SELECT addresses.*, 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((45.501689 - addresses.latitude) * PI() / 180 / 2), 2) + COS(45.501689 * PI() / 180) * COS(addresses.latitude * PI() / 180) * POWER(SIN((-73.567256 - addresses.longitude) * PI() / 180 / 2), 2))) AS distance, MOD(CAST((ATAN2( ((addresses.longitude - -73.567256) / 57.2957795), ((addresses.latitude - 45.501689) / 57.2957795)) * 57.2957795) + 360 AS decimal), 360) AS bearing FROM "addresses" INNER JOIN users ON users.id = addresses.addressable_id INNER JOIN items ON items.user_id = users.id WHERE (items.name IS NOT NULL) AND (items.name <> '') AND (items.visibility = TRUE) AND (items.photo IS NOT NULL) AND (items.photo <> '') AND (addresses.latitude IS NOT NULL AND addresses.addressable_type = 'User' ) AND (addresses.latitude BETWEEN 31.028510688915205 AND 59.97486731108479 AND addresses.longitude BETWEEN -94.21702228070411 AND -52.91748971929589 AND (3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((45.501689 - addresses.latitude) * PI() / 180 / 2), 2) + COS(45.501689 * PI() / 180) * COS(addresses.latitude * PI() / 180) * POWER(SIN((-73.567256 - addresses.longitude) * PI() / 180 / 2), 2)))) BETWEEN 0.0 AND 1000) ORDER BY distance ASC
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=224.28..224.28 rows=1 width=138)
   Sort Key: (('7917.511728464'::double precision * asin(sqrt((power(sin((((('45.501689'::double precision - addresses.latitude) * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision) + (('0.70088823836273'::double precision * cos(((addresses.latitude * '3.14159265358979'::double precision) / '180'::double precision))) * power(sin((((('-73.567256'::double precision - addresses.longitude) * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision)))))))
   ->  Nested Loop  (cost=0.11..224.28 rows=1 width=138)
         ->  Nested Loop  (cost=0.06..207.10 rows=39 width=8)
               ->  Seq Scan on items  (cost=0.00..126.62 rows=39 width=4)
                     Filter: ((name IS NOT NULL) AND visibility AND (photo IS NOT NULL) AND ((name)::text <> ''::text) AND ((photo)::text <> ''::text))
               ->  Index Only Scan using users_pkey on users  (cost=0.06..2.06 rows=1 width=4)
                     Index Cond: (id = items.user_id)
         ->  Index Scan using index_addresses_on_addressable_type_and_addressable_id on addresses  (cost=0.06..0.44 rows=1 width=98)
               Index Cond: (((addressable_type)::text = 'User'::text) AND (addressable_id = users.id))
               Filter: ((latitude IS NOT NULL) AND (latitude >= '31.0285106889152'::double precision) AND (latitude <= '59.9748673110848'::double precision) AND (longitude >= '-94.2170222807041'::double precision) AND (longitude <= '-52.9174897192959'::double precision) AND (('7917.511728464'::double precision * asin(sqrt((power(sin((((('45.501689'::double precision - latitude) * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision) + (('0.70088823836273'::double precision * cos(((latitude * '3.14159265358979'::double precision) / '180'::double precision))) * power(sin((((('-73.567256'::double precision - longitude) * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision)))))) >= '0'::double precision) AND (('7917.511728464'::double precision * asin(sqrt((power(sin((((('45.501689'::double precision - latitude) * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision) + (('0.70088823836273'::double precision * cos(((latitude * '3.14159265358979'::double precision) / '180'::double precision))) * power(sin((((('-73.567256'::double precision - longitude) * '3.14159265358979'::double precision) / '180'::double precision) / '2'::double precision)), '2'::double precision)))))) <= '1000'::double precision))
(11 rows)

Upvotes: 0

Views: 793

Answers (2)

AntonTkachov
AntonTkachov

Reputation: 1784

You have not so easy question and my answer is built on my assumption and code that I see. I am sure that with your feedbacks and cooperation we will make it :)

I think that first major issue is that you have separate queries to requests table for each item_id and this is definitely a bottleneck.

STEP1: You can improve your fetch addresses code as follows:

@addresses = Address.joins("INNER JOIN users ON users.id = addresses.addressable_id AND addresses.addressable_type = 'User' INNER JOIN items ON items.user_id = users.id")
                    .where.not({ 
                                 items: {
                                   name: [nil, ''],
                                   photo: [nil, ''],
                                   visibility: false
                                 },
                                 addresses: { latitude: nil }
                               })
                    .near([latitude, longitude], (params[:distance].to_i + 1000))
                    .select('addresses.*, items.id AS item_id')

STEP2: Remove @places = query. At least I don't see any place where you use it

STEP3: Prevent (N + 1) queries with includes:

@requests = Request.where(item_id: @addresses.map(&:item_id).uniq).where('created_at < ? AND created_at > ?', (DateTime.now - 1.day), (DateTime.now - 6.months)).to_a
render json: {markers: @addresses.uniq.map { |address|
                  [{
                    name: address.user.items.first.name,
                    photo: { uri: address.user.items.first.photo.url },
                    id: Item.where(user_id: address.addressable_id).first.id,
                    latitude: address.latitude,
                    longitude: address.longitude,
                    breed: address.user.items.first.breed.id,
                    innactive: checkitem(@address.user.items.first, @requests) ? false : true,
                    power: (address.user.items.first.requests.count >= 2 && address.user.items.first.requests.last(3).map(&:status).count('pending') < 1) ? true : false,
                  }]
            }.reject { |e| e.nil? }.flatten.first(100)
          }
end

STEP4: Remove queries from checkitem:

def checkitem(item, requests)
  begin
    statuses = requests.select { |r| r.item_id = item.id }.map(&:status)

    if (requests.exists? && requests.count > 2)
      if requests.count('pending') >= 3 && (item.user.current_sign_in_at.present? && item.user.current_sign_in_at < (DateTime.now - 2.weeks))
        false
      else
        true
      end
    elsif (requests == [] || requests.count <= 2)
      true
    elsif (item.user.current_sign_in_at.present? && item.user.current_sign_in_at > (DateTime.now - 2.weeks)) || item.user.created_at > (DateTime.now - 2.weeks)
      true
    else
      false
    end
  rescue
    true
  end
end

This code still smell a lot, but let's take it as a first step and go further. For additional changes I will need a bit more code pieces/etc, but I really assume that this should remove main bottleneck.

Upvotes: 2

user2102266
user2102266

Reputation: 539

I don't know anything about Crystal on Train Track but if your issue is specifically caused by SQL queries which are taking too long to post an output. You can try these.

You join a Users_Table with addresses then You take items_Table and join it to previous operation.

Before you filter these;

  • items_name should be NOT NULL
  • items_name should be not ''
  • items_photo should be NOT NULL
  • items_photo should be not ''
  • items.visibility = TRUE
  • addresses.latitude should not be NULL
  • and some more which can not be easily avoided i assume.

I am not sure about your design but some of the above can be avoided. What i would do creating a VIEW. Static conditions between users like NOT NULLs would already be filtered and wouldn't need to be executed each time.

A view called (Showable_Items) which are all items but

  • items_name should be NOT NULL
  • items_name should be not ''
  • items_photo should be NOT NULL
  • items_photo should be not ''
  • items.visibility should be TRUE

A view called (Addressable_Addresses) which are all addresses but

  • addresses.latitude should not be NULL

And join these 2 view with ready to use content.

Also try; What is your most critical filter parameter. coordinates comparison possibly filters 99.9% of the table. So this table should also be divided. VIEWS again . ALL_ADDRESSES_VIEW BUT which latitude between 10 and 15 and such. whatever makes sense with your design.

Upvotes: 0

Related Questions