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.

Published Tuesday, June 10, 2008 10:30 PM by Jason N. Gaylord
Filed under: , ,

Comments

# Performance Tip: Return Only Necessary Columns Using LINQ

Wednesday, June 11, 2008 3:21 AM by Performance Tip: Return Only Necessary Columns Using LINQ

Pingback from  Performance Tip: Return Only Necessary Columns Using LINQ

# re: Performance Tip: Return Only Necessary Columns Using LINQ

Wednesday, June 11, 2008 4:20 PM by ZamesCurran

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)

# re: Performance Tip: Return Only Necessary Columns Using LINQ

Friday, June 13, 2008 10:00 AM by Cecil

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<Student>, I'd create a StudentAlt

class and return IEnumerable<StudentAlt>                                                    

   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<STUDENTALT> 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<STUDENT> select() {

           SchoolDataContext db = new SchoolDataContext();

           return from m in db.FSEHS_STUDENTs.Take(200)            

                  select m;

       }                                                

}

Leave a Comment

(required) 
(required) 
(optional)
(required)