时间戳

首页 > 解决方案 > 如何添加 CTE 以提高存储过程性能？

sql - 如何添加 CTE 以提高存储过程性能？

问题描述

我有一个如下所示的存储过程：

CREATE PROCEDURE [Schema].[ProcName]
    (@PhoneNum  VARCHAR(100)
     @EmailAddr VARCHAR(100)
     @DriverLic VARCHAR(100)
     @EligiblityDate VARCHAR(10))
AS
BEGIN
SET NOCOUNT ON;
    DECLARE @Phone BIT = 0,
            @Email BIT = 0,
            @License BIT = 0

    IF (SELECT Value FROM Table 
        WHERE Product = 'ProductA' 
          AND @EligibilityDate BETWEEN EligStart AND EligEnd 
          AND ProductType = 1 
          AND Value = @PhoneNum 
          AND IsActive = 1) 
        SET @Phone = 1

    IF (SELECT Value FROM Table 
        WHERE Product = 'ProductA' 
          AND @EligibilityDate BETWEEN EligStart AND EligEnd 
          AND ProductType = 2 
          AND Value = @EmailAddr 
          AND IsActive = 1) 
        SET @Email = 1

    IF (SELECT Value FROM Table 
        WHERE Product = 'ProductA' 
          AND @EligibilityDate BETWEEN EligStart AND EligEnd 
          AND ProductType = 3 
          AND Value = @DriverLic 
          AND IsActive = 1) 
        SET @License = 1

    SELECT 
        @Phone AS Phone,
        @Email AS Email,
        @License AS License;

    RETURN 0;

我希望通过使用 CTE 来提高此查询的性能。但是，我不知道该怎么做，因为我以前从未使用过 CTE。

标签： sqlsql-serverstored-procedurescommon-table-expression

解决方案

因此，为此，CTE 对您没有好处。CTE 适用于您在一个查询中重复使用子选择，或者如果您尝试执行递归操作，并且它们通常不如我们希望的那样有用。对于您的查询的复杂性，我会担心首先将基础知识直接放在您的脑海中，然后在您发现自己需要 CTE 时担心它们，而不是在没有令人信服的理由的情况下接触它们。

我认为您在这里需要的更像是以下内容：

Create Procedure [Schema].[ProcName]
    (@PhoneNum   VARCHAR(100),
    @EmailAddr  VARCHAR(100),
    @DriverLic  VARCHAR(100),
    @EligiblityDate VARCHAR(10))
AS
BEGIN

    select
        max(case when ProductType = 1 and [Value] = @PhoneNum then 1 else 0 end) as PhoneNum,
        max(case when ProductType = 2 and [Value] = @EmailAddr then 1 else 0 end) as EmailAddr,
        max(case when ProductType = 3 and [Value] = @DriverLic then 1 else 0 end) as DriverLic
    from [Table]
    WHERE
        Product = 'ProductExample'
        AND @EligibilityDate BETWEEN EligStart and EligEnd
        AND IsActive = 1
END

这具有将多个查询连接在一起的类似效果（当您复制这样的代码时您会怀疑！）到单个查询中，但不需要子选择。

推荐阅读