Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

PimpThisBlog.com — Mon, 12 Oct 2009 01:05:07 GMT

Thank you for submitting this cool story - Trackback from PimpThisBlog.com

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

Chris — Wed, 19 Aug 2009 14:29:42 GMT

Hi,

I don't get at all how you are "passing" the delegate to the compiled query. The delegate is called GetUserFromUserName, right? There is no reference to this function in the Compiled_GetUserFromUserName. When I mimic this code I get an error stating "cannot convert method group SingleOrDefault to non-delegate type System.Linq.IQueryable". What am I missing?

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

Günther Foidl — Fri, 05 Jun 2009 23:41:22 GMT

Hi, very good explaination.

But I missed one thing. The comparison to stored procedures in SQL Server. They can be executed by LINQ.

Finally I would say this is just an opinion when it's not possible to add a stored procedure.

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

Magnus — Tue, 12 May 2009 17:18:28 GMT

Hi, I tried to use this but it failed.

Im using an object of type Expression<Func<table, bool>> in may where clause. like this:

internal static readonly

Func<ApciPlusSSCE, Expression<Func<MswToParameter,bool>>,IQueryable<Parameter>>

Compiled_GetParameters = CompiledQuery.Compile<ApciPlusSSCE, Expression<Func<MswToParameter, bool>>, IQueryable<Parameter>>(

(db,predicate) => db.MswToParameter.Where(predicate).Select(m => m.Parameter).Distinct());

When called I get an NotSupportedException: Unsupported overload used for query operator 'Where'.

The query works as a dynamic linq query.

What have I missed? Where shall I begin to search for the error?

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

Daniel — Tue, 14 Apr 2009 02:24:50 GMT

Hi Omar, thanks for that.

I guess because the Compiled Queries return an IQueryable I was expecting to be able to reuse it like you do a dynamic query:

IQueryable<MyEntity> query = ...(ExecuteCompiledQuery)

and then be able to go:

int totalCount = query.Count();

List<MyEntity> results = query.Skip(10).Take(10).ToList();

But having looked into it, I can see that the query is actually executed immediately on the SQL box when you call the Compiled Query.

So I guess I would need a seperate compiled query for the count and then another compiled query which has "skip" and "take" paramters to actually get the search result range I'm after... The problem with this is that I have to write and maintain duplicate code.

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

omar — Tue, 07 Apr 2009 08:48:25 GMT

Hi daniel,

"in fact if they are they become less efficient that dynamic queries. "

Compiled Queries are definitely more efficient than dynamic queries in terms of execution. They are compiled, faster and does not consume memory like dynamic queries does on every execution.

I believe you wanted to say compiled queries become less reusable than dynamic queries.

Compiled queries are like fixed queries. You have to build the whole query and just call it. I do not know a way to join a compiled query with another compiled query or dynamic query. In fact I am curious how you join a dynamic query with another one.

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

Daniel — Mon, 06 Apr 2009 20:59:45 GMT

Omar, great article. I had hoped this would solve some of the speed issues that we have been facing, however was disapointed to find that the compiled queries couldn't be reused further, in fact if they are they become less efficient that dynamic queries.

Eg: I tried to join another table onto a compiled query, but this blew out the query. However it looks like I can refine the query without losing performance so long as I'm not changing the structure by adding more tables etc.

Are there any way to join onto compiled queries without losing the performance gain?

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

Guy Noir — Sat, 14 Feb 2009 22:24:29 GMT

I too have been searching for a way to use the DataLoadOptions w/ compiled queries, so thanks for the post!

It's only natural that you'd want to use DataLoadOptions w/ a compiled query, but it took me way to long to find this answer.

Is there a way that doesn't require an additional line of code to use it each time you want to use the compiled query?

Couldn't the static DataLoadOptions somehow be part of the initialization of the compiled query?

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

skain — Mon, 03 Nov 2008 20:16:17 GMT

Thanks for the info on how to use CompiledQueries with LoadWith! I've been trying to figure that out for a while now and had just about concluded it couldn't be done.

Now I've just got to update my app to use static instances . . .

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

bigjim — Sat, 01 Nov 2008 21:26:16 GMT

Well, this is all academic. Apparently this week MS announced the death of Linq To SQL in favor of Entity Framework. Unfortunately, in my opinion.

Look to David Hayden (among others) for more on this news

codebetter.com/.../linq-to-sql-is-dead-read-between-the-lines.aspx

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

niran — Sat, 01 Nov 2008 15:05:23 GMT

Nice post.

I would welcome any performance improvement in linq to sql. However, there is some stories floating around saying linq to sql is dead.

BTW, is it possible to use pure code instead of posting the screen shots?

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

Lee — Fri, 31 Oct 2008 22:01:26 GMT

I was planning on changing my dal to use linq to sql to make my life creating queries in the bll abit easier and less painful but the performance hit has put me off completely. I appreciate the workaround but it's more work than going back to basics. I hope this will be resolved in .net 4.

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

Omari — Thu, 30 Oct 2008 07:59:39 GMT

You must make all DataLoadOptions static even if don't use compiled queries. It is a deep dive secret of L2S I've found hard way.

L2S cache last 10 entity materializers and will reuse them but only if DataContext has has the same (or empty) instance of DataLoadOptions assigned. otherwise it will compile it EACH time spending cpu and memory.

Look at System.Data.Linq.SqlClient.ObjectReaderCompiler.Compile(SqlExpression, Type) : IObjectReaderFactory

and System.Data.Linq.SqlClient.ObjectReaderCompiler.maxReaderCacheSize : Int32

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

Guy Harwood — Thu, 30 Oct 2008 07:47:49 GMT

So much code for such little reward.

It seems a lot easier in many respects to go back to basics, as ive had my fingers burnt a few times with things like this in live situations.

LINQ to SQL requires a huge learning investment just to get it performing at an acceptable performance level.

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

roncansan — Wed, 29 Oct 2008 14:52:18 GMT

Isn't easier and faster to create a store procedure and call the SP through LINQ?

re: Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

Muhammad Mosa — Tue, 28 Oct 2008 15:18:17 GMT

I've applied this on LINQ to Entites, but Omar I have a question! Shall I use this will frequently used queries?! what if I decided to be smart and complie each Query? what would be the concequences?