iron_odin iron_odin - 4 months ago 10
SQL Question

Does Linq search the entire database when I run a select statement with a time restricting where clauses?

Say I'm running a linq query to select all customers in a table where some column

DateOfCreation
(
datetime
) is within the last 30 days, and there are years worth of customers in the table.

Does the query continue searching every single row after it's gone beyond 30 days into the past? For this example assume there is an auto-incrementing integer primary key - maybe
CustomerID
, and the rows have been inserted at time of creation, so they are also sorted by
DateOfCreation
.

Answer

If you have a linq query which will search for all data in the last 30 days that matches the test value, like this:

var result = dataContext.MyTable.Where(r => r.Value == testValue &&
                                       r => r.DateOfCreation > DateTime.Now.AddDays(-30));

This translates to the following SQL:

select * from MyTable
where Value = testValue
and DateOfCreation > DATEADD(day,-30,GetDate())

so the query will have to search every row of the table to find the right data, but such a query (if the table is indexed correctly with an index on DateOfCreation) will be relatively quick. If the column has an index it will mean that SQL will not have to scan the entire table to find the rows that satisfy the query.

If you are experiencing problems use something like LINQPad to convert your linq to SQL then you can run that directly on the database and use the SQL tools (Execution planes, SQL Profiler) to find where the bottlenecks are. The solution may be adding indexes etc. to the tables or ultimately rewrite the query.