Improve performance on LINQ Query

Question

My linq query goes slow when I try to loop through the results to create an Xelement, which I later process XSLT based on the XElement.

Here is my code

public override XElement Search(SearchCriteria searchCriteria)
    {
        XElement root = new XElement("Root");
        using (ReportOrderLogsDataContext dataContext = DataConnection.GetLinqDataConnection<ReportOrderLogsDataContext>(searchCriteria.GetConnectionString()))
        {
            try
            {


                IQueryable<vw_udisclosedDriverResponsePart> results = from a in dataContext.vw_udisclosedDriverResponseParts
                              where
                                  (a.CreateDt.HasValue &&
                                   a.CreateDt >= Convert.ToDateTime(searchCriteria.BeginDt) &&
                                   a.CreateDt <= Convert.ToDateTime(searchCriteria.EndDt))
                              select a;

                if (!string.IsNullOrEmpty(searchCriteria.AgentNumber))
                {
                    results = results.Where(request => request.LgAgentNumber == searchCriteria.AgentNumber);
                }
                if (!string.IsNullOrEmpty(searchCriteria.AgentTitle))
                {
                    results = results.Where(a => a.LgTitle == searchCriteria.AgentTitle);
                }
                if (!string.IsNullOrEmpty(searchCriteria.QuotePolicyNumber))
                {
                    results = results.Where(a => a.QuotePolicyNumber == searchCriteria.QuotePolicyNumber);
                }
                if (!string.IsNullOrEmpty(searchCriteria.InsuredName))
                {
                    results = results.Where(a => a.LgInsuredName.Contains(searchCriteria.InsuredName));
                }

                foreach (var match in results) // goes slow here, specifically times out before evaluating the first match when results are too large.
                {
                    DateTime date;
                    string strDate = string.Empty;
                    if (DateTime.TryParse(match.CreateDt.ToString(), out date))
                    {
                        strDate = date.ToString("MM/dd/yyyy");
                    }

                    root.Add(new XElement("Record",
                                          new XElement("System", "Not Supported"),
                                          new XElement("Date", strDate),
                                          new XElement("Agent", match.LgAgentNumber),
                                          new XElement("UserId", match.LgUserId),
                                          new XElement("UserTitle", match.LgTitle),
                                          new XElement("QuoteNum", match.QuotePolicyNumber),
                                          new XElement("AddressLine1", match.AddressLine1),
                                          new XElement("AddressLine2", match.AddressLine2),
                                          new XElement("City", match.City),
                                          new XElement("State", match.State),
                                          new XElement("Zip", match.Zip),
                                          new XElement("DriverName", string.Concat(match.GivenName, " ", match.SurName)),
                                          new XElement("DriverLicense", match.LicenseNumber),
                                          new XElement("LicenseState", match.LicenseState)));
                    ;
                }
            }
            catch (Exception es)
            {

                throw es;
            }
        }
        return root;
        // return GetSearchedCriteriaFromStoredPocedure(searchCriteria);
    }

I assume there is a better way to convert the results object into an XElement. Processing the view itself only takes about 2 seconds. Trying to loop through the results object is resulting in a timeout, even when many results are not returned.

Any help would be appreciated.

Thanks!

-James

AMENDED 7/10/2012

The issue is not with the linq query itself but its with the execution of the view when specifying a date range. Executing the view by itself takes about 4-6 seconds. When a small date range (07/05/2012 - 07/10/2012) is used the view takes around 1:30. Does anyone have any suggestions of how to increase performance of the query with a date range specified. Its faster if I got all of the results and looped through them checking the date.

i.e.

    IQueryable<vw_udisclosedDriverResponsePart> results = from a in dataContext.vw_udisclosedDriverResponseParts select a;

                foreach (var match in results) //results only takes 3 seconds to enumerate, before would timeout
                {
                    // eval search criteria date here.
                }

I can code it like I suggested above, but does anyone have a better way?

Answer 1

I would suggest few experiments:

One.
Put a

int count = results.Count();

before the foreach and see if this takes a long time.

Two.
Leave the the Count() call and see if the foreach is still slow. If it is fast it would suggest that the initial connection to the db is slow.

As others suggested - have a look how you query performs in the db (actually type in in the database, without c#).

You could also post a SHOW TABLE result so the community could inspect the indexes and help you with a fix.

Answer 2

And, of course, do the conversion outside the linq. criteria won't change during the runtime of the Linq expression.

From what you posted, I made this example:

var begin = Convert.ToDateTime(searchCriteria.BeginDt);
var end = Convert.ToDateTime(searchCriteria.EndDt);

var results = from a in searchList
              where ((a.CreateDt.HasValue &&
                      a.CreateDt >= begin &&
                      a.CreateDt <= end)
                      && (string.IsNullOrEmpty(searchCriteria.AgentNumber) || a.LgAgentNumber == searchCriteria.AgentNumber)
                      && (string.IsNullOrEmpty(searchCriteria.AgentTitle) || a.LgTitle == searchCriteria.AgentTitle)
                      && (string.IsNullOrEmpty(searchCriteria.QuotePolicyNumber) || a.LgTitle == searchCriteria.QuotePolicyNumber)
                      && (string.IsNullOrEmpty(searchCriteria.InsuredName) || a.LgInsuredName.Contains(searchCriteria.InsuredName))
                     )
                        select a;

Perhaps this is helpful for you.

For measuring the time I used the following:

var watch = new Stopwatch();
watch.Start();
var arr = results.ToArray();  // force evaluation of linq
watch.Stop();
var elapsed = watch.ElapsedTicks;

Seems the altered query is already about 30-40% faster on average, but i just did some runs.

Answer 3

How does the database perform? The simplest test is to run a sample query - a query that will retrieve the data you need from the database, just to test database indexing and performance - because in 99% of cases that's the cause of slowness.

I would guess that the slowness is occurring because

• you are iterating from the database, rather than retrieving all the rows up front, and
• you are selecting on bad WHERE conditions (are your indexes correct?)

Firstly, call ToList to get the results to determine that the slowness is happening in the database, not in the XML construction

if (!string.IsNullOrEmpty(searchCriteria.InsuredName))
{
    //...
}    
var matches = results.ToList();    
foreach (var match in matches) 
{
    //...

Assuming that the var matches = results.ToList() is very slow, I'd look at the functions in the WHERE clause

(a.CreateDt.HasValue &&
a.CreateDt >= Convert.ToDateTime(searchCriteria.BeginDt) &&
a.CreateDt <= Convert.ToDateTime(searchCriteria.EndDt))

to check that they aren't being executed for every row.

If you use SQL Server, run Profiler (in the Tools menu) to trace the SQL that LINQ-to-SQL.