Performance issues on ASP.NET MVC 2 with SQL Server

Question

Hello,

EDIT: I think it's a problem on the subquery on the LINQ-generated query, it get all the records... But I don't know how could I fix it

I have made a simple ASP.NET MVC 2 application that does SELECT queries on a view, I get really poor performance, and while doing a simple benchmark with jMeter (10 conccurents connection) while disabling the cache (I don't want everything to rely on the non customizable/extreme OutputCache)

I see that the SQL Server get overloaded, consuming a LOT of CPU (up to 100%) and all its reserved memory space (512MB)

Here is the action code that cause the problems (manual transactions because it cause DeadLock with the other program that insert new data on the database) :

public ActionResult Index(int page = 0)
{
    IronViperEntities db = new IronViperEntities();
    db.Connection.Open();
    DbTransaction transaction = db.Connection.BeginTransaction(IsolationLevel.ReadUncommitted);
    var messages = (from globalView in db.GlobalViews orderby globalView.MessagePostDate descending select globalView).Skip(page*perPage).Take(perPage);
    transaction.Commit();
    db.Connection.Close();
    ViewData["page"] = page;
    ViewData["messages"] = messages;
    return View();
}

Here is the query executed on the database :

SELECT TOP (100) 
[Extent1].[MessageId] AS [MessageId], 
[Extent1].[MessageUuid] AS [MessageUuid], 
[Extent1].[MessageData] AS [MessageData], 
[Extent1].[MessagePostDate] AS [MessagePostDate], 
[Extent1].[ChannelName] AS [ChannelName], 
[Extent1].[UserName] AS [UserName], 
[Extent1].[UserUuid] AS [UserUuid], 
[Extent1].[ChannelUuid] AS [ChannelUuid]
FROM ( SELECT [Extent1].[MessageId] AS [MessageId], [Extent1].[MessageUuid] AS [MessageUuid], [Extent1].[MessageData] AS [MessageData], [Extent1].[MessagePostDate] AS [MessagePostDate], [Extent1].[ChannelName] AS [ChannelName], [Extent1].[UserName] AS [UserName], [Extent1].[UserUuid] AS [UserUuid], [Extent1].[ChannelUuid] AS [ChannelUuid], row_number() OVER (ORDER BY [Extent1].[MessagePostDate] DESC) AS [row_number]
    FROM (SELECT 
      [GlobalView].[MessageId] AS [MessageId], 
      [GlobalView].[MessageUuid] AS [MessageUuid], 
      [GlobalView].[MessageData] AS [MessageData], 
      [GlobalView].[MessagePostDate] AS [MessagePostDate], 
      [GlobalView].[ChannelName] AS [ChannelName], 
      [GlobalView].[UserName] AS [UserName], 
      [GlobalView].[UserUuid] AS [UserUuid], 
      [GlobalView].[ChannelUuid] AS [ChannelUuid]
      FROM [dbo].[GlobalView] AS [GlobalView]) AS [Extent1]
)  AS [Extent1]
WHERE [Extent1].[row_number] > 0
ORDER BY [Extent1].[MessagePostDate] DESC

View Code :

SELECT     dbo.Messages.Id AS MessageId, dbo.Messages.Uuid AS MessageUuid, dbo.Messages.Data AS MessageData, dbo.Messages.PostDate AS MessagePostDate, 
                      dbo.Channels.Name AS ChannelName, dbo.Users.Name AS UserName, dbo.Users.Uuid AS UserUuid, dbo.Channels.Uuid AS ChannelUuid
FROM         dbo.Messages INNER JOIN
                      dbo.Users ON dbo.Messages.UserId = dbo.Users.Id INNER JOIN
                      dbo.Channels ON dbo.Messages.ChannelId = dbo.Channels.Id

I don't think the server hardware is a problem, I can run equivalent Rails/Grails application without any performance issue. (Dual Core, 3Gb of RAM)

A select count(*) on GlobalView returns ~270.000 lines, indexes are daily rebuilt and a explain show it uses all the clustered indexes.

I get an HTTP average response time of 8000ms, the SQL Server Management Studio shows an average CPU time for this SQL query of 866ms and an average logical IO of 7,592.03.

Database file size if ~180MB

I am using Windows Server 2008 R2 Enterprise Edition, ASP.NET MVC 2 with IIS 7.5 and SQL Server 2008 R2 Express Edition with Advanced Services. They are the only things running on this server.

What can I do ?

Thank you

Answer 1

I guess you got the query from SQL Server Profiler. Save the result, and pass it into the Database Engine Tuning Advisor. That might help you create additional indexes and statistics.

Answer 2

Just out of curiosity: wouldn't appending a .ToList() to the end of the var messages = ... line help?

Answer 3

I've found the probleme,

I replaced "orderby globalView.MessagePostDate descending" by "orderby globalView.MessageId descending", because there isn't any index on MessagePostDate, and that is muuuuch better !

Thank you