Optimizing sorting when sort column is string input

Question

Unfortunately, I have a large table with potentially thousands of rows, and the users can sort it at runtime by clicking on table headers. I'm using the datatables jQuery plugin for my front-end table interface, but due to the large amount of data available, I'm handling all search, sorting and paging from the server. This is a table that tracks projects over my company's entire history. My problem comes from when users start sorting "completed" projects. There are in excess of 7,000 completed projects, and they might be sorted by any of several foreign key relationships to other tables. As an example, I'll list the relevant part of my query that does the actual sorting:

if (sortcolumn == "ProjectType") return projects.OrderBy(p => p.ProjectType.Name);

So far, since the sort column is passed in at runtime, I'm testing sortcolumn and sortdirection by simple string comparisons. My Project table has the ProjectTypeID field, but then has to sort 7,000+ projects through its relationship to the ProjectType table. This is killing my performance. Is there a better means for sorting at runtime? I'm already using Scott Gu's dynamic linq library that allows passing in strings for column names, but in this case it doesn't help. 

Answer 1

Hi Ironwil,

Welcome to the MSDN forum.

I recommend you take a look at this page about High Performance Dynamic Sorting Using Expressions: http://www.codeproject.com/Articles/29660/Dynamite-High-Performace-Dynamic-Sorting-Using-Exp

Good day.

---

Alexander Sun [MSFT]
MSDN Community Support | Feedback to us