由于计算，SQL Server 查询速度变慢

Question

我负责一个旧的记录系统，该系统是用 ASP.net Web Forms编写的，使用 ADO.Net 2.0进行持久性。

基本上，该系统允许用户添加有关他们正在做的工作的详细信息，他们被分配完成工作的小时数以及他们迄今为止在工作上花费的小时数。

该系统还具有一个报告功能，其中包含基于 SQL 查询的报告。最近，我注意到从系统运行的许多报告执行速度非常慢。该数据库大约有 11 个表，并且不会存储太多数据。27,000 条记录是任何一个表保存的最多记录，大多数表甚至远低于 1,500 条记录。

因此，我认为

这个问题与大量数据无关，我认为它更多地与构造不佳的sql查询有关，甚至可能同样适用于数据库设计。

例如，有与此类似的查询

@start_date datetime, 
@end_date datetime,
@org_id int 
select distinct t1.timesheet_id, 
                t1.proposal_job_ref, 
                t1.work_date AS [Work Date], 
                consultant.consultant_fname + ' ' + consultant.consultant_lname AS [Person], 
                proposal.proposal_title AS [Work Title], 
                t1.timesheet_time AS [Hours],
--GET TOTAL DAYS ASSIGNED TO PROPOSAL
(select sum(proposal_time_assigned.days_assigned)-- * 8.0) 
from proposal_time_assigned
where proposal_time_assigned.proposal_ref_code = t1.proposal_job_ref ) 
as [Total Days Assigned],

--GET TOTAL DAYS SPENT ON THE PROPOSAL SINCE 1ST APRIL 2013 
(select isnull(sum(t2.timesheet_time / 8.0), '0') 
from timesheet_entries t2 
where t2.proposal_job_ref = t1.proposal_job_ref 
and t2.work_date <= t1.work_date 
and t2.work_date >= '01/04/2013' ) 
as [Days Spent Since 1st April 2013],
--GET TOTAL DAYS REMAINING ON THE PROPOSAL 
(select sum(proposal_time_assigned.days_assigned) 
from proposal_time_assigned 
where proposal_time_assigned.proposal_ref_code = t1.proposal_job_ref )
- 
(select sum(t2.timesheet_time / 8.0) 
from timesheet_entries t2 
where t2.proposal_job_ref = t1.proposal_job_ref 
and t2.work_date <= t1.work_date
 ) as [Total Days Remaining]
from timesheet_entries t1, 
consultant, 
proposal, 
proposal_time_assigned 
where (proposal_time_assigned.consultant_id = consultant.consultant_id)
     and (t1.proposal_job_ref = proposal.proposal_ref_code) 
     and (proposal_time_assigned.proposal_ref_code = t1.proposal_job_ref)
     and (t1.code_id = @org_id) and (t1.work_date >= @start_date) and (t1.work_date <= @end_date) 
    and (t1.proposal_job_ref <> '0')
order by 2, 3

预期将返回报告的数据。我什至不确定是否有人可以遵循上面查询中发生的事情，但基本上发生了相当多的计算，即除法、乘法、减法。我猜这就是减慢 sql 查询速度的原因。

我想我的问题是，任何人都可以对上面的查询有足够的理解，甚至建议如何加快它。

另外，像上面提到的计算应该在SQL查询中执行吗？还是应该在代码中完成此操作？

任何帮助将不胜感激。

谢谢。

Answer 1

根据给出的信息，我不得不对某些表关系进行有根据的猜测。 如果您发布表结构、索引等... 我们可以完成此查询中的其余列。

截至目前，此查询计算"分配的天数"、"花费的天数"和"剩余天数"对于钥匙"timesheet_id和proposal_job_ref"

我们要看到的是"work_date"、"timesheet_time"、"[人]"、"proposal_title"如何与此相关联。这些也是按人和Proposal_title计算的吗？

您可以使用 SQLFIDDLE 为我们提供示例数据和输出，以便我们可以处理完整的数据，而不是进行猜测。

    SELECT 
             q1.timesheet_id
            ,q1.proposal_job_ref
            ,q1.[Total Days Assigned]
            ,q2.[Days Spent Since 1st April 2013]
            ,(
                q1.[Total Days Assigned] 
                - 
                q2.[Days Spent Since 1st April 2013]
            ) AS [Total Days Remaining]
    FROM
    (
        select 
            t1.timesheet_id
            ,t1.proposal_job_ref
            ,sum(t4.days_assigned) as [Total Days Assigned]
        from tbl1.timesheet_entries t1
        JOIN tbl1.proposal t2
            ON t1.proposal_job_ref=t2.proposal_ref_code
        JOIN tbl1.proposal_time_assigned t4
            ON t4.proposal_ref_code = t1.proposal_job_ref
        JOIN tbl1.consultant t3
            ON t3.consultant_id=t4.consultant_id
        WHERE t1.code_id = @org_id
            AND t1.work_date BETWEEN @start_date AND @end_date
            AND t1.proposal_job_ref <> '0'
        GROUP BY t1.timesheet_id,t1.proposal_job_ref
    )q1
    JOIN
    (
        select 
            tbl1.timesheet_id,tbl1.proposal_job_ref
            ,isnull(sum(tbl1.timesheet_time / 8.0), '0') AS [Days Spent Since 1st April 2013]
        from tbl1.timesheet_entries tbl1
        JOIN tbl1.timesheet_entries tbl2
            ON tbl1.proposal_job_ref=tbl2.proposal_job_ref
            AND tbl2.work_date <= tbl1.work_date
            AND tbl2.work_date >= '01/04/2013'
        WHERE tbl1.code_id = @org_id
            AND tbl1.work_date BETWEEN @start_date AND @end_date
            AND tbl1.proposal_job_ref <> '0'
        GROUP BY tbl1.timesheet_id,tbl1.proposal_job_ref
    )q2
        ON q1.timesheet_id=q2.timesheet_id 
        AND q1.proposal_job_ref=q2.proposal_job_ref

Answer 2

我在您的查询中看到的问题是：

1> 未为表提供别名。2> 使用子查询（执行成本很高）而不是 WITH 子句。

如果我写你的查询，它将看起来像这样：

select distinct t1.timesheet_id, 
                t1.proposal_job_ref, 
                t1.work_date AS [Work Date], 
                c1.consultant_fname + ' ' + c1.consultant_lname AS [Person], 
                p1.proposal_title AS [Work Title], 
                t1.timesheet_time AS [Hours],
--GET TOTAL DAYS ASSIGNED TO PROPOSAL
(select sum(pta2.days_assigned)-- * 8.0) 
from proposal_time_assigned pta2
where pta2.proposal_ref_code = t1.proposal_job_ref ) 
as [Total Days Assigned],

--GET TOTAL DAYS SPENT ON THE PROPOSAL SINCE 1ST APRIL 2013 
(select isnull(sum(t2.timesheet_time / 8.0), 0) 
from timesheet_entries t2 
where t2.proposal_job_ref = t1.proposal_job_ref 
and t2.work_date <= t1.work_date 
and t2.work_date >= '01/04/2013' ) 
as [Days Spent Since 1st April 2013],
--GET TOTAL DAYS REMAINING ON THE PROPOSAL 
(select sum(pta2.days_assigned) 
from proposal_time_assigned pta2
where pta2.proposal_ref_code = t1.proposal_job_ref )
- 
(select sum(t2.timesheet_time / 8.0) 
from timesheet_entries t2 
where t2.proposal_job_ref = t1.proposal_job_ref 
and t2.work_date <= t1.work_date
 ) as [Total Days Remaining]
from timesheet_entries t1, 
consultant c1, 
proposal p1, 
proposal_time_assigned pta1
where (pta1.consultant_id = c1.consultant_id)
     and (t1.proposal_job_ref = p1.proposal_ref_code) 
     and (pta1.proposal_ref_code = t1.proposal_job_ref)
     and (t1.code_id = @org_id) and (t1.work_date >= @start_date) and (t1.work_date <= @end_date) 
    and (t1.proposal_job_ref <> '0')
order by 2, 3

检查上面的查询，了解任何索引选项和要从每个表中处理的记录数。

Answer 3

检查数据库中的索引（如果这些列未编制索引，则首先为每个列编制索引）。

• proposal_time_assigned.提案_参考_代码
• proposal_time_assigned.顾问编号
• timesheet_entries.code_id
• timesheet_entries.提案_工作_参考
• timesheet_entries.工作日期
• consultant.consultant_id
• proposal.proposal_ref_code

如果没有所有这些索引，则无法改善此查询。

查询中唯一会影响性能的是筛选 [work_date] 的方式。 当前语法会导致表扫描：

--bad
and t2.work_date <= t1.work_date 
and t2.work_date >= '01/04/2013'

此语法使用索引（如果存在），并且速度要快得多：

--better
and t2.work_date between t1.work_date and '01/04/2013'