提高性能的方法 SP

Question

我有以下代码...

UPDATE  Sales
    SET MappingOrderID = CAST ([dbo].[fRemoveNonNumericCharacters] (ltrim(rtrim(ActualOrderNumber))) AS INT) 
    FROM    Sales
    where   isnumeric(ActualOrderNumber) = 1 
    and ActualOrderNumber not like '%.%' 
    and len(ActualOrderNumber) < 10
    and MappingOrderID is null
    and ActualOrderNumber is not null
    and ltrim(rtrim(ActualOrderNumber)) in  (select ltrim(rtrim(OrderID)) from  dbo.Orders)

参与的列如下：

来自销售： MappingOrderId INT ActualOrderNumber NVARCHAR (10)

来自订单： OrderId INT

这是旧代码，我是数据库管理员，不知道为什么他/她会使用那么多Trim...脚本需要花费9分钟才能运行，并且理想情况下需要经常运行...

我在执行计划中也看到了这个：

- CONVERT_IMPLICIT(varchar(12),[DataMart].[dbo].[Orders].[OrderID],0) - CONVERT_IMPLICIT(varchar(20),[Datamart].[dbo].[Sales].[ActualOrderNumber],0) - 表达式中的类型转换(CONVERT_IMPLICIT(varchar(20),[DataMart].[dbo].[Sales].[ActualOrderNumber],0))可能会影响查询计划选择中的"CardinalityEstimate"，表达式中的类型转换(CONVERT_IMPLICIT(varchar(12),[Datamart].[dbo].[Orders].[OrderID],0))可能会影响查询计划选择中的"CardinalityEstimate"

根据我的感觉...

它通过将实际订单号（varchar）转换为更新MappingOrderId（int）...只有当实际订单号存在于订单中时才会更新...

我试过一些修改；但是varchar实际订单号的值像3545427103，如果我尝试删除任何东西，比如：

and len(ActualOrderNumber) < 10

它会崩溃...

我的总体目标是使这个过程尽可能高效；在源头修复数据是唯一真正的解决方案吗？

我实施了很多建议，时间降到了1分钟。谢谢！但我不明白的是：

以前，我有丑陋的代码：

现在我有更多的读取量… 运行只需几秒钟…这怎么可能呢？

- Chicago1988

---

LIKE也是非常昂贵的。如果你了解逻辑，肯定有改进这个查询的方法。那么是在订单号字段中检查美元价值吗？这可以改为CONTAINS。还有只返回长度小于10的订单号，这是怎么回事？ - Jacob H

如果有机会的话，请在两个表中添加一个包含清理后订单号的列，填充并索引它。总共所需的时间可能少于九分钟，也可能不少于九分钟。您还可以考虑将最终的“IN”更改为“exists”，但请在更改之间检查查询计划，以确保是否存在优势。 - Nick.McDermaid

哦，你还有一个可能会拖慢速度的UDF。查询计划应该能给你一些想法。为了确定CardinalityEstimate是否真的受到影响，比较实际和预估计划，并查看行数是否在相同的范围内。 - Nick.McDermaid

我在帖子中添加了额外的信息... - Chicago1988

我将代码改为：更新销售表，将 MappingOrderID 设为 ActualOrderNumber。其中，ActualOrderNumber 为长度小于10且为数字的值，并且 MappingOrderID 为空。另外，ActualOrderNumber 必须在 dbo.Orders 的 OrderID 中出现。（这个改动明显更快，但不知道为什么会读取更多？！） - Chicago1988

Answer 1

UPDATE  Sales
SET MappingOrderID = CAST (ltrim(rtrim(ActualOrderNumber)) AS INT) 
FROM    Sales S
inner join dbo.Orders O on ltrim(rtrim(O.OrderID))=ltrim(rtrim(s.ActualOrderNumber))
where   isnumeric(ActualOrderNumber) = 1 
and ActualOrderNumber not like '%.%' 
and len(ActualOrderNumber) < 10
and MappingOrderID is null

变更

1. 删除了函数 "fRemoveNonNumericCharacters"

• 由于您在 where 子句中使用 isnumeric 进行检查，因此无需使用此函数。

2. 合并了销售表和订单表

• 如果按照您所述在 where 子句中使用订单表，则每个销售表的行都会呈现完整的订单表。
• 为了减少这个过程，我添加了连接操作。

3. 删除了 ActualOrderNumeric 不为 null 的条件

• 由于您在 where 子句中使用 isnumeric 进行检查，因此不需要使用该条件。因为对于 null，isnumeric 将返回 0。

- maulik kansara

---

可以加上 "and CONTAINS(ActualOrderNumber, '.')" 这句话，这应该比 NOT LIKE 子句更高效。希望这些列已被索引。 - Jacob H