Improve speed of LINQ query

Question

How can I improve the speed of this LINQ query?

using (var txn = new TransactionScope(TransactionScopeOption.Required,
        new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
     var myAlertsList = (from alerts in _db.Alerts.AsNoTracking()
                         where alerts.AlertID >= Id.AlertID && alerts.DataWystapienia >Id.DataWystapienia
                         select new MyAlerts { AlertID = alerts.AlertID, DataWystapienia = alerts.DataWystapienia, Message = alerts.Message })
                        .OrderByDescending(a => a.AlertID).Take(Id.NumberOfRows).Concat((
                         from subAlerts in _db.SubAlerts.AsNoTracking()
                         where subAlerts.AlertID >= Id.AlertID && subAlerts.DataWystapienia > Id.DataWystapienia
                         select new MyAlerts { AlertID = subAlerts.AlertID, DataWystapienia = subAlerts.DataWystapienia, Message = subAlerts.Message })
                        .OrderByDescending(c => c.AlertID).Take(Id.NumberOfRows)).OrderByDescending(a => a.AlertID).Take(Id.NumberOfRows);

     return myAlertsList;
}

It seems like the order by date is the longest process in this query. Is this correct? The database has about a million records. NumberOfRows is max 200. Time of this query is 45ms (after using only one orderby). Is there something more that I can do to improve that LINQ query?

you have 3 OrderByDescendings followed by takes of which you can remove all but the last — ratchet freak
– ratchet freak, Commented Nov 22, 2013 at 14:02
@ratchetfreak, explain that more in an answer, that is a review of the code, and is a good answer as long as you explain it a little bit more. — Malachi
– Malachi, Commented Nov 22, 2013 at 15:01

ratchet freak · Accepted Answer · 2013-11-22 15:15:00Z

First lets split up the query so we can read it:

var alertList = from alerts in _db.Alerts.AsNoTracking()
                where alerts.AlertID >= Id.AlertID && alerts.DataWystapienia >Id.DataWystapienia
                select new MyAlerts { AlertID = alerts.AlertID, DataWystapienia = alerts.DataWystapienia, Message = alerts.Message };

var subAlertList = from subAlerts in _db.SubAlerts.AsNoTracking()
                   where subAlerts.AlertID >= Id.AlertID && subAlerts.DataWystapienia > Id.DataWystapienia
                   select new MyAlerts { AlertID = subAlerts.AlertID, DataWystapienia = subAlerts.DataWystapienia, Message = subAlerts.Message };

var myAlertsList = alertList.OrderByDescending(a => a.AlertID).Take(Id.NumberOfRows)
                   .Concat(subAlertList.OrderByDescending(a => a.AlertID).Take(Id.NumberOfRows))
                   .OrderByDescending(a => a.AlertID).Take(Id.NumberOfRows);

this is fully equivalent to your code I only extracted the 2 lists

the last operation of code has 3 OrderByDescending(a => a.AlertID).take(Id.NumberOfRows) while only 1 is needed

var myAlertsList = alertList.Concat(subAlertList).OrderByDescending(a => a.AlertID).Take(Id.NumberOfRows);

the inside queries aren't doing anything that needs them to be in order for any reason, you just need the final result to be in order for viewing purposes I assume. — Malachi
– Malachi, Commented Nov 24, 2013 at 2:35

Stack Exchange Network

Improve speed of LINQ query

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Improve speed of LINQ query

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions