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?

Answer 1

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);