加速此 Sybase 存储过程

Question

我有这个存储过程，用于填充用户表。它看起来很慢，因为它大约需要平均时间。 6s返回记录。我还能做些什么来调整这个存储过程以使其更快吗？

CREATE PROCEDURE dbo.usmGetPendingAuthorizations
(
    @empCorpId char(8) = null,
    @empFirstName char(30) = null,
    @empLastName char(30) = null,
    @accessCompletionStatus char(20) = null,
    @ownerCorpId char(8) = null,
    @reqCorpId char(8) = null,
    @reqDate datetime = null,
    @rowCount int = 100
)
AS BEGIN       

    SET ROWCOUNT @rowCount

    SELECT
        UPPER(LTRIM(RTRIM(pa.RequestorCorpId))) AS ReqCorpId, 
        UPPER(LTRIM(RTRIM(pa.AccessCompletionStatus))) AS AccessCompletionStatus, 
        UPPER(LTRIM(RTRIM(pa.Comment))) AS ReqComment, 
        UPPER(LTRIM(RTRIM(pa.ValidLoginInd))) AS ValidLoginInd, 
        UPPER(LTRIM(RTRIM(pa.OwnerCorpId))) AS OwnerCorpId, 
        UPPER(LTRIM(RTRIM(pa.UserTypeCode))) AS UserTypeCode, 
        UPPER(LTRIM(RTRIM(pa.SelectMethod))) AS SelectMethod, 
        pa.ExpirationDate AS ExpirationDate, 
        pa.RequestorDate AS ReqDate, 
        pa.BeginDate AS BeginDate, 
        pa.EndDate AS EndDate, 
        UPPER(LTRIM(RTRIM(pa.UserGroupTypeCode))) AS UserGroupTypeCode,
        pa.SubsidiaryId AS SubsidiaryId,    
        UPPER(LTRIM(RTRIM(pa.EmployeeCorpId))) AS EmpCorpId,
        emp.empKeyId AS EmpKeyId,
        LTRIM(RTRIM(emp.firstName)) AS EmpFirstName,
        LTRIM(RTRIM(emp.lastName)) AS EmpLastName
    FROM
        dbo.PendingAuthorization AS pa JOIN capmark..EmployeeDataExtract AS emp
    ON
        UPPER(LTRIM(RTRIM(pa.EmployeeCorpId))) = UPPER(LTRIM(RTRIM(emp.corporateId)))
    WHERE
        UPPER(LTRIM(RTRIM(pa.EmployeeCorpId))) LIKE ISNULL(UPPER(LTRIM(RTRIM(@empCorpId))), '%')
        AND UPPER(LTRIM(RTRIM(emp.firstName))) LIKE ISNULL('%' + UPPER(LTRIM(RTRIM(@empFirstName))) + '%', '%')
        AND UPPER(LTRIM(RTRIM(emp.lastName))) LIKE ISNULL('%' + UPPER(LTRIM(RTRIM(@empLastName))) + '%', '%')
        AND pa.AccessCompletionStatus LIKE ISNULL(UPPER(LTRIM(RTRIM(@accessCompletionStatus))), '%')
        AND pa.OwnerCorpId LIKE ISNULL(UPPER(LTRIM(RTRIM(@ownerCorpId))), '%')
        AND pa.RequestorCorpId LIKE ISNULL(UPPER(LTRIM(RTRIM(@reqCorpId))), '%')
        AND DATEDIFF(dd, pa.RequestorDate, CONVERT(VARCHAR(10), ISNULL(@reqDate, pa.RequestorDate), 101)) = 0

    SET ROWCOUNT 0
END

Answer 1

尽管您似乎从未接受过任何答案，但我会尽力帮助您:)。

首先，我将修改 WHERE 子句。而不是您的 LIKE：

UPPER(LTRIM(RTRIM(pa.EmployeeCorpId))) LIKE ISNULL(UPPER(LTRIM(RTRIM(@empCorpId))), '%')

我会使用这个：

(UPPER(LTRIM(RTRIM(pa.EmployeeCorpId))) LIKE UPPER(LTRIM(RTRIM(@empCorpId)))) OR (@empCorpId IS NULL)

那么，您可以尝试使用以下 WHERE 子句代替您的子句，看看性能是否有任何差异？

WHERE
    (UPPER(LTRIM(RTRIM(pa.EmployeeCorpId))) LIKE UPPER(LTRIM(RTRIM(@empCorpId))) OR @empCorpId IS NULL)
    AND (UPPER(LTRIM(RTRIM(emp.firstName))) LIKE '%' + UPPER(LTRIM(RTRIM(@empFirstName))) + '%' OR @empFirstName IS NULL)
    AND (UPPER(LTRIM(RTRIM(emp.lastName))) LIKE '%' + UPPER(LTRIM(RTRIM(@empLastName))) + '%' OR @empLastName IS NULL)
    AND (pa.AccessCompletionStatus LIKE UPPER(LTRIM(RTRIM(@accessCompletionStatus))) OR @accessCompletionStatus IS NULL)
    AND (pa.OwnerCorpId LIKE UPPER(LTRIM(RTRIM(@ownerCorpId))) OR @ownerCorpId IS NULL)
    AND (pa.RequestorCorpId LIKE UPPER(LTRIM(RTRIM(@reqCorpId))) OR @reqCorpId IS NULL)
    AND (DATEDIFF(dd, pa.RequestorDate, CONVERT(VARCHAR(10), ISNULL(@reqDate, pa.RequestorDate), 101)) = 0)

其次，一般来说，如果 WHERE 子句中引用的列被适当索引，那么 SELECT 的工作速度会更快。

然后，DATEDIFF 和 WHERE 子句中的 CONVERT 也不会加快查询速度。

但是，主要问题是：连接表中有多少行？因为 6 秒可能还不错。您可以检查/使用查询计划以找出任何潜在的瓶颈。

Answer 2

主要问题是函数的自由使用，尤其是在连接中。在以这种方式使用函数的情况下，Sybase 无法利用这些字段上的索引。以连接为例，

ON
    UPPER(LTRIM(RTRIM(pa.EmployeeCorpId))) = UPPER(LTRIM(RTRIM(emp.corporateId)))

所有这些装饰和鞋面真的需要吗？

如果您存储了脏数据 - 混合大小写，有一些前导空格和一些尾随空格，我建议您尝试收紧数据存储和/或更新的方式 - 不允许此类数据进入。一次性清理数据，使所有公司 ID 变为大写，且没有尾随或前导空格。

一旦获得干净的数据，您可以在 EmployeeDataExtract 表中的 CorporateId 列上添加索引（如果已经存在，则重建索引）并将联接更改为

ON
    pa.EmployeeCorpId = emp.corporateId

如果您确实无法确保 PendingAuthorization 表中的干净数据，那么您可以必须将函数保留在连接的那一侧，但至少 emp 表上的索引可供优化器考虑。

将 LIKE 与前沿通配符一起使用会使索引无法使用，但这在您的情况下可能是不可避免的。

看起来 PendingAuthorization.RequestorDate 字段仅用于选择一个日期的数据 - @reqDate 中提供的日期。您可以将 WHERE 子句的该部分转换为范围查询，然后可以使用日期字段上的索引。
为此，您只需使用 @reqDate 的日期部分（忽略一天中的时间），然后从该“日期+1”派生。这些将是使用的值。这是否有很大帮助取决于 PendingAuthorization 表中存在多少个 RequestorDate 天。