LINQ to SQL : Too much CPU Usage: What happens when there are multiple users_问答_开发者

I am using LINQ to SQL and seeing my CPU Usage sky rocketting. See below screenshot. I have three questions

What can I do to reduce this CPU Usage. I have done profiling and basically removed everything. Will making every LINQ to SQL statement into a compiled query help?
I also find that even with compiled queries simple statements like ByID() can take 3 milliseconds on a server with 3.2开发者_JAVA技巧5GB RAM 3.17GHz - this will just become slower on a less powerful computer. Or will the compiled query get faster the more it is used?
The CPU Usage (on the local server goes to 12-15%) for a single user will this multiply with the number of users accessing the server - when the application is put on a live server. i.e. 2 users at a time will mean 15*2 = 30% CPU Usage. If this is the case is my application limited to maximum 4-5 users at a time then. Or doesnt LINQ to SQL .net share some CPU usage. alt text http://www.freeimagehosting.net/uploads/5f10e1f694.png

Profile. Profile. Profile.

Profile to find out exactly which query is taking the most resources and improve the performance of that query. You can use the Log property of the DataContext to view the SQL - see this article. You can get the query plans for a query in SQL Server - see this article.

Examples of ways to improve a query:

Add missing indexes.
Rewrite the query to take advantage of the indexes that are already there.
Don't fetch too much data per query - use paging and only fetch more rows when requested. Don't fetch fields you don't need.
Don't fetch too little data per query - don't make a loop fetching one row at a time. Fetch many rows at once.

Once you have done that, profile again to check if you have improved the performance of that query. If not, repeat until you have.

Then profile again to see what the next killer query is and repeat the process until your performance is acceptable.

You say you already have profiled, but you haven't posted any profiling information such as queries, query plans, execution times, query frequency, etc. Without more profiling information all we can do is guess.

I have noticed similar behavior with some applications here; Using ANTS Profiler, we have narrowed down our CPU utilization to being in the Linq to SQL code. We've found a few major places that this becomes a problem:

Complex Linq to SQL queries. Using several joins and where clause constraints and such. The problem with these is that the Linq to SQL engine must convert the C# statement into a SQL statement. I believe this conversion by default can be re-used if the same code is called by the same DataContext, but if you instantiate a new DataContext for each execution, then the CPU intensive conversion code is executed every single time. This can be resolved either by replacing the complex Linq to SQL query with a stored procedure or by using a pre-compiled Linq to SQL query (Here's a blog post showing how to create a compiled query).
Reflection based property assignment. We found that simple queries like DataContext.Orders.FirstOrDefault(o => o.OrderID = orderID) can be very CPU intensive as they use reflection to set all of the properties to the returned values from the query. We have not investigated this beyond this point; We have simply replaced some of our Linq to SQL based code with standard C# Data Access (eg, SqlCommand, SqlClient) with hand written code to pull the appropriate column data into the appropriate properties, but only in places that ANTS was telling us were most important. Making this change generally dropped CPU utilization significantly (eg, dropping from 2000 ms of CPU utilization down to 39 ms).
A general Linq performance issue(not specific to Linq to SQL); While writing Linq queries does result in more intention revealing code than similar iterative approaches, we have found that sometimes the queries end up being very inefficient (usually by iterating over a collection multiple times or building a cartesian product where one really wasn't necessary). I don't have any examples of this off the top of my head, but ANTS was able to help locate these issues also (usually looking for extremely high hit counts for a particular line of code). These can usually be fixed with minor tweaks to the Linq query (or in some cases replacing the Linq query with a couple statements).

Perhaps important to note is that our use of Linq to SQL is based on the DBML files that the GUI creates, so attribute decorated classes with all the information about column mappings and such.

This is just what we have found regarding Linq to SQL; I would be interested to see what other's experience has been. I would highly recommend ANTS Performance Profiler for determining where CPU utilization is concentrated within your application; Note that if the high CPU utilization is SQL based (which it doesn't sound like it is in your case), then ANTS probably won't help (nor will the rest of my answer :) ).

Compiled queries will not "get faster" with more use. The primary benefit of compiled queries is to save the need for the LINQ engine to repeatedly perform the translation process every time it is called.

As far as the CPU usage goes, if this is your development machine, the odds are very good that something else is going on to cause such high activity. Even if this is a dedicated database server, I would highly suggest using SQL Profiler to investigate what statements are being generated by your LINQ queries. It may require tweaking your schema, your code or your database settings to get the usage back to a more acceptable level.

We recently deployed a large eCommerce application using linq to sql and experienced CPU usage very similar to what you describe.

After several weeks of profiling and debug traces, it turned out the villain was the jit-compiling of the queries at runtime.

We went through our data layer and converted all queries to compiled queries and our CPU use instantly went from a constant 100% to averaging 5% to 20% with some spikes up to 80% when the queries were first being compiled.

Are there any common queries that can be cached?
Can the linq queries be rewritten
Is most of the query processing being done on the web server or in SQL?
Are you running both SQL sever and web server on the same box? Do you have a test environment that mimics the production environment?