慢查询，想使用存储过程（ASP.Net MVC3，Generic Repo，POCO）

I have an MVC3 app which is using POCO classes separated into distinct layers. I have implemented a generic repo pattern as well.

我有一个MVC3应用程序，它使用分成不同层的POCO类。我也实现了一个通用的repo模式。

I have a dashboard which uses Kendo UI charts for management to view real time stats. As the database is growing the dashboard is slower and slower. I found these reasons:

我有一个仪表板，它使用Kendo UI图表进行管理，以查看实时统计数据。随着数据库的增长，仪表板越来越慢。我发现了这些原因：

1. Records grow daily
2. 记录每天都在增长
3. Each chart calls to the repo to get all the rows to calculate stats with LINQ
4. 每个图表调用repo获取所有行以使用LINQ计算统计数据
5. Automapper is creating my model to viewmodels
6. Automapper正在为viewmodels创建我的模型

I would like to speed this up by using stored procedures in the database to get exactly the numbers I need for stats. I am not sure how I should implement this... even though it feels so wrong! Any tips?

我想通过使用数据库中的存储过程来快速获取统计数据所需的数字来加快速度。我不确定我应该如何实现这个......即使它感觉错了！有小费吗？

Example Controller with Repo code

具有Repo代码的示例控制器

Public Class DashboardController
    Inherits BaseController

    Private ticketRepo As MaintenanceTicketsRepository

    Public Sub New()
        Me.ticketRepo = New MaintenanceTicketsRepository(New TicketContext)
    End Sub

    Function Chart_OpenItems() As ActionResult
        Dim tickets As IList(Of MaintenanceTicket) = ticketRepo.GetAll().Include(Function(p) p.Priority).Include(Function(s) s.Status).OrderBy(Function(o) o.PriorityId).ToArray()
         'Do some work with repo then dispose'
    End Function

    Function Chart_ClosedItems() As ActionResult
        Dim tickets As IList(Of MaintenanceTicket) = ticketRepo.GetAll().Include(Function(p) p.Priority).Include(Function(s) s.Status).OrderBy(Function(o) o.PriorityId).ToArray()
         'Do some work with repo then dispose'
    End Function
End Class

1 个解决方案

#1

Trying to answer your question. If you can reduce the amount of data going to your Model by using a stored procedure to aggregate the data before it leaves SQL Server then go for it. The actual implementation details will depend on what kind of ORM setup you have. If you can reduce the 1800 rows to >100 you will almost certainly see a speed increase.

试着回答你的问题。如果您可以通过使用存储过程在数据离开SQL Server之前聚合数据来减少转移到模型的数据量，那么就去实现它。实际的实现细节将取决于您拥有的ORM设置类型。如果你可以将1800行减少到> 100，你几乎肯定会看到速度增加。