Performance Tip: Return Only Necessary Columns Using LINQ

I was running into an issue where one of my webmethods was taking a large amount of time to return a small set (5-10 objects). I was using LINQ to SQL. I noticed that the LINQ to SQL query was returning all of the rows. After looking into the table a bit further, I noticed that the table included some columns with a larger type (old text column, image column, etc). So, I decided to modify my select to contain just the columns I needed. It improved my response time from roughly 8 seconds to 250 milliseconds. Here's a sample select statement:

From t In db.News _
Where t.NewsID = NewsID _
Select New With {.Title = t.Title, .Abstract = t.Abstract, .DatePublished = t.DatePublished)

Instead of enumerating these items as type NewsPosting, I had to build a list of type Object or create a new custom type. For my purpose, a Generic.List(Of Object) worked just fine.

2 Comments

  • I'm told just creating an anonymous struct for the fields, instead of return the native LINQ object (even if it includes all of the columns) will speed things up, as the native object is dragging around a lot of extra weight (mainly there in case you update the object)

  • True that works. Also if you're using a 3-tier approach, then returning anonymous types in your CRUD methods
    is a lil different. Some I'd have to create an altered type to hold the small column set.
    So For instance if I have a query that returns IEnumerable, I'd create a StudentAlt
    class and return IEnumerable



    public class FSEHS_STUDENT_MIN {
    public string NSUID { get; set; }
    public string FIRST_NAME { get; set; }
    public string LAST_NAME { get; set; }
    public override string ToString() {
    return "NSUID: " + NSUID + "\nFirst Name :" + FIRST_NAME;
    }
    }

    partial class FSEHS_STUDENT {
    public static IEnumerable select() {
    SchoolDataContext db = new DataContext();
    return from m in db.FSEHS_STUDENTs.Take(200) select new FSEHS_STUDENT_MIN{ FIRST_NAME=m.FIRST_NAME};
    }

    public static IEnumerable select() {
    SchoolDataContext db = new SchoolDataContext();

    return from m in db.FSEHS_STUDENTs.Take(200)
    select m;
    }
    }

Comments have been disabled for this content.