Linq to SQL performance

TJones posted on Friday, October 31, 2008

While upgrading to csla 3.5.1 I've run into the following problem.

The original code, Method 1 below, takes less than 1 second to load 249 records, and the new code, Method 2 below, takes approximately 8 seconds to load the same 249 records using the same stored procedure.
Anyone have any suggestions as to why the Linq to SQL approach would perform so much slower.
Method 1: CSLA 3.0
        using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
        {
          while (dr.Read())
            this.Add(MyObject<C>.GetMyObject(dr));
        }

Method 2: CSLA 3.5 Using Linq to SQL
        this.AddRange(
          from row in mgr.DataContext.spGetMyObject(criteria.Value)
          select MyObject.GetMyObject(row)
        );

ajj3085 replied on Friday, October 31, 2008

Linq is built on top of Ado.Net; it's not going to perform better, it will always add some overhead. That said, it could be how you're building your linq statement.

What is the .Net type of row? There's probably something going on there..

TJones replied on Friday, October 31, 2008

row is the result of mgr.DataContext.spGetMyObject(criteria.Value), which is a stored procedure call from the database.

spGetMyObject was auto generated via the designer as follows:

[Function(Name="dbo.spGetMyObject")]
  public ISingleResult<spGetMyObjectResult> spGetMyObject([Parameter(Name="ID", DbType="BigInt")] System.Nullable<long> iD)
  {
   IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), iD);
   return ((ISingleResult<spGetMyObjectResult>)(result.ReturnValue));
  }

ajj3085 replied on Friday, October 31, 2008

So, Linq might be constructing a type on the fly to deal with the result... that would likely take some resources. If you define an L2S class matching your table, and do the select from that, you might have better performance.

var rows =
   from rowSet in mgr.DataContext.MyObject // MyObject is TableSet<MyObject>
       where rowSet.ID == criteria.Value
      select rowSet;

TJones replied on Friday, October 31, 2008

After some research, it appears that the slow down is a result of the repeated calls to LoadProperty< , > in MyObject.GetMyObject(...) in the templates I'm using.