CODEWITHSUNDEEP
.netsql-serverperformancelinq-to-sql
Jader Dias
Jader Dias

Reputation: 90495

Does SQL Server cache LINQ to SQL queries?

As far as I know, in MS SQL Server 2000+ stored procedures are compiled, and run faster than normal non-compiled queries. I wonder if MS SQL Server also compiles LINQ to SQL queries, and caches that compilation, for performance purposes.

Upvotes: 3

Views: 2015

Answers (5)

NicoJuicy
NicoJuicy

Reputation: 3528

CompiledQueries of LinQ get cached by SQL Server, that's why they are so much faster. Normal LinQ Queries don't get cached by the SQL Server.

For testing.

Create a normal LinQ Query Create the same compiled LinQ query.

After the compiled query ran several time, clean the SQL Cache and you will notice that it will have almost the same execution time as the normal LinQ Query the first time.

PS. You can clean the SQL Server cache with the query: DBCC DROPCLEANBUFFERS

So, to answer your question about LinQ queries and caching.

Normal LinQ queries don't get cached and should be used when you only have to run it once. Compiled LinQ queries DO get cached by the SQL Server and will have performance improvements if you run it more than once.

I just found out about it and i think it's handy to know.

Upvotes: 1

Hector Sosa Jr
Hector Sosa Jr

Reputation: 4250

MS SQL Server caches the query plans for anything TSQL that is run against it. Once the plan is cached, the query will run a bit faster. I know that there is an expiration time for this cache, but I haven't asked the dba's about it.

As far as I know, MS SQL Server 2000 was the last one that compiled the stored procs. That's not done anymore. That's why a lot of inline queries are just as fast (with SQL Server 2000+).

Upvotes: 1

Robert MacLean
Robert MacLean

Reputation: 39261

When it comes to the execution plan in SQL Server there is no major differences in speed with stored procedures vs normal non-compiled queries. See this for more information about other factors about speed.

LINQ-to-SQL generates normal paramatised queries (SQL Server knows nothing about LINQ, the app generates normal SQL) so the balance is the same. That is if you aren't using the LINQ-to-SQL functions which call Stored Procs (assuming you meant the query stuff).

You may also want to read this, which is a similar question, but focuses on the more important benefits of LINQ vs StoredProcs

Upvotes: 3

Gustavo
Gustavo

Reputation: 359

As far as I know, it behaves as normal queries to SQL Server. You can always call Stored Procedures with LINQ to SQL if benefits exist for your specific case.

Upvotes: 1

Alan Jackson
Alan Jackson

Reputation: 6511

SQL Server doesn't know about LINQ.

As far as I know, LINQ runs just as a library that uses the queries to generate SQL queries that are then sent to the server as SQL text commands.

Upvotes: 2

Related Questions