2
\$\begingroup\$

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?

\$\endgroup\$
  • 1
    \$\begingroup\$ you have 3 OrderByDescendings followed by takes of which you can remove all but the last \$\endgroup\$ – ratchet freak Nov 22 '13 at 14:02
  • 2
    \$\begingroup\$ @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. \$\endgroup\$ – Malachi Nov 22 '13 at 15:01
5
\$\begingroup\$

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);
\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – Malachi Nov 24 '13 at 2:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.