從 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 關鍵字只會增加系統開銷
From:http://tw.wingwit.com/Article/program/Java/Javascript/201311/25338.html
