Duplicate SQL-queries in hobo_index finder

[Ronny Hanssen]

I have an index controller action that uses hobo_index with a finder to get the data my view will be using. When I use hobo_index without a finder it works fine. However, when I add a finder (a dynamic scope) then things behave weird.

This is my first attempt at making a filter so I am not sure if I am attacking this the right way. My controller looks like this:

class JobsController < ApplicationController
  ...
  # Alt 1 - standard hobo, no filters, orders, where or selects
  def index
    hobo_index :per_page => 10
  end

  # Alt 2 - Using a scope to add order from gui
  #def index
  #  hobo_index apply_order(Job.scoped) , :per_page => 10
  #end
  ...
private
  ...
  def apply_order(scope)
    order_field = session[:order_job_field]
    order_direction = session[:order_job_direction]
    if Job.order_field_list.include?(order_field) && Job.order_direction_list.include?(order_direction)
      return scope.order("jobs.#{order_field} #{order_direction=='Ascending'?'ASC':'DESC'}")
    end
    return scope
  end
  ...
end

When loading the alternative 1 (index) the log ouputs the following (I have "set_default_order 'updated_at DESC' in the Job-model)

Started GET "/jobs" for 127.0.0.1 at 2011-05-19 01:13:00 +0200
...
  Processing by JobsController#index as HTML
  SQL (0.0ms)  SELECT COUNT(*) FROM "users"
  User Load (1.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  Job Load (17.0ms)  SELECT "jobs".* FROM "jobs" ORDER BY updated_at DESC LIMIT 10 OFFSET 0
  SQL (1.0ms)  SELECT COUNT(*) FROM "jobs"
  User Load (3.0ms)  SELECT "users".* FROM "users" LIMIT 30

This seems fine, 1 job query, limiting itself to 10 hits, ordered as default.

But, when I switch to using the alternative 2 index and use a scope (alternative 2) the output looks like this (even without any specific order specified):'

Started GET "/jobs" for 127.0.0.1 at 2011-05-19 01:15:31 +0200
...
  Processing by JobsController#index as HTML
  SQL (0.0ms)  SELECT COUNT(*) FROM "users"
  User Load (1.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  Job Load (89.0ms)  SELECT "jobs".* FROM "jobs" ORDER BY jobs.updated_at DESC LIMIT 10 OFFSET 0
  SQL (1.0ms)  SELECT COUNT(*) FROM "jobs"
  Job Load (2653.2ms)  SELECT "jobs".* FROM "jobs" ORDER BY jobs.updated_at DESC
  User Load (3.0ms)  SELECT "users".* FROM "users" LIMIT 30

As you can see, hobo/rails now executes the Job select-statement twice. One time similar to the first select with a limit, as expected. The second select is unexpected though.

Can anyone explain why and possibly pinpoint an error and/or a solution?


Regards,
Ronny

[Ronny Hanssen]

Has anyone been able to add a scope to their hobo_* controller methods without getting duplicate sql-queries?

I am really puzzled by this one. I cannot see why the scope should force a duplicate sql-call...?


~Ronny

[Domizio Demichelis]

the code? :-)

dd

--
You received this message because you are subscribed to the Google Groups "Hobo Users" group.
To post to this group, send email to hobo...@googlegroups.com.
To unsubscribe from this group, send email to hobousers+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/hobousers?hl=en.

[Ronny Hanssen]

All relevant code is posted in the first message, afaict.

BTW: The first line after each of the log-samples above starts processing DRYML-pages, so the extra SQL query comes before the view comes into play, it seems.

If there is any other code you need then please tell me about it.

Thanks,
Ronny

[Domizio Demichelis]

You should investigate why when you post a message the subject changes, so the users that are reading the posts are receiving it as it where another indipendend thread (so I don't have the history, hence I asked for the code).

Please, look at the page:
http://groups.google.com/group/hobousers/browse_thread/thread/a7d7091107fb04dd?hl=en

BTW, I don't think the Job.scoped return what you want to pass to the apply_order method.

ciao
dd

--

[Ronny Hanssen]

Sorry. I wasn't aware of that. Strange. I am only answering using Google Groups. Nothing fancy. Except that it is displaying the UI in Norwegian that is... And I am unable to find a setting for it. Maybe it is using the language browser handshake?

~Ronny

[Ronny Hanssen]

But, even with ht=en, all the messages are in this thread (but I can see that the subject was changed). Including the first message with the code.

Anyway, I was sure that .scoped returned a proper scope? The hobo_* methods previously took a finder as an argument. Can they now take a scope? I really hope so, filters are a joy to write compared to in rails 1/2...

The sql-query generated (the first one) does indeed work as a scope should. I even have a more advanced version with more wheres and joins. But, somewhere in the hobo_index it seems that it also fires a full select (without limit for paging) - which is a performance killer.

~Ronny

[Matt Jones]

On May 18, 2011, at 7:35 PM, Ronny Hanssen wrote:

> When loading the alternative 1 (index) the log ouputs the following (I have "set_default_order 'updated_at DESC' in the Job-model)
> Started GET "/jobs" for 127.0.0.1 at 2011-05-19 01:13:00 +0200
> ...
> Processing by JobsController#index as HTML
> SQL (0.0ms) SELECT COUNT(*) FROM "users"
> User Load (1.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
> Job Load (17.0ms) SELECT "jobs".* FROM "jobs" ORDER BY updated_at DESC LIMIT 10 OFFSET 0
> SQL (1.0ms) SELECT COUNT(*) FROM "jobs"
> User Load (3.0ms) SELECT "users".* FROM "users" LIMIT 30
> This seems fine, 1 job query, limiting itself to 10 hits, ordered as default.
>
> But, when I switch to using the alternative 2 index and use a scope (alternative 2) the output looks like this (even without any specific order specified):'
> Started GET "/jobs" for 127.0.0.1 at 2011-05-19 01:15:31 +0200
> ...
> Processing by JobsController#index as HTML
> SQL (0.0ms) SELECT COUNT(*) FROM "users"
> User Load (1.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
> Job Load (89.0ms) SELECT "jobs".* FROM "jobs" ORDER BY jobs.updated_at DESC LIMIT 10 OFFSET 0
> SQL (1.0ms) SELECT COUNT(*) FROM "jobs"
> Job Load (2653.2ms) SELECT "jobs".* FROM "jobs" ORDER BY jobs.updated_at DESC
> User Load (3.0ms) SELECT "users".* FROM "users" LIMIT 30
> As you can see, hobo/rails now executes the Job select-statement twice. One time similar to the first select with a limit, as expected. The second select is unexpected though.
>
> Can anyone explain why and possibly pinpoint an error and/or a solution?

I've seen this happen before with when calling responds_to? on association / scope proxies. It was fixed in the 1.0 branch, but Rails 3 may have found a new way to mess it up.

--Matt Jones

[Tomoaki Hayasaka]

Hi,

From: Ronny Hanssen <super...@gmail.com>
Subject: [Hobo Users] Duplicate SQL-queries in hobo_index finder
Date: Wed, 18 May 2011 16:35:25 -0700 (PDT)

> As you can see, hobo/rails now executes the Job select-statement twice. One
> time similar to the first select with a limit, as expected. The second
> select is unexpected though.

reproduced more simply with:

>> User.paginate(:page => 1); nil
User Load (2.6ms) SELECT "users".* FROM "users" LIMIT 30 OFFSET 0

>> User.order("id").paginate(:page => 1); nil
User Load (2.7ms) SELECT "users".* FROM "users" ORDER BY id LIMIT 30 OFFSET 0
User Load (2.0ms) SELECT "users".* FROM "users" ORDER BY id desc

I filed this issue as

https://hobo.lighthouseapp.com/projects/8324-hobo/tickets/952-duplicated-sql-queries-when-paginating

and I'm looking for a solution.

-----
Tomoaki Hayasaka <haya...@pfsl.mech.tohoku.ac.jp>

[Ronny Hanssen]

Thanks for the diagnosis Tomoaki. Much appreciated. Good luck on a solution. I am not well enough acquainted in the source to fix it myself unfortunately.

~Ronny

[Ronny Hanssen]

I see that there's a checking that is meant to solve this.

I did a bundle install (my Gemfile lists: gem 'hobo', :git => 'git://github.com/tablatom/hobo.git', :branch => 'rails3') on my project, but it seems that the problem I reported is still behaving the same way.

Any clues why this still happens?

~Ronny

[Domizio Demichelis]

I usually just delete the Gemfile.lock so I have not that much experience with the bundler. I don't know whether the `bundle install` just checks the installation and doesn't update the repo. I would try to use `bundle update` that seems more appropriate.

ciao
dd

--

[Ronny Hanssen]

Thanks!

I deleted the Gemfile.lock and then ran bundle install, and this solved my issues :)

I read an article that suggested using bundle install over bundle update, but I no longer remember the reasoning behind the suggestion.. :(

 Thanks to you both, Tomoaki & Domizio :)

~Ronny

[Domizio Demichelis]

If you delete the Gemfile.lock you don't need to run bundle install :-)

ciao
dd

--

[Tomoaki Hayasaka]

Nice to hear!

Domizio, would you update the ticket status? I have no rights to do
that and I forgot trying [#952:resolved].

-----
Tomoaki Hayasaka <haya...@pfsl.mech.tohoku.ac.jp>

[Domizio Demichelis]

Done, BTW it's [#952 state:resolved].
ciao
dd

[Tomoaki Hayasaka]

> Done, BTW it's [#952 state:resolved].

oops, thanks.

-----
Tomoaki Hayasaka <haya...@pfsl.mech.tohoku.ac.jp>