LINQ to Entities query takes a long time to compile, SQL is fast

Question

I am working on a piece of code written by a colleague who interacts with a CRM application that our company uses. In this piece of code, there are two LINQ to Entities queries that are executed many times in our application, and I was asked to optimize them because one of them is very slow.

These are the requests:

The first request, this one compiles almost instantly. It receives communication information from the CRM database, filtering the list of relationship identifiers specified by the application:

from relation in context.ADRELATION where ((relationIds.Contains(relation.FIDADRELATION)) && (relation.FLDELETED != -1)) join addressTable in context.ADDRESS on relation.FIDADDRESS equals addressTable.FIDADDRESS into temporaryAddressTable from address in temporaryAddressTable.DefaultIfEmpty() join mailAddressTable in context.ADDRESS on relation.FIDMAILADDRESS equals mailAddressTable.FIDADDRESS into temporaryMailAddressTable from mailAddress in temporaryMailAddressTable.DefaultIfEmpty() select new { Relation = relation, Address = address, MailAddress = mailAddress }; 

The second request, which takes 4 to 5 seconds to compile , and takes information about people from the database (again filtered by a list of identifiers):

 from role in context.ROLE join relationTable in context.ADRELATION on role.FIDADRELATION equals relationTable.FIDADRELATION into temporaryRelationTable from relation in temporaryRelationTable.DefaultIfEmpty() join personTable in context.PERSON on role.FIDPERS equals personTable.FIDPERS into temporaryPersonTable from person in temporaryPersonTable.DefaultIfEmpty() join nationalityTable in context.TBNATION on person.FIDTBNATION equals nationalityTable.FIDTBNATION into temporaryNationalities from nationality in temporaryNationalities.DefaultIfEmpty() join titelTable in context.TBTITLE on person.FIDTBTITLE equals titelTable.FIDTBTITLE into temporaryTitles from title in temporaryTitles.DefaultIfEmpty() join suffixTable in context.TBSUFFIX on person.FIDTBSUFFIX equals suffixTable.FIDTBSUFFIX into temporarySuffixes from suffix in temporarySuffixes.DefaultIfEmpty() where ((rolIds.Contains(role.FIDROLE)) && (relation.FLDELETED != -1)) select new { Role = role, Person = person, relation = relation, Nationality = nationality, Title = title.FTXTBTITLE, Suffix = suffix.FTXTBSUFFIX }; 

I configured SQL Profiler and took SQL from both queries and then ran it in SQL Server Management Studio. Both requests were executed very quickly, even with a large (~ 1000) number of identifiers. Therefore, the problem is compiling the LINQ query.

I tried using a compiled query, but since they can only contain primitive parameters, I had to cut out the part with the filter and apply it after invoking Invoke (), so I'm not sure if that helps a lot. Also, since this code works in the WCF service, I'm not sure if the compiled request will still exist on subsequent calls.

Finally, I tried to select only one column in the second query. Although this obviously will not give me the information I need, I thought it would be faster than the 200 columns that we are currently selecting. There is no such case, it lasted 4-5 seconds.

I am not a LINQ guru at all, so I can hardly follow this code (I have the feeling that it is not written optimally, but cannot impose on it). Can someone give me a clue as to why this problem may occur?

The only thing left for me is to manually select all the information, and not to combine all these tables. Then I get about 5-6 requests. Not so bad, I think, but since I am not dealing with a terribly inefficient SQL here (or at least an acceptable level of inefficiency), I was hoping to prevent this.

Thank you in advance, I hope I made it clear. If not, feel free to ask and I will provide further information.

---

Edit: I ended up adding associations to my entity infrastructure (no foreign keys were specified in the target database) and rewriting the request:

 context.ROLE.Where(role => rolIds.Contains(role.FIDROLE) && role.Relation.FLDELETED != -1) .Select(role => new { ContactId = role.FIDROLE, Person = role.Person, Nationality = role.Person.Nationality.FTXTBNATION, Title = role.Person.Title.FTXTBTITLE, Suffix = role.Person.Suffix.FTXTBSUFFIX }); 

It seems a lot more readable, and it's faster.

Thanks for the suggestions, I will definitely stay one of them about creating several compiled queries for a different number of arguments!

Answer 1

Gabriel's answer is correct: use a compiled query.

It looks like you are compiling it again for each WCF request, which, of course, defeats the goal of a one-time initialization. Instead, put the compiled query in a static field.

Edit:

Do this: send the maximum load to your service and postpone the debugger 10 times. Look at the call stack. Did he stop more often in L2S code or in ADO.NET code? This will show you if there is a problem with L2S or with SQL Server.

Then fix the filter. We need to return it back to the compiled request. This can only be done by changing this:

 rolIds.Contains(role.FIDROLE) 

:

 role.FIDROLE == rolIds_0 || role.FIDROLE == rolIds_1 || ... 

You need a new compiled query for each power rolIds. This is unpleasant, but you need to collect it. In my project, I automated this task, but you can make a one-time solution here.

I assume that in most queries there will be very little role identifier, so you can materialize 10 compiled queries for capacities 1-10, and if the capacity exceeds 10, you return to client-side filtering.

Answer 2

If you decide to save the request inside the code, you can compile it. You should still compile the request once at application startup, but all subsequent calls will use this already compiled request. You can take a look at the MSDN help here: http://msdn.microsoft.com/en-us/library/bb399335.aspx .

Another option is to use a stored procedure and call the procedure from your code. Therefore, there is no compilation time.