CODEWITHSUNDEEP
sql-serverperformancelinq-to-sqldatatableiorderedenumerable
Ruutert
Ruutert

Reputation: 395

Improve query performance to order records by Linq-to-SQL

I'm using a query to get records from a table named customers and I want to order the records by address, housenumber, surname, name.

First I used this (DataTable)

public CustomerInfo.customers GetCustomers(string zipcode) {
     string sql = "select id, name, surname, zipcode, housenumber where zipcode = @_zipcode order by address, housenumber, surname, name";
 ....     
}

now I use this:

public OrderedEnumerableRowCollection<CustomerInfo.customerRow> GetCustomers(string zipcode) {
     string sql = "select id, name, surname, zipcode, housenumber where zipcode = @_zipcode";

     ....

     return (from c in datatable).OrderBy(c => c.Address).ThenBy(....).ThenBy(...);       
}

Is that the right way to improve performance ...?

What are the (dis)advantages of the OrderedEnumerableRowCollection vs DataTable?

Please let me know how you should do this.

Upvotes: 1

Views: 222

Answers (1)

Joel Mansford
Joel Mansford

Reputation: 1316

I would say it depends on the indexing at your SQL Server as to which is faster. For instance if the SQL Server is indexed such that it can do the ordering quickly (or the data is very small) then the data table is fine.

I would generally expect the SQL server to be more efficient at this than the .NET code.

On a general architectual note though. If the ordering is a UI concern (it's not clear from your question if it is) then the ordering client-side makes more sense, especially if you'll use the datatable elsewhere (potentially in a different order).

Upvotes: 0

Related Questions