wcf serialize linq results causes massive sql server load

I am trying to get serialized results from a WCF service from database using linq.

The tables in the db are normalized to some extent and I'm returning some other data along with the on that I initially retrieve with a linq query using Data.Linq.DataLoadOptions, just like in Scott Landford's Blog: http://codeexperiment.com/post/Returning-LINQ-to-SQL-Entities-From-WCF.aspx

Here's a p开发者_运维技巧art of my code that is relevant:

    ServerDALDataContext db = new ServerDALDataContext();

    System.Data.Linq.DataLoadOptions dlo = new System.Data.Linq.DataLoadOptions();
    // get COMPETITOR_ENTRies data along with COMPETITION
    dlo.LoadWith<COMPETITION>(e => e.COMPETITOR_ENTRies);
    // get dividends for competitors along with COMPETITOR_ENTRies
    dlo.LoadWith<COMPETITOR_ENTRY>(e => e.DIVIDENDs);
    db.LoadOptions = dlo;

    // retrieve MEETING data from database
    var competitions = (from c in db.COMPETITIONs
                        select c)
        .AsEnumerable()
        .Where(c => c.CONTROL_UPDATE_DATA.FOR_UPDATE); 
            && c.COMPETITION_DATETIME.Value.Date == dateFrom.Date);

    // return as list            
    return competitions != null ? competitions.ToList() : null;

There is also a client application that consumes the service, sending asynchronous requests to the WCF service on every 10 seconds or so.

The problem that arrises is that when this is used, it actually overloads the SQL server to such extent that it uses 100% CPU all the time, causing the responses to the client to be late. I remove the dlo.LoadWith calls and responses come in a meaningful time.

Any suggestions on how to resolve this issue, and not overload the SQL server that much?

// retrieve MEETING data from database 
var competitions = (from c in db.COMPETITIONs 
                    select c) 
    .AsEnumerable() 
    .Where(c => c.CONTROL_UPDATE_DATA.FOR_UPDATE);  
        && c.COMPETITION_DATETIME.Value.Date == dateFrom.Date); 

AsEnumerable...

You're loading the whole table.

what's is a good approach in this kind of situation?

Either set the dataContext.Log = Console.Out; or fire up sql profiler and look at what's being sent into the database.

From there two possibilities:

1. You are sending into the database well filtered queries that fetch the results you want - but they are performing poorly. Put the queries into SqlStudio and click the button "Display Estimated Execution Plan". Also use SET STATISTICS IO ON and SET STATISTICS TIME ON, then look at the messages tab. The Solution typically involve adding indexes.

2. You are sending badly filtered queries, or queries that fetch the data piecewise (many roundtrips). Go back to the c# code that generated the queries and figure out what the error is. Solutions typically involve placing Where correctly, or doing a Join instead of a GroupBy, and definitely no query execution within a loop.

Proper date filtering with a time trim:

DateTime theDate = DateTime.Now;
  //manipulate date into a range locally.
DateTime startDate = theDate.Date;
DateTime endDate = startDate.AddDays(1);

  //filter by the range - endpoint excluded.
IEnumerable<Order> query = myDC.Orders.Where(order =>
   startDate <= order.OrderDate
   && order.OrderDate < endDate);