提高"SQL Server"性能的几种实用技术

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