五种提高SQL性能的方法

核心提示： 图2. Returning All Customers and Their Sales set nocount onDECLARE @dtStartDate DATETIME, @dtEndDate DATETIME, @dtDate DATETIMESET @dtEndDate = '5/5/

图2. Returning All Customers and Their Sales

set nocount onDECLARE @dtStartDate DATETIME,     @dtEndDate DATETIME,    @dtDate DATETIMESET @dtEndDate = '5/5/1997'SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1)     AS VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + '     23:59:59' AS DATETIME))SET @dtStartDate = DATEADD(MM, -1 * 12, @dtEndDate)SELECT    CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +        CASE         WHEN MONTH(o.OrderDate) < 10         THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))        ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))    END AS sMonth,    c.CustomerID,    c.CompanyName,    c.ContactName,    SUM(od.Quantity * od.UnitPrice) AS mSalesFROM Customers c    INNER JOIN Orders o ON c.CustomerID = o.CustomerID    INNER JOIN [Order Details] od ON o.OrderID = od.OrderIDWHERE    o.OrderDate BETWEEN @dtStartDate AND @dtEndDateGROUP BY    CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +        CASE         WHEN MONTH(o.OrderDate) < 10         THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))        ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))    END,    c.CustomerID,    c.CompanyName,    c.ContactNameORDER BY    c.CompanyName,    sMonth

---

图 3 Cartesian Product at Work

DECLARE @tblMonths TABLE (sMonth VARCHAR(7))DECLARE @tblCustomers TABLE (    CustomerID CHAR(10),            CompanyName VARCHAR(50),            ContactName VARCHAR(50))DECLARE @tblFinal TABLE (    sMonth VARCHAR(7),             CustomerID CHAR(10),            CompanyName VARCHAR(50),            ContactName VARCHAR(50),            mSales MONEY)DECLARE @dtStartDate DATETIME,     @dtEndDate DATETIME,    @dtDate DATETIME,    @i INTEGERSET @dtEndDate = '5/5/1997'SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1) AS        VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + '     23:59:59' AS DATETIME))SET @dtStartDate = DATEADD(MM, -1 * 12, @dtEndDate)— Get all months into the first tableSET @i = 0WHILE (@i < 12)BEGIN    SET @dtDate = DATEADD(mm, -1 * @i, @dtEndDate)    INSERT INTO @tblMonths SELECT CAST(YEAR(@dtDate) AS VARCHAR(4)) + '-' +                CASE                 WHEN MONTH(@dtDate) < 10                     THEN '0' + CAST(MONTH(@dtDate) AS VARCHAR(2))                ELSE CAST(MONTH(@dtDate) AS VARCHAR(2))                END AS sMonth    SET @i = @i + 1END— Get all clients who had sales during that period into the "y" tableINSERT INTO @tblCustomers    SELECT    DISTINCT        c.CustomerID,        c.CompanyName,        c.ContactName    FROM Customers c        INNER JOIN Orders o ON c.CustomerID = o.CustomerID    WHERE    o.OrderDate BETWEEN @dtStartDate AND @dtEndDateINSERT INTO @tblFinalSELECT    m.sMonth,    c.CustomerID,    c.CompanyName,    c.ContactName,    0FROM @tblMonths m CROSS JOIN @tblCustomers cUPDATE @tblFinal  SET     mSales = mydata.mSalesFROM @tblFinal f INNER JOIN     (    SELECT    c.CustomerID,        CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +        CASE WHEN MONTH(o.OrderDate) < 10             THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))            ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))        END AS sMonth,        SUM(od.Quantity * od.UnitPrice) AS mSales    FROM Customers c        INNER JOIN Orders o ON c.CustomerID = o.CustomerID        INNER JOIN [Order Details] od ON o.OrderID = od.OrderID    WHERE    o.OrderDate BETWEEN @dtStartDate AND @dtEndDate    GROUP BY        c.CustomerID,        CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +        CASE WHEN MONTH(o.OrderDate) < 10             THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))            ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))        END    ) mydata on f.CustomerID = mydata.CustomerID AND f.sMonth =        mydata.sMonth SELECT    f.sMonth,    f.CustomerID,    f.CompanyName,    f.ContactName,    f.mSalesFROM @tblFinal fORDER BY    f.CompanyName,    f.sMonth

图2 中的 SQL 就执行了上述操作。

虽然这看起来好像没什么神奇的，但是请考虑一下，如果您从客户到定单（这些定单按月份进行分组并对销售额进行小计）进行了标准的 INNER JOIN，则只会获得客户有定单的月份。因此，对于客户未订购任何产品的月份，您不会获得 0 值。如果您想为每个客户都绘制一个图，以显示每个月和该月销售额，则可能希望此图包括月销售额为 0 的月份，以便直观标识出这些月份。如果使用 图 2 中的 SQL，数据则会跳过销售额为 0 美元的月份，因为在定单表中对于零销售额不会包含任何行（假设您只存储发生的事件）。

图3 中的代码虽然较长，但是可以达到获取所有销售数据（甚至包括没有销售额的月份）的目标。首先，它会提取去年所有月份的列表，然后将它们放入第一个 TABLE 数据类型表 (@tblMonths) 中。下一步，此代码会获取在该时间段内有销售额的所有客户公司的名称列表，然后将它们放入另一个 TABLE 数据类型表 (@tblCus-tomers) 中。这两个表存储了创建结果集所必需的所有基本数据，但实际销售数量除外。第一个表中列出了所有月份（12 行），第二个表中列出了这个时间段内有销售额的所有客户（对于我是 81 个）。并非每个客户在过去 12 个月中的每个月都购买了产品，所以，执行 INNER JOIN 或 LEFT JOIN 不会返回每个月的每个客户。这些操作只会返回购买产品的客户和月份。 笛卡尔乘积则可以返回所有月份的所有客户。笛卡尔乘积基本上是将第一个表与第二个表相乘，生成一个行集合，其中包含第一个表中的行数与第二个表中的行数相乘的结果。因此，笛卡尔乘积会向表 @tblFinal 返回 972 行。最后的步骤是使用此日期范围内每个客户的月销售额总计更新 @tblFinal 表，以及选择最终的行集。 如果由于笛卡尔乘积占用的资源可能会很多，而不需要真正的笛卡尔乘积，则可以谨慎地使用 CROSS JOIN。例如，如果对产品和类别执行了 CROSS JOIN，然后使用 WHERE 子句、DISTINCT 或 GROUP BY 来筛选出大多数行，那么使用 INNER JOIN 会获得同样的结果，而且效率高得多。如果需要为所有的可能性都返回数据（例如在您希望使用每月销售日期填充一个图表时），则笛卡尔乘积可能会非常有帮助。但是，您不应该将它们用于其他用途，因为在大多数方案中 INNER JOIN 的效率要高得多。

拾遗补零

这里介绍其他一些可帮助提高 SQL 查询效率的常用技术。假设您将按区域对所有销售人员进行分组并将他们的销售额进行小计，但是您只想要那些数据库中标记为处于活动状态的销售人员。您可以按区域对销售人员分组，并使用 HAVING 子句消除那些未处于活动状态的销售人员，也可以在 WHERE 子句中执行此操作。在 WHERE 子句中执行此操作会减少需要分组的行数，所以比在 HAVING 子句中执行此操作效率更高。HAVING 子句中基于行的条件的筛选会强制查询对那些在 WHERE 子句中会被去除的数据进行分组。

另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表，来代替使用 GROUP BY 子句。在这种情况下，使用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数（SUM、COUNT、MAX 等）的情况下再使用 GROUP BY。另外，如果您的查询总是自己返回一个唯一的行，则不要使用 DISTINCT 关键字。在这种情况下，DISTINCT 关键字只会增加系统开销。您已经看到了，有大量技术都可用于优化查询和实现特定的业务规则，技巧就是进行一些尝试，然后比较它们的性能。最重要的是要测试、测试、再测试。在此专栏的将来各期内容中，我将继续深入讲述 sql server(WINDOWS平台上强大的数据库平台) 概念，包括数据库设计、好的索引实践以及 sql server(WINDOWS平台上强大的数据库平台) 安全范例。 (完)