从 INSERT 返回 IDENTITY
SELECT @@IDENTITY
内嵌视图与临时表
临时表 在 tempdb 中的临时表会导致查询进行大量 I/O 操作和磁盘访问临时表会消耗大量资源
内嵌视图 使用内嵌视图取代临时表内嵌视图只是一个可以联接到 FROM 子句中的查询如果只需要将数据联接到其他查询则可以试试使用内嵌视图以节省资源
避免 LEFT JOIN 和 NULL
LEFT JOIN 消耗的资源非常之多因为它们包含与 NULL(不存在)数据匹配的数据在某些情况下这是不可避免的但是代价可能非常高LEFT JOIN 比 INNER JOIN 消耗资源更多所以如果您可以重新编写查询以使得该查询不使用任何 LEFT JOIN则会得到非常可观的回报
加快使用 LEFT JOIN 的查询速度的一项技术涉及创建一个 TABLE 数据类型插入第一个表(LEFT JOIN 左侧的表)中的所有行然后使用第二个表中的值更新 TABLE 数据类型此技术是一个两步的过程但与标准的 LEFT JOIN 相比可以节省大量时间一个很好的规则是尝试各种不同的技术并记录每种技术所需的时间直到获得用于您的应用程序的执行性能最佳的查询
DECLARE @tblMonths TABLE (sMonth VARCHAR())
灵活使用笛卡尔乘积
对于此技巧我将进行非常详细的介绍并提倡在某些情况下使用笛卡尔乘积出于某些原因笛卡尔乘积 (CROSS JOIN) 遭到了很多谴责开发人员通常会被警告根本就不要使用它们在许多情况下它们消耗的资源太多从而无法高效使用但是像 SQL 中的任何工具一样如果正确使用它们也会很有价值
其中一段示例代码值得效仿
笛卡尔乘积则可以返回所有月份的所有客户笛卡尔乘积基本上是将第一个表与第二个表相乘生成一个行集合其中包含第一个表中的行数与第二个表中的行数相乘的结果因此笛卡尔乘积会向表 @tblFinal 返回 (所有月份)*(所有客户)= 行最后的步骤是使用此日期范围内每个客户的月销售额总计更新 @tblFinal 表以及选择最终的行集
DECLARE @tblMonths TABLE (sMonth VARCHAR())
DECLARE @tblCustomers TABLE ( CustomerID CHAR()
CompanyName VARCHAR()
ContactName VARCHAR())
DECLARE @tblFinal TABLE ( sMonth VARCHAR()
CustomerID CHAR()
CompanyName VARCHAR()
ContactName VARCHAR()
mSales MONEY)
DECLARE @dtStartDate DATETIME
@dtEndDate DATETIME
@dtDate DATETIME
@i INTEGER
SET @dtEndDate = //
SET @dtEndDate = DATEADD(DD CAST(CAST((MONTH(@dtEndDate) + ) AS
VARCHAR()) + // + CAST(YEAR(@dtEndDate) AS VARCHAR()) + :: AS DATETIME))
SET @dtStartDate = DATEADD(MM * @dtEndDate)
Get all months into the first table
SET @i =
WHILE (@i < )
BEGIN
SET @dtDate = DATEADD(mm * @i @dtEndDate)
INSERT INTO @tblMonths SELECT CAST(YEAR(@dtDate) AS VARCHAR()) + +
CASE
WHEN MONTH(@dtDate) <
THEN + CAST(MONTH(@dtDate) AS VARCHAR())
ELSE CAST(MONTH(@dtDate) AS VARCHAR())
END AS sMonth
SET @i = @i +
END
Get all clients who had sales during that period into the y table
INSERT INTO @tblCustomers
SELECT DISTINCT
cCustomerID
cCompanyName
cContactName
FROM Customers c
INNER JOIN Orders o ON cCustomerID = oCustomerID
WHERE oOrderDate BETWEEN @dtStartDate AND @dtEndDate
INSERT INTO @tblFinal
SELECT msMonth
cCustomerID
cCompanyName
cContactName
FROM @tblMonths m CROSS JOIN @tblCustomers c
UPDATE @tblFinal SET
mSales = mydatamSales
FROM @tblFinal f INNER JOIN
(
SELECT cCustomerID
CAST(YEAR(oOrderDate) AS VARCHAR()) + +
CASE WHEN MONTH(oOrderDate) <
THEN + CAST(MONTH(oOrderDate) AS VARCHAR())
ELSE CAST(MONTH(oOrderDate) AS VARCHAR())
END AS sMonth
SUM(odQuantity * odUnitPrice) AS mSales
FROM Customers c
INNER JOIN Orders o ON cCustomerID = oCustomerID
INNER JOIN [Order Details] od ON oOrderID = odOrderID
WHERE oOrderDate BETWEEN @dtStartDate AND @dtEndDate
GROUP BY
cCustomerID
CAST(YEAR(oOrderDate) AS VARCHAR()) + +
CASE WHEN MONTH(oOrderDate) <
THEN + CAST(MONTH(oOrderDate) AS VARCHAR())
ELSE CAST(MONTH(oOrderDate) AS VARCHAR())
END
) mydata on fCustomerID = mydataCustomerID AND fsMonth =
mydatasMonth
SELECT fsMonth
fCustomerID
fCompanyName
fContactName
fmSales
FROM @tblFinal f
ORDER BY
fCompanyName
fsMonth
另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表来代替使用 GROUP BY 子句在这种情况下使用 DISTINCT 关键字的 SQL 效率更高请在需要计算聚合函数(SUMCOUNTMAX 等)的情况下再使用 GROUP BY另外如果您的查询总是自己返回一个唯一的行则不要使用 DISTINCT 关键字在这种情况下DISTINCT 关键字只会增加系统开销
