Linq to SQL: select optimization

Question

On large tables in MSSQL; selecting specific columns results in greater speed of the query. Does the same apply to Linq to SQL?

Would this:

var person = from p in [DataContextObject].Persons
             where p.PersonsID == 1
             select new { p.PersonsID, p.PersonsAdress, p.PersonsZipcode };

be faster than this:

var person = from p in [DataContextObject].Persons
             where p.PersonsID == 1
             select p;

... ?

Answer 1

If you are limiting the size of the result set by only selecting a few specific columns, then YES it will have an affect.

EDIT ading clarification from comment

How is this better, it will reduce the size of the resultant data returned from SQL AND it will reduce the size of the objects used to store the results in memory.

This is due to the fact that in the end LINQ to SQL generates SQL, so the same performance benefits exist.

Answer 2

I think the same applies, because LINQ to SQL translates the Linq query operations to SQL commands.

Answer 3

I highly recommend LinqPad. It is free and lets you run LINQ queries dynamically. When you can also look at the SQL that is generated.

What you will see is that the LINQ query will translate the first query into selecting only those columns. So it is faster.

Answer 4

Additionally to what the others have said, the new unnamed structure will be a much lighter-weight object than the Person object -- it would be much faster, even if you selected all the columns. (Person has method/fields etc to support writing the object back to the database. The unnamed type does not.)

Answer 5

There are 3 aspects with "faster" here.

1. less data transmitted means faster. On the other hand it will not get that significantly faster, unless you select more than one row or if your Person contains some other "heavy" columns - long varchars, image etc.

2. as J. Curran pointed out, less memory allocated means faster. Same remark as in 1. applies here.

3. Your query executes faster if you have an index containing all selected columns (or attached to it starting from SQL Server 2005). In this case the SQL Server engine does not need to load the page with the row in memory - if it's not there yet.

Personally I wouldn't bother trying to optimize my queries this way (unless, as I said your rows contain binary data or very long strings that you don't need), partially because if you decide later that you'd like to have more information about this selected Person, you would need to change your DB access code vs. just accessing a property in your POCO/anonymous class.

Answer 6

If you have columns that are very large such as binaries and images then it can make a significant difference which is why LINQ to SQL allows you to specify delay loading for certain columns so that you can still select entire objects without performing 'select new' projections.