四个语句帮你提高 SQL Server 的伸缩性

核心提示： 另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表，来代替使用 GROUP BY 子句，四个语句帮你提高 SQL Server 的伸缩性(7)，在这种情况下，使用 DISTINCT 关键字的 SQL 效率更高，技巧就是进行一些尝试，然后比较它们的性能，请在需要计算聚合

另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表，来代替使用 GROUP BY 子句。在这种情况下，使用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数（SUM、COUNT、MAX 等）的情况下再使用 GROUP BY。另外，如果您的查询总是自己返回一个唯一的行，则不要使用 DISTINCT 关键字。在这种情况下，DISTINCT 关键字只会增加系统开销。

您已经看到了，有大量技术都可用于优化查询和实现特定的业务规则，技巧就是进行一些尝试，然后比较它们的性能。最重要的是要测试、测试、再测试。

Figure 2 Returning All Customers and Their Sales
set nocount on
DECLARE @dtStartDate DATETIME,
@dtEndDate DATETIME,
@dtDate DATETIME
SET @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 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
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.ContactName
ORDER BY
c.CompanyName,
sMonth
Figure 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 INTEGER
SET @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 table
SET @i = 0
WHILE (@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 + 1
END
— Get all clients who had sales
during that period into the "y" table
INSERT 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 @dtEndDate
INSERT INTO @tblFinal
SELECT m.sMonth,
c.CustomerID,
c.CompanyName,
c.ContactName,
0
FROM @tblMonths m CROSS JOIN @tblCustomers c
UPDATE @tblFinal SET
mSales = mydata.mSales
FROM @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.mSales
FROM @tblFinal f
ORDER BY
f.CompanyName,
f.sMonth