Linq to SQL Compiled Queries are thread-safe


JD Conley wrote an interesting article showing the benefits you can get by using compiled queries on his blog.  He wrote me today saying:

"I enjoyed your postings on linq to sql performance and compiled query optimization. I recently published a blog about a real world situation that benefited from a compiled query. However, it got me thinking. Are compiled queries thread safe? Can I really create a static instance of a query and use it from multiple threads simultaneously (like in an ASP.NET application)?

My gut tells me no, but all the examples I’ve seen assume that they are in fact thread safe. Any ideas?"

A: Absolutely you can use it like that.  That’s like the main scenario it was designed for 🙂

And by the way JD, nice job on your analysis — textbook approach: cast a broad net, find the key resource, drill down. Don’t make too many assumptions before you look at data and don’t get detailed data until you know what to look at. 

^5

Comments (6)

  1. omario says:

    Rico, could tell how much memory a compiled query take? Does it depend on only generated sql-string and parameters or the size of original  expression?

  2. ricom says:

    Experts like me always get these things wrong , you should measure 🙂

    But funny you should ask because a perf quiz is coming up soon 🙂

  3. Frank Hileman says:

    I did not like the ugliness of the solution. Is this a good argument in favor of old-fashioned stored procedures? Or it it more a design flaw to create so many queries?

  4. ricom says:

    Well you can always use stored procedures if you like but I think compiled select statements have their place as well.

    What was the ugly part?

  5. Tanveer Badar says:

    Quoting from another MSDN blog,

    ‘… there are several known and important correctness issues, and performance hasn’t been a priority thus far (it absolutely will be going forwards!)…’.

    Do they ever listen to you?

  6. Frank Hileman says:

    The ugly part is below. Stored procedures tend to be easier to understand for simple operations like this. I really hope LINQ does not lead to more server round trip problems, having seen what happens when people neglect the issue over and over again.

    private static readonly Func<    FeedsDataContext, string, FeedSourceType,     string, IQueryable<Feed>> _compiledGet =    CompiledQuery.Compile(        (FeedsDataContext dc, string owner,         FeedSourceType sourceType, string facebookId) =>            from f in dc.Feeds            where f.OwnerId == owner &&            f.FeedSourceType == (byte)sourceType &&            f.FacebookId == facebookId            select f);public static Feed GetOrCreateFeed(    FeedsDataContext dc,     string owner,     FeedSourceType sourceType,     string facebookId){    Feed existingItem = _compiledGet(        dc, owner, sourceType, facebookId)            .SingleOrDefault();    …    }