LINQ-to-SQL CompiledQuery.Compile() with Update, Delete, Insert?

7

All,

So I've got all my select queries in LINQ-to-SQL converted to using CompiledQueries to speed things up. Works great so far for select statements, but I haven't been able to figure out how to pre-compile insert, update, or delete statements.

Granted, when you're inserting, deleting or updating in LINQ-to-SQL, you have to use the object model. But obviously somewhere along the way it generates a query, which would be nice to pre-compile and store in a static member.

Is this possible? What is LINQ performance like for updates, deletes and inserts when its not pre-compiled? I could see it being a lot faster than the selects, because what they do underneath is a lot simpler and less "dynamic"...

Sam Schutte

출처 linq-to-sql precompiled

3 답변

8

There is a big difference. Linq-To-SQL select queries can be large complex expressions trees. Its these that may take sometime 'compiling'. In this case coalescing to some T-SQL that can be run against a SQL Server. So it makes sense to cache the result of an operation so that it can be re-used.

However other Delete, Update, and Insert are simple operations that do not require an expression tree to be converted to T-SQL (LINQ itself is all about querying). Its just unfortunate that we've been trained to think of SQL code that performs these other operations as 'queries', we're not actuall asking for any info.

These operations are only defined by the DataContext not by LINQ hence the code to perform these functions is already compiled.

  • Cool. Makes sense. I guess I was forgetting that in LINQ-to-SQL the Update and Delete queries never are going to have complex WHERE clauses. They always just Update/Delete based on an ID. And Inserts in LINQ-to-SQL probably never have any WHERE clause at all... - Sam Schutte
3

I think of the three only insert would make sense to be able to compile and re-use because delete is trivially simple (DELETE FROM Table WHERE Key...) and UPDATE only updates the fields that have changed and so varies per update operation.

[)amien

  • Interesting - I think part of my thinking was that Update and Deletes could be more complex. In standard SQL, they certainly could be - you could have some giant complex WHERE statement on the end of an Update or Delete. But in LINQ-to-SQL you would only ever use the primary key or updated state. - Sam Schutte
0

L2S uses "sp_executeSQL" so after you run it the first time it will be in the stored procedure execution plan cache. Subsequent runs (of the same query - not same params) will reuse the compiled plan from the cache. So what you are asking for is automagically handled by SQL Server 'behind the scenes'.

  • True - in terms of the execution plan. The "precompiling" I'm talking about is the part where the LINQ is parsed from its expression tree into SQL. See the CompiledQuery.Compile() method. - Sam Schutte

Linked

Related

Latest