'How can I refactor this LINQ to be more performant?
I have a console app that processes orders generated since 18:30 on the previous day. The method returns an array of order numbers.
The first operation is to retrieve a list of orders from a table called Docstore (which contains orders that have been printed, which are the only orders that are needed) into an array:
string[] orders;
using (var db = new TCRContext())
{
var query = from docs in db.Docstore
where docs.DateCreated >= fromDate && docs.DocumentType == "InvoiceCredit"
select docs.PrimaryKey.Trim();
orders = query.Distinct().ToArray();
}
Typically there may be at most 40 such orders. There are a couple of further considerations. First, there's a database VIEW called MatchingCustomerOrders which JOINs Orders (from the actual Order table, not the Docstore table) onto another table CPCustomers which contains a subset of all the company's Customers who participate in this particular operation (about 90 out of 2,370).
CREATE OR ALTER VIEW [dbo].[MatchingCustomerOrders]
AS
SELECT
Orders.order_no,
Orders.invoice_date
FROM
Orders INNER JOIN
dbo.CPCustomer ON Orders.customer = dbo.CPCustomer.Customer
WHERE
(dbo.CPCustomer.IsDeleted = 0)
GO
So we only need orders that match these customers. And we want to restrict them to those which match the invoice date parameters (i.e. from 18:30 the previous day to now) and also we don't want to process orders that have already been processed (hence the ProcessedInvoices part)
var query = from matches in db.MatchingCustomerOrders
where orders.Contains(matches.order_no.Trim()) &&
!(from processed in db.ProcessedInvoices select processed.order_no.Trim()).Contains(matches.order_no.Trim()) &&
matches.invoice_date >= fromDate.Date && matches.invoice_date <= toDate
select matches.order_no;
orders = query.Distinct().ToArray();
This is not particularly performant because the VIEW MatchingCustomerOrders has 90,000+ rows, and my suspicion is that LINQ is operating over the whole set. I've been banging my head trying to refactor this but none of my solutions seems to work.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
