Optimize the query PostgreSql-8.4

Question

I have rails controller coding as below:

@checked_contact_ids = @list.contacts.all(
  :conditions => {
    "contacts_lists.contact_id" => @list.contacts.map(&:id),
    "contacts_lists.is_checked" => true
  }
).map(&:id)

its equivalent to sql

SELECT *
FROM "contacts"
INNER JOIN "contacts_lists" ON "contacts".id = "contacts_lists".contact_id
WHERE ("contacts_lists".list_id = 67494 )

This above query takes more time to run, I want another way to run the same query with minimum time.

Is anyone knows please notice me Or is it possible? or is the above query enough for give output?

I am waiting information...................

mu is too short · Accepted Answer · 2011-04-20 06:20:39Z

I think the main problem with your original AR query is that it isn't doing any joins at all; you pull a bunch of objects out of the database via @list.contacts and then throw most of that work away to get just the IDs.

A first step would be to replace the "contacts_lists.contact_id" => @list.contacts.map(&:id) with a :joins => 'contact_lists' but you'd still be pulling a bunch of stuff out of the database, instantiating a bunch of objects, and then throwing it all away with the .map(&:id) to get just ID numbers.

You know SQL already so I'd probably go straight to SQL via a convenience method on your List model (or whatever @list is), something like this:

def checked_contact_ids
    connection.execute(%Q{
        SELECT contacts.id
        FROM contacts
        INNER JOIN contacts_lists ON contacts.id = contacts_lists.contact_id
        WHERE contacts_lists.list_id    = #{self.id}
          AND contacts_lists.is_checked = 't'
    }).map { |r| r['id'] }
end

And then, in your controller:

@checked_contact_ids = @list.checked_contact_ids

If that isn't fast enough then review your indexes on the contacts_lists table.

There's no good reason not go straight to SQL when you know exactly what data you need and you need it fast; just keep the SQL isolated inside your models and you shouldn't have any problems.

Collectives™ on Stack Overflow

Optimize the query PostgreSql-8.4

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related