CODEWITHSUNDEEP
linqsql-server-2008caching
Dave
Dave

Reputation: 1641

Trying not to be too clever, can I make an assumption about the SQL cache?

I have a SQL2008 table of automotive makes, models, and year. To display a list of years to the user I need to know the lowest and the highest years in the table, like 1919 - 2012.

I use a LINQ query as follows:

return (from car in context.Cars select car.Year).Min();

...which turns into this SQL query:

SELECT MIN([t0].[Year]) AS [value] FROM [dbo].[Cars] AS [t0]

I know (and you know) that the min will rarely if ever change and the max will change, at most, once per year. But since they are indeed data-dependant, they're not suitable as constants. Since they -could- change during runtime, I don't want to make them static.

I'm assuming that SQL will cache that min and max, and as long as the underlying tables are not modified, it's smart enough to return me cached information. Until and unless it ever became a performance bottleneck, it's more of a curiosity, but to what extent can I rely on SQL (in this case, SQL2008 R2) caching query results as long as the tables do not change?

Upvotes: 0

Views: 60

Answers (2)

Jordan
Jordan

Reputation: 2758

Well instead of even hitting the database why not cache it on the application level? You can use SqlCacheDependency SQL Cache Dependency Invalidate the cache if the table changes otherwise don't even hit it.

Upvotes: 1

Lucero
Lucero

Reputation: 60196

Caching the absolute min and max of a column wouldn't make sense, because they are dependent on any filtering done in the WHERE clause. However, you should be able to make this query fast by adding suitable indexes.

Upvotes: 0

Related Questions