August 19, 2011 at 9:28 am
Our .Net consultant is rewriting a legacy ASP 2.0 application to .Net ASP. Everything running under IIS 7.5 (W2K8 R2 Server) and SQL Server 2008 R2. Sweet setup, everything has been running well except....
Side-by-side the new .NET application is 5x-10x SLOWER compared to the older ASP 2.0 app. Overloading our SQL server's CPU, task manager CPU 25%-50%-+, and as soon as we move all users back to ASP 2.0 website CPU drops back to 0%-5%.
.NET App apparently uses something called Entity Framework EF. I'm not a .Net programmer, so I'm not familiar with EF. I was involved in the asp programming.
SQL 2008 R2 Profiler shows what I think are big hints to the problem. We did some simply quick traces:
The GOOD Asp 2.0 trace is fairly short. The EventClass column is mostly a bunch of SQL:BatchStarting and SQL:BatchCompleted. Text data column always nice clean SQL syntax.
The BAD .Net trace is quite longer during the exact same job. Here's the EventClass column and textdata columns. These four rows repeated many times, SQL may change.
EventClass Text Data
-------------- ------------
Audit Login -- network protocol: TCP/IP set qu....
RPC:Completed exec sp_executesql N'SELECT ...
Audit Logout <blank>
RPC:Completed exec sp_reset_connection
Regarding all of those rows of SQL statements... why are they all proceeded by exec sp_executesql ?
And the .Net SQL syntax looks really over the top complex... here's a simple example of a SQL query:
Good Asp SQL code syntax:
SELECT description, room FROM t_rooms WHERE result = 'Kitchen'
Bad .NET SQL code syntax:
exec sp_executesql N'SELECT
[Limit1].[C1] AS [C1],
[Limit1].[room] AS [room],
[Limit1].[description] AS [description]
FROM ( SELECT TOP (1)
[Extent1].[description] AS [description],
[Extent1].[room] AS [room],
1 AS [C1]
FROM [dbo].[t_rooms] AS [Extent1]
WHERE [Extent1].[result] = @p__linq__67553
) AS [Limit1]',N'@p__linq__67553 nvarchar(10)',@p__linq__67553=N'Kitchen'
Wow, what's with that .Net Syntax? Limit1's? Extent1? I'm getting a headache just looking at it. Why all extra stuff? SQL code looks very inefficient to me. IS EF a MS disaster to be avoided by real programmers?
Our .Net programmer is trying to tell us EF is a good thing. I don't think I agree especially if it's pushing such crap to the SQL box as profiler seems to show...
I just don't know how to direct the .Net programmer to fix it.
Help !
August 19, 2011 at 9:37 am
When a programmer tells you somthing is good always dig deeper to see WHY.
If it's faster for him to program in chances are good that something is generating code with results similar to what you are seeing.
The unfortunate short answer to your problem is find a dev that codes for performance. Not as in codes fast for sake of coding quickly.
Good code takes time. Great code takes, a heck of a lot of things to go right.
The only app I ever developped in asp.net was 100% hand-coded queries and it flew from wall to wall. Nothing was slow in that thing. But then again I know what I doing sql side (doesn't make him a bad person, just the wrong fit for what you need).
August 19, 2011 at 12:23 pm
I write asp.net all day everyday and it is super fast. Once I have stuff ready to deploy I have not been able to get much noticeable difference in page loads between classic asp and asp.net. I recently completely rewrote a very large ecomm site that we had in classic asp. In some instances, i actually saw performance gains. I not all that familair with the entity Framework myself but like Remi said anything that automatically generates sql is going to be slower than hand crafted, thought out sql. Main point I am making is that the culprit is NOT .net. I found a Microsoft blog post about the Entity Framework and some performance comparisons. Granted it is a bit old but given the stark performance differences I suspect it is the Entity Framework that is the cause of your slow performance. All too often developers tend to find something that is pretty cool and works great for one situation so they continue to use that same methodology across the board. Unfortunately this can lead to some serious performance problems. This is true of sql people as well (can you say cursors?).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply