Sorting 1100 rows extremely slow ~20 mins

Tomanow

Hi, I'm experiencing a strange problem where trying to sort any columns results in about a 20 minute wait period. After it is done processing, the columns can be sorted instantly. This is strange because it is only 1100 rows. I greatly appreciate any help on this. I'm using Ajax / server side processing. Also, the search and pagination have almost no delay. Why would sorting be so slow? I'm using Ruby/Rails.

I will be happy to make a donation to the project for any support!

[code]
$("#datatable").dataTable({
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": $('#datatable').data('source'),
"bAutoWidth": true
});

$.extend( $.fn.dataTableExt.oStdClasses, {
"sSortAsc": "header headerSortDown",
"sSortDesc": "header headerSortUp",
"sSortable": "header"
} );
[/code]
--
[code]
class ClientsDatatable
delegate :params, :h, :link_to, :mail_to, :number_to_currency, to: :@view

def initialize(view)
@view = view
end

def as_json(options = {})
{
sEcho: params[:sEcho].to_i,
iTotalRecords: Client.count,
iTotalDisplayRecords: clients.total_entries,
aaData: data
}
end

private

def data
clients.map do |client|
[
client.first_name,
client.middle_name,
client.last_name,
client.employer,
client.account_number,
mail_to(client.email.downcase, client.email.downcase),
client.account_status,
Client.find_user_name_by_id(client.broker_id),
client.date_updated,
link_to('Show', client),
link_to('Edit', "clients/#{client.id}/edit"),
link_to('Destroy', client, method: :delete, data: { confirm: 'Are you sure?' })
]
end
end

def clients
@clients ||= fetch_clients
end

def fetch_clients
clients = Client.order("#{sort_column} #{sort_direction}")
clients = clients.page(page).per_page(per_page)
if params[:sSearch].present?
clients = clients.where("last_name like :search or first_name like :search", search: "%#{params[:sSearch]}%")
end
clients
end

def page
params[:iDisplayStart].to_i/per_page + 1
end

def per_page
params[:iDisplayLength].to_i > 0 ? params[:iDisplayLength].to_i : 10
end

def sort_column
columns = %w[last_name]
columns[params[:iSortCol_0].to_i]
end

def sort_direction
params[:sSortDir_0] == "desc" ? "desc" : "asc"
end
end
[/code]
--
[code]



First name
Middle
Last name
Firm
Account number
Email
Account status
Broker Name
Date updated
Show
Edit
Delete






[/code]

PhilG

Given that you're using server side processing, the sorting is occurring on the server side. The best thing to do would be to see what SQL statements are being emitted and whether they are optimal.

I've fixed sorting performance issues where the sort was slow because we were sorting on columns that were the result of a join. Removing those sped up sorting immensely. This may not be the case here, but it illustrates that sorting performance problems are going to be server side.

Tomanow

Thank you for your response, Phil. The table is not the result of any joins, however. It is a simple clients table with basic information.

allan

Regardless, if you are using server-side processing, the sort is occurring at the server and not in DataTables itself. I'd say you will want to look at the SQL that is being used and analyse the program that be handing your server-side processing request. 20 minutes for 1100 is crazy. Even IE6 could do better than that ;-)

Allan

Tomanow

Thanks for the response Allan. I managed to solve the issue! It was this line:

[code]
columns = %w[last_name]
[/code]

which wasn't processing correctly because I guess the %w(word array) creates an issue with a single value (it is not an array). When I populated it with more values the sorting worked flawlessly. Hopefully this helps others.

pmccann

I'm pretty sure that the %w construct is fine with one element. The console shows it produces an array with one item. But if params[iSortCol_0].to_i isn't equal to 0 won't your sort command be

Client.order("desc")

or Client.order("asc")? This should give an error when rails sends the appropriate SQL to the DB (and does at my end). Weird... I have no idea what would cause a 20 minute delay unless the SQL is being interpreted in some other way by the DB. Just out of interest: what happens when you make that command in the rails console?

clients = Client.order("desc") # for example...