如何提高SQL Server查询的性能

I have the following query in Stored Procedure:

我在存储过程中有以下查询:

UPDATE  dbo.CRM_tblActivityStore
SET StoDateChngFlg = CASE 
             WHEN x.STAGE_STAD <> x.ACTSTR_STAD THEN 1 ELSE 0 END
FROM    (SELECT CASE 
WHEN sd.stad = '00000000' THEN '12-31-2049' ELSE CONVERT (DATE, sd.stad) 
END AS STAGE_STAD,
                CONVERT (DATE, tas.StoDateSTA) AS ACTSTR_STAD,
                tas.StoActivityNbr,
                sd.Proj_Code,
                sd.OrderNbr,
                sd.FileNbr
         FROM   [SCM Server].[OrdrMgmt].dbo.STAGE_StageData AS sd
                INNER JOIN
                dbo.CRM_tblActivityStore AS tas
                ON sd.Proj_Code = tas.StoProjCode
                   AND sd.OrderNbr = tas.StoOrderNbr
                   AND sd.FileNbr = tas.StoFile) AS x
        INNER JOIN
        dbo.CRM_tblActivityStore AS tast
        ON tast.StoActivityNbr = x.StoActivityNbr
           AND tast.StoProjCode = x.Proj_Code
           AND tast.StoOrderNbr = x.OrderNbr
           AND tast.StoFile = x.FileNbr;

Some more information that might help:

一些可能有用的信息:

[SCM Server].[OrdrMgmt].dbo.STAGE_StageData - has more than 2,000,000 rows dbo.CRM_tblActivityStore - has more than 5,000,000 rows

[SCM服务器]。[OrdrMgmt] .dbo.STAGE_StageData - 有超过2,000,000行dbo.CRM_tblActivityStore - 有超过5,000,000行

None of these tables is indexed and no Primery key.

这些表中没有一个是索引的,也没有Primery键。

This one take more than 3 hours.

这个需要3个多小时。

How drasticley can it be improve to add index?

增加指数可以改善多大程度?

Any other ideas how to improve it?

还有其他想法如何改进吗?

Thanks,

Ilan

3 个解决方案

#1

As David says, adding indexes on the join columns will improve the response time for this query. However, that doesn't address the underlying issue of why these tables don't have primary keys. A primary key uniquely identifies each row in a table. Are you /really/ sure your data model allows duplicate rows? If so, are you sure that fits your business needs? Is there another, perhaps better, way of approaching the problem you're trying to solve.

正如David所说,在连接列上添加索引将改善此查询的响应时间。但是,这并没有解决为什么这些表没有主键的根本问题。主键唯一标识表中的每一行。您/确实/确定您的数据模型允许重复行吗?如果是这样,您确定符合您的业务需求吗?是否有另一种,或许更好的方式来解决你试图解决的问题。

Rules to consider: 1 - each row uniquely identifiable (primary key) 2 - each column in the row is related to the primary key 3 - each column is only to the primary key

要考虑的规则:1 - 每行唯一可识别(主键)2 - 行中的每列与主键3相关 - 每列仅与主键相关

You might need to break these tables into multiple tables. Consider carefully what each table is trying to describe and the relationship to the other tables. Do not try to make any one table do more than is allowed by its primary key.

您可能需要将这些表分成多个表。仔细考虑每个表尝试描述的内容以及与其他表的关系。不要试图让任何一个表做超过其主键允许的表。

#2

When optimizing queries I always start by using the

在优化查询时,我总是先使用

Include Actual Execution Plan

包括实际执行计划

option in SSMS, under the Query menu option.

SSMS中的选项,在“查询”菜单选项下。

You will then get a trove a info as to how to optimize your query. It will even give you the actual index creation script it thinks you need to add.

然后,您将获得有关如何优化查询的信息。它甚至会为您提供它认为您需要添加的实际索引创建脚本。

Good luck with this. I'm sure you can bring down this query to a few minutes.

祝你好运。我相信你可以把这个问题搞砸几分钟。

#3

Your database will be much improved by adding some indexes. In the query you provided, you have some joins which would benefit from the associated columns being indexed. I.e. sd.Proj_Code = tas.StoProjCode and sd.OrderNbr = tas.StoOrderNbr and sd.FileNbr =tas.StoFile

通过添加一些索引可以大大改善您的数据库。在您提供的查询中,您有一些联接可以从索引的关联列中受益。即sd.Proj_Code = tas.StoProjCode和sd.OrderNbr = tas.StoOrderNbr和sd.FileNbr = tas.StoFile

So long as the fields you are trying to match are the same data type, indexing will drastically improve your query times.

只要您尝试匹配的字段是相同的数据类型,索引将大大缩短您的查询时间。

Try indexing some columns then check what the planner is trying to do to see if you should change what is indexed - single or multiple column indexes, primary keys, etc

尝试索引一些列,然后检查规划器尝试做什么,看看是否应该更改索引的内容 - 单个或多个列索引,主键等

#1