Topic: Slow query

Hi, here at my work we had an old database and I'm trying to build a rails app using that database.
One table has a arround 24k rows and the query runs very slow.

Processing by VpsController#index as HTML
  Vp Load (238.4ms)  SELECT `vps`.* FROM `vps` 
  EXPLAIN (0.8ms)  EXPLAIN SELECT `vps`.* FROM `vps` 
EXPLAIN for: SELECT `vps`.* FROM `vps` 
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | vps   | ALL  | NULL          | NULL | NULL    | NULL | 24021 |       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
1 row in set (0.00 sec)

  Rendered vps/index.html.erb within layouts/application (78171.2ms)
Completed 200 OK in 81754ms (Views: 78330.9ms | ActiveRecord: 241.1ms)

Is it possible to improve this without changing the database structure?

Re: Slow query

All you are doing is select * from vps;  There's not much opportunity to do anything there.

But look at what rails is telling you,  out of a total of 81743 ms,  ActiveRecord was only responsible for 241 ms,  the view code is responsible for the VAST majority of the slowness.

You'd better post vps/index.html.erb,  something in there is chewing up lot's of time,  the database is fine!

Joe got a job, on the day shift, at the Utility Muffin Research Kitchen, arrogantly twisting the sterile canvas snout of a fully charged icing anointment utensil.

Re: Slow query

Well, my view is scaffold generated

<h1>Listing vps</h1>

<table>
  <tr>
    <th>Number</th>
    <th></th>
    <th></th>
    <th></th>
  </tr>

<% @vps.each do |vp| %>
  <tr>
    <td><%= vp.number %></td>
    <td><%= link_to 'Show', vp %></td>
    <td><%= link_to 'Edit', edit_vp_path(vp) %></td>
    <td><%= link_to 'Destroy', vp, confirm: 'Are you sure?', method: :delete %></td>
  </tr>
<% end %>
</table>

<br />

<%= link_to 'New Vp', new_vp_path %>

Maybe tables are not the best way to present this data. Anyway, if I can't make this work there are some ideas to change this query to something that will return less rows.

Re: Slow query

You need kaminari to paginate,  it's pretty easy to implement,  your controller would simply have to be modified slightly.

i.e.

def index
   @vps = Vps.all.page(params[:page]).per(50)
end

Then in your view somewhere you stick

<%= paginate @vps %>

That's it,  now you'd only be grabbing 50 records at a time,  or in your case maybe you'd want 1000 records per page,  and you'd have 24 page links.

Joe got a job, on the day shift, at the Utility Muffin Research Kitchen, arrogantly twisting the sterile canvas snout of a fully charged icing anointment utensil.

Re: Slow query

Thanks Brad, I'm using will_paginate to display 1000 records per page and it's very nice.
Is kaminari better than will_paginate?

Re: Slow query

will_paginate wasn't updated very recently last time I checked, about 6 months ago,  and I had some issue with it,  so I switched to kaminari, and the issue went away.  I think it's better.  If will_paginate isn't giving you any problems,  then I'd stick with it,  they both do the same thing as far as the SQL queries,  so kaminari won't change your performance issues, (I don't THINK)

Joe got a job, on the day shift, at the Utility Muffin Research Kitchen, arrogantly twisting the sterile canvas snout of a fully charged icing anointment utensil.