Geek Answers Handbook

Linq to SQL: select optimization

On large tables in MSSQL; Selecting specific columns results in faster query speeds. Does the same apply to Linq to SQL?

Will it be:

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

will be faster than this:

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

...?

+6
performance sql linq-to-sql
source share
6 answers

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

What you will see is that the LINQ query will translate the first query to select only these columns. So it's faster.

+6
source share

If you limit the size of the result set by selecting only a few specific columns, then YES will have an effect.

PICTURE explanation with comments

Best of all, this will reduce the size of the resulting data returned by SQL and reduce the size of the objects used to store the results in memory.

This is because SQL is generated at the end of LINQ to SQL, so there are the same performance benefits.

+4
source share

There are 3 aspects with faster.

  • less transmitted data Faster. On the other hand, it will not get it much faster if you do not select more than one row or if your Face contains some other "heavy" columns - long varchars, image, etc.

  • as noted by J. Carran, less dedicated memory is faster. The same remark applies here as in 1.

  • Your query is faster if you have an index containing all the selected columns (or attached to it, starting with SQL Server 2005). In this case, the SQL Server engine does not need to load the page using a line in memory - if it is not already specified.

Personally, I would not try to optimize my queries this way (if, as I said, your lines contain binary data or very long lines that you do not need), partly because if you decide later what you want for more information about this selected character, you will need to change the database access code, as well as just get access to the property in your POCO / anonymous class.

+3
source share

I think the same thing applies since LINQ to SQL translates Linq query operations to SQL commands.

+1
source share

In addition to what others have said, the new unnamed structure will be much lighter than the Person object - it will be much faster even if you select all the columns. (A person has a method / fields, etc., to support writing an object back to the database. An unnecessary type does not.)

+1
source share

If you have very large columns, such as binaries and images, this can make a big difference, so LINQ to SQL allows you to specify a load delay for certain columns so that you can select entire objects without making “select new” predictions.

+1
source share

More articles:


All Articles