Improve performance of instrument list

[peer35]

https://database.cosmin.nl/instruments

• Check server performance (atp)
• Improve query
  instruments_controller.rb

...
        @instruments, @alphaParams = Instrument.all
                                         .alpha_paginate(params[:letter], {:default_field => '0-9', :include_all => false, :js => false, :bootstrap3 => true}) { |instrument| instrument.name }
        @instruments.sort_by! { |m| m.name.downcase }
...

This queries all instruments in batches of 500 and then filters them. Slow.

views/instruments/index.html.erb

...
          <td><%= instrument.records.count %></td>
...
          <td>
            <% if instrument.records.count == 0 %>
              <%= link_to 'Delete', instrument, method: :delete, data: {confirm: 'Are you sure?'} %>
            <% end %>
          </td>
...

Results in 2 queries per row (although the second one is from cache). Also slow

• Use jquery datatables with paginated queries.

[peer35]

See https://github.com/lingz/alphabetical_paginate/blob/master/lib/alphabetical_paginate/controller_helper.rb from line 52:

      if params[:db_mode]
        if !ActiveRecord::Base.connection.adapter_name.downcase.include? "mysql"
          raise "You need a mysql database to use db_mode with alphabetical_paginate"
        end

        if all
          output = self
        else

          # In this case we can speed up the search taking advantage of the indices
          can_go_quicker = (current_field =~ params[:language].letters_regexp) || (current_field =~ /[0-9]/ && params[:enumerate])


          # Use LIKE the most as you can to take advantage of indeces on the field when available
          # REGEXP runs always a full scan of the table!
          # For more information about LIKE and indeces have a look at
          # http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx

          # Also use some sanitization from ActiveRecord for the current field passed
          if can_go_quicker
            output = self.where("LOWER(%s) LIKE ?" % params[:db_field], current_field+'%')
          else
            regexp_to_check = current_field =~ /[0-9]/ ? '^[0-9]' : '^[^a-z0-9]'
            output = self.where("LOWER(%s) REGEXP '%s.*'" % [params[:db_field], regexp_to_check])
          end
        end

Could be change to something like

...
        if !ActiveRecord::Base.connection.adapter_name.downcase.include? "postgresql"
          raise "You need a postgresql database to use db_mode with alphabetical_paginate"
        end
...
            output = self.where("LOWER(%s) SIMILAR TO '%s.*'" % [params[:db_field], regexp_to_check])
...

[peer35]

Also alphabetical_paginate seems not to be maintained, another reason to switch to something based on datatables?

[peer35]

Record count problem:

• Instead of Instrument.paginate do a join with record_instrument for the count and then paginate.
• Base on a database view which includes the COUNT of records.
  See https://www.netguru.com/blog/database-views-and-how-to-use-them-in-a-ror-based-app

[peer35]

https://stackoverflow.com/questions/30133478/rails-get-count-of-association-through-join-table
Something like:

  Instrument.select('instrument.id, instrument.name, instrument.url1, instrument.url2, instrument.url3, instrument.status, count(record.id) as count').group('instrument.id').map do |c|
    {
      name: c.name,
      url1: c.url1,
      url2: c.url2,
      url3: c.url3,
      status: c.status,
      count: c.count
    }
  end

[peer35]

I've now reduced the size and number of queries, directly loading the list is now quicker.

However, the biggest problem seems to be updating an instrument, it takes ~1.5 second to update associated records. Some instruments have 20 associated records, that's 30 seconds! Opening a new issue #41

[peer35]

• Updating associated records is now asynchronous, so it feels a lot quicker.

• Server performance is still not good, server is a lot slower than a virtualbox VM.