Problem with request SQL too slow

[rtherisod]

Hi, I have a problem with Wice_Grid gem when lots of data.

In my controller, i have this code for initialize grid

@customers = initialize_grid(Customer, :name => 'customer_grid', :include => [:main_contact, :students, :requests], :conditions => ["relations.from_website IS NULL OR relations.from_website = 0"], :custom_order => { 'contacts.country_id' => "(SELECT countries.name FROM countries WHERE countries.id = contacts.country_id)", 'students.birthdate' => "CASE WHEN students.birthdate IS NULL THEN 1 ELSE 0 END, students.birthdate" } )

And in the view

` <%= grid(@customers) do |g|

          g.row_attributes do |customer|
            {href: customer_path(customer)}
          end

          g.blank_slate  do
            content_tag :div, "No records found", class: 'well'
              end

          g.column name: t("activerecord.attributes.relation.lastname"), attribute: 'lastname' do |customer|
            customer.lastname + ((!customer.particule.blank?) ? ", " + customer.particule : "")
          end

          g.column name: t("activerecord.attributes.relation.firstname"), attribute: 'firstname' do |customer|
            customer.firstname
          end

          g.column name: t("activerecord.model.language.one"), attribute: 'language_id', custom_filter: Language.all.map{|c| [c.name, c.id]} do |customer|
            customer.language.name if customer.language
          end

          g.column name: t("activerecord.model.country.one"), attribute: 'country_id',  model: 'MainContact', custom_filter: Country.all.map{|c| [c.name, c.id]} do |customer|
            customer.main_contact.country.name if customer.main_contact.country if customer.main_contact
          end

          g.column name: t("activerecord.attributes.contact.address_province"), attribute: 'address_province',  model: 'MainContact' do |customer|
            customer.main_contact.address_province if customer.main_contact
          end

          g.column name: t("activerecord.attributes.contact.address_zip"), attribute: 'address_zip',  model: 'MainContact' do |customer|
            customer.main_contact.address_zip if customer.main_contact
          end

          g.column name: t("activerecord.attributes.contact.address_city"), attribute: 'address_city',  model: 'MainContact' do |customer|
            customer.main_contact.address_city if customer.main_contact
          end

          g.column name: t("activerecord.attributes.relation.created_at"), attribute: 'created_at' do |customer|
            customer.created_at.strftime("%d %b. %Y")
          end 

          g.column name: t("activerecord.attributes.contact.is_valid_address"), attribute: 'is_valid_address',  model: 'MainContact' do |customer|
            if customer.main_contact
                t(customer.main_contact.is_valid_address.to_s)
            else
                t(false.to_s)
            end
          end 

          g.column name: t("activerecord.attributes.contact.is_advertising_target"), attribute: 'is_advertising_target',  model: 'MainContact' do |customer|
            if customer.main_contact
                t(customer.main_contact.is_advertising_target.to_s)
            else
                t(false.to_s)
            end
          end 

          g.column name: t(".created_date_requests"), attribute: 'created_at',  model: 'Request' do |customer|
            customer.requests.group("DATE(created_at)").map { |e| e.created_at.strftime("%d-%m-%Y") }.join(", ")
          end 


          g.column name: t(".birthdate_students"), attribute: 'birthdate',  model: 'Student' do |customer|
            customer.students.group(:birthdate).select { |s| !s.birthdate.blank? }.map { |e| e.birthdate.strftime("%d-%m-%Y") }.join(", ")
          end 

          g.column name: t("layouts.application.archived"), attribute: 'archived' do |customer|
            (customer.archived.blank?) ? t(false.to_s) : t(customer.archived.to_s)
          end

    end -%>`

When i look the console output, il have something like this

(12589.5ms) SELECT COUNT(DISTINCTrelations.id) FROMrelationsLEFT OUTER JOINcontactsONcontacts.contactable_id=relations.idANDcontacts.typeIN ('MainContact') ANDcontacts.contactable_type= 'Relation' ANDcontacts.type= 'MainContact' LEFT OUTER JOINkinshipsONkinships.relation_id=relations.idLEFT OUTER JOINstudentsONstudents.id=kinships.student_idLEFT OUTER JOINrequestsONrequests.relation_id=relations.idWHERErelations.typeIN ('Customer') AND ((relations.from_website IS NULL OR relations.from_website = 0) AND ( (relations.archived = 0 or relations.archived is null) ))

This request return a count of 6216 records in more than 30 seconds. Why does this query takes so long ?

Thanks a lot for your help !

[leikind]

I'm sorry, I have no idea why your database is slow, and I think you are looking for help in the wrong place

[rtherisod]

OK, thank you for your suggestion, I will review the structure of my data base