Neo4j.rb - index paginated with Kaminari too slow

Question

Here is my controller (index is sorted):

  def index
    if params[:limit]
      @bisacs = Bisac.order(:bisac_code).page(params[:page]).per(params[:limit])
    else
      @bisacs = Bisac.order(:bisac_code).page(params[:page])
    end
  end

Here are the queries executed (too many in my opinion and too slow, 6-7 seconds for a first page or to navigate to he next / last one:

Started GET "/bisacs" for 127.0.0.1 at 2015-08-11 13:16:33 -0400
Processing by BisacsController#index as HTML
  Rendered home/_main_links.html.erb (0.2ms)
 CYPHER 358ms MATCH (result_bisac:`Bisac`) RETURN count(result_bisac) AS result_bisac 
 CYPHER 389ms MATCH (result_bisac:`Bisac`) RETURN result_bisac ORDER BY result_bisac.bisac_code SKIP {skip_0} LIMIT {limit_25} | {:skip_0=>0, :limit_25=>25}
 CYPHER 119ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319299}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319299}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24320808}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24320808}
 CYPHER 120ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319262}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319262}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24320349}
 CYPHER 120ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24320349}
 CYPHER 120ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24318456}
 CYPHER 119ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24318456}
 CYPHER 119ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24320054}
 CYPHER 116ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24320054}
 CYPHER 119ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24321703}
 CYPHER 119ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24321703}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319503}
 CYPHER 117ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319503}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24321755}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24321755}
 CYPHER 116ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319313}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319313}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24321376}
 CYPHER 119ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24321376}
 CYPHER 119ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24321021}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24321021}
 CYPHER 123ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319280}
 CYPHER 119ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319280}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24318845}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24318845}
 CYPHER 122ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24320822}
 CYPHER 119ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24320822}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24318841}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24318841}
 CYPHER 119ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24321956}
 CYPHER 117ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24321956}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319031}
 CYPHER 119ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319031}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24320070}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24320070}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24322195}
 CYPHER 116ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24322195}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319124}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319124}
 CYPHER 119ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24322258}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24322258}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24318767}
 CYPHER 121ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24318767}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24320583}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24320583}
 CYPHER 118ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319086}
 CYPHER 117ms MATCH n WHERE (ID(n) = {ID_n}) WITH n RETURN ID(n) | {:ID_n=>24319086}
  Rendered bisacs/index.html.erb within layouts/application (6722.1ms)
Completed 200 OK in 7096ms (Views: 7095.5ms)

How this can be improved? Why these 25 nodes are fetched one by one?

To retrieve all 3983 codes in the Neo4j console takes only 1 second or so:

MATCH (result_bisac:`Bisac`) RETURN result_bisac ORDER BY result_bisac.bisac_code;
Returned 3983 rows in 1064 ms, displaying first 1000 rows.

score 3 · Accepted Answer · answered Aug 11 '15 at 18:10

Hrmm, ok, so this is another somewhat embarrassing answer ;)

If you put the following in your config (either in config/environments/development.rb or config/application.rb) it should fix it:

config.neo4j._active_record_destroyed_behavior = true

Explination: The gem implements the ActiveNode#exists? method a bit differently than the ActiveRecord#exists? method. In ActiveRecord it just does a check to see if the object was destroyed at some point in memory. In ActiveNode we were doing a check to the actual database (and maybe we need query caching at some point).

So, long story short, we didn't want to introduce this change right away because it's a breaking change, so I put in a configuration variable which fixes it for now. The configuration variable shouldn't be neccessary in version 6.0

Worked perfect. Only one clean cypher query is executed (instead of many): Started GET "/bisacs?page=5" for 127.0.0.1 Processing by BisacsController#index as HTML Parameters: {"page"=>"5"} Rendered home/_main_links.html.erb (0.2ms) CYPHER 124ms MATCH (result_bisac:`Bisac`) RETURN count(result_bisac) AS result_bisac CYPHER 367ms MATCH (result_bisac:`Bisac`) RETURN result_bisac ORDER BY result_bisac.bisac_code SKIP {skip_100} LIMIT {limit_25} | {:skip_100=>100, :limit_25=>25} Rendered bisacs/index.html.erb within layouts/application (526.8ms) Completed 200 OK in 886ms (Views: 885.1ms) — LDB, Aug 11 '15 at 18:44

LDB · Answer 2 · 2015-08-11T18:00:03.170

I commented the following first 3 lines in the tag in the view and speed become acceptable, around one second:

<% @bisacs.each do |bisac| %>
  <tr>
    <td><%#= link_to 'Show', bisac %></td>
    <td><%#= link_to 'Edit', edit_bisac_path(bisac) %></td>
    <td><%#= link_to 'Destroy', bisac, method: :delete, data: { confirm: 'Are you sure?' } %></td>
    <td><%= bisac.bisac_code%></td>
    <td><%= bisac.bisac_value%></td>
  </tr>
<% end %>

However adding a link back to "show" makes the page rendering very slow again:

<% @bisacs.each do |bisac| %>
  <tr>
    <td><%= link_to bisac.bisac_code, bisac%></td>
    <td><%= bisac.bisac_value%></td>
  </tr>
<% end %>

Neo4j.rb - index paginated with Kaminari too slow

2 Answers2