提高sql效能資源(1)

五種提高 SQL 效能的方法(轉)

從 INSERT 返回 IDENTITY
我決定從遇到許多問題的內容入手：如何在執行 SQL INSERT 後檢索 IDENTITY 值。通常，問題不在於如何編寫檢索值的查詢，而在於在哪裡以及何時進行檢索。在 SQL Server 中，下面的語句可用於檢索由最新在活動資料庫連線上執行的 SQL 語句所建立的 IDENTITY 值：

SELECT @@IDENTITY這個 SQL 語句並不複雜，但需要記住的一點是：如果這個最新的 SQL 語句不是 INSERT，或者您針對非 INSERT SQL 的其他連線執行了此 SQL，則不會獲得期望的值。您必須執行下列程式碼才能檢索緊跟在 INSERT SQL 之後且位於同一連線上的 IDENTITY，如下所示：

INSERT INTO Products (ProductName) VALUES ('Chalk')
SELECT @@IDENTITY在一個連線上針對 Northwind 資料庫執行這些查詢將返回一個名稱為 Chalk 的新產品的 IDENTITY 值。所以，在使用 ADO 的 Visual Basic? 應用程式中，可以執行以下語句：

Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _
(ProductName) VALUES ('Chalk');SELECT @@IDENTITY"
lProductID = oRs(0)此程式碼告訴 SQL Server 不要返回查詢的行計數，然後執行 INSERT 語句，並返回剛剛為這個新行建立的 IDENTITY 值。SET NOCOUNT ON 語句表示返回的記錄集有一行和一列，其中包含了這個新的 IDENTITY 值。如果沒有此語句，則會首先返回一個空的記錄集（因為 INSERT 語句不返回任何資料），然後會返回第二個記錄集，第二個記錄集中包含 IDENTITY 值。這可能有些令人困惑，尤其是因為您從來就沒有希望過 INSERT 會返回記錄集。之所以會發生此情況，是因為 SQL Server 看到了這個行計數（即一行受到影響）並將其解釋為表示一個記錄集。因此，真正的資料被推回到了第二個記錄集。當然您可以使用 ADO 中的 NextRecordset 方法獲取此第二個記錄集，但如果總能夠首先返回該記錄集且只返回該記錄集，則會更方便，也更有效率。

此方法雖然有效，但需要在 SQL 語句中額外新增一些程式碼。獲得相同結果的另一方法是在 INSERT 之前使用 SET NOCOUNT ON 語句，並將 SELECT @@IDENTITY 語句放在表中的 FOR INSERT 觸發器中，如下面的程式碼片段所示。這樣，任何進入該表的 INSERT 語句都將自動返回 IDENTITY 值。

CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS
    SELECT @@IDENTITY
GO
觸發器只在 Products 表上發生 INSERT 時啟動，所以它總是會在成功 INSERT 之後返回一個 IDENTITY。使用此技術，您可以始終以相同的方式在應用程式中檢索 IDENTITY 值。

返回頁首
內嵌檢視與臨時表
某些時候，查詢需要將資料與其他一些可能只能通過執行 GROUP BY 然後執行標準查詢才能收集的資料進行聯接。例如，如果要查詢最新五個定單的有關資訊，您首先需要知道是哪些定單。這可以使用返回定單 ID 的 SQL 查詢來檢索。此資料就會儲存在臨時表（這是一個常用技術）中，然後與 Products 表進行聯接，以返回這些定單售出的產品數量：

CREATE TABLE #Temp1 (OrderID INT NOT NULL, _
                     OrderDate DATETIME NOT NULL)
INSERT INTO #Temp1 (OrderID, OrderDate)
SELECT     TOP 5 o.OrderID, o.OrderDate
FROM Orders o ORDER BY o.OrderDate DESC
SELECT     p.ProductName, SUM(od.Quantity) AS ProductQuantity
FROM     #Temp1 t
    INNER JOIN [Order Details] od ON t.OrderID = od.OrderID
    INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName
ORDER BY p.ProductName
DROP TABLE #Temp1這些 SQL 語句會建立一個臨時表，將資料插入該表中，將其他資料與該表進行聯接，然後除去該臨時表。這會導致此查詢進行大量 I/O 操作，因此，可以重新編寫查詢，使用內嵌檢視取代臨時表。內嵌檢視只是一個可以聯接到 FROM 子句中的查詢。所以，您不用在 tempdb 中的臨時表上耗費大量 I/O 和磁碟訪問，而可以使用內嵌檢視得到同樣的結果：

SELECT p.ProductName,
    SUM(od.Quantity) AS ProductQuantity
FROM     (
    SELECT TOP 5 o.OrderID, o.OrderDate
    FROM     Orders o
    ORDER BY o.OrderDate DESC
    ) t
    INNER JOIN [Order Details] od ON t.OrderID = od.OrderID
    INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY
    p.ProductName
ORDER BY
    p.ProductName此查詢不僅比前面的查詢效率更高，而且長度更短。臨時表會消耗大量資源。如果只需要將資料聯接到其他查詢，則可以試試使用內嵌檢視，以節省資源。

返回頁首
避免 LEFT JOIN 和 NULL
當然，有很多時候您需要執行 LEFT JOIN 和使用 NULL 值。但是，它們並不適用於所有情況。改變 SQL 查詢的構建方式可能會產生將一個花幾分鐘執行的報告縮短到只花幾秒鐘這樣的天壤之別的效果。有時，必須在查詢中調整資料的形態，使之適應應用程式所要求的顯示方式。雖然 TABLE 資料型別會減少大量佔用資源的情況，但在查詢中還有許多區域可以進行優化。SQL 的一個有價值的常用功能是 LEFT JOIN。它可以用於檢索第一個表中的所有行、第二個表中所有匹配的行、以及第二個表中與第一個表不匹配的所有行。例如，如果希望返回每個客戶及其定單，使用 LEFT JOIN 則可以顯示有定單和沒有定單的客戶。

此工具可能會被過度使用。LEFT JOIN 消耗的資源非常之多，因為它們包含與 NULL（不存在）資料匹配的資料。在某些情況下，這是不可避免的，但是代價可能非常高。LEFT JOIN 比 INNER JOIN 消耗資源更多，所以如果您可以重新編寫查詢以使得該查詢不使用任何 LEFT JOIN，則會得到非常可觀的回報（請參閱圖 1 中的圖）。

圖 1 查詢

加快使用 LEFT JOIN 的查詢速度的一項技術涉及建立一個 TABLE 資料型別，插入第一個表（LEFT JOIN 左側的表）中的所有行，然後使用第二個表中的值更新 TABLE 資料型別。此技術是一個兩步的過程，但與標準的 LEFT JOIN 相比，可以節省大量時間。一個很好的規則是嘗試各種不同的技術並記錄每種技術所需的時間，直到獲得用於您的應用程式的執行效能最佳的查詢。

測試查詢的速度時，有必要多次執行此查詢，然後取一個平均值。因為查詢（或儲存過程）可能會儲存在 SQL Server 記憶體中的過程快取中，因此第一次嘗試耗費的時間好像稍長一些，而所有後續嘗試耗費的時間都較短。另外，執行您的查詢時，可能正在針對相同的表執行其他查詢。當其他查詢鎖定和解鎖這些表時，可能會導致您的查詢要排隊等待。例如，如果您進行查詢時某人正在更新此表中的資料，則在更新提交時您的查詢可能需要耗費更長時間來執行。

避免使用 LEFT JOIN 時速度降低的最簡單方法是儘可能多地圍繞它們設計資料庫。例如，假設某一產品可能具有類別也可能沒有類別。如果 Products 表儲存了其類別的 ID，而沒有用於某個特定產品的類別，則您可以在欄位中儲存 NULL 值。然後您必須執行 LEFT JOIN 來獲取所有產品及其類別。您可以建立一個值為“No Category”的類別，從而指定外來鍵關係不允許 NULL 值。通過執行上述操作，現在您就可以使用 INNER JOIN 檢索所有產品及其類別了。雖然這看起來好像是一個帶有多餘資料的變通方法，但可能是一個很有價值的技術，因為它可以消除 SQL 批處理語句中消耗資源較多的 LEFT JOIN。在資料庫中全部使用此概念可以為您節省大量的處理時間。請記住，對於您的使用者而言，即使幾秒鐘的時間也非常重要，因為當您有許多使用者正在訪問同一個聯機資料庫應用程式時，這幾秒鐘實際上的意義會非常重大。

返回頁首
靈活使用笛卡爾乘積
對於此技巧，我將進行非常詳細的介紹，並提倡在某些情況下使用笛卡爾乘積。出於某些原因，笛卡爾乘積 (CROSS JOIN) 遭到了很多譴責，開發人員通常會被警告根本就不要使用它們。在許多情況下，它們消耗的資源太多，從而無法高效使用。但是像 SQL 中的任何工具一樣，如果正確使用，它們也會很有價值。例如，如果您想執行一個返回每月資料（即使某一特定月份客戶沒有定單也要返回）的查詢，您就可以很方便地使用笛卡爾乘積。 圖 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 關鍵字只會增加系統開銷。

Tips for Writing
Efficient SQL Queries
From:
Vigyan Kaushik
2
In order to improve overall application performance, it’s very important to construct SQL queries
in the most efficient way. There can be many different ways to write a SQL query. Here are few
tips that can help you in writing efficient and reusable SQL queries. All examples given below
are based on Oracle default demo tables EMP and DEPT. You can create these tables in your
local schema from the following directory in windows environment.
%ORACLE_HOME%/sqlplus/demo/demobld.sql
Order of the tables in Joins: If you specify 2 or more tables in the FROM clause of a SELECT
statement, then Oracle parser will process the tables from right to left, so the table name you
specify last will be processed first. In this case you have to choose one table as driving table.
Always choose the table with less number of records as the driving table.
Name the Columns in a Query: There are three good reasons why it is better to name the
columns in a query rather than to use "select * from ...".
1. Network traffic is reduced. This can have a significant impact on performance if the table has
a large number of columns, or the table has a long or long raw column (both of which can be up
to 2 GB in length). These types of columns will take a long time to transfer over the network and
so they should not be fetched from the database unless they are specifically required.
2. The code is easier to understand.
3. It could save the need for changes in the future. If any columns is added to or removed from
the base table/view, then “select * “statement can produce wrong results set and statement may
fail.
Use table alias: Always use table alias and prefix all column names with the aliases when you
are using more than one table.
Never compare NULL to anything else: All expressions return NULL if one of the operands is
NULL. This is applicable for all operators except Concatenation operator (||).
Use Bind Variables: It is also better to use bind variables in queries. That way the query
becomes generic and therefore re-usable. For example, instead of writing a query like -
SELECT ename, sal
FROM emp
WHERE deptno = 20;
Change it to -
SELECT ename, sal
FROM emp
WHERE deptno = :deptno;
The first query can be re-used for deptno number 20 only, whereas the second query can be reused
for any other deptno also.
3
SQL Writing Convention: It is a good practice to use a standard syntax for wiring SQL queries.
I will recommend following standards to use while writing SQL queries.
Write all standard SQL TEXT in upper case:
For example:
SELECT ename, sal
FROM emp
WHERE deptno = 20;
Write all non standard SQL TEXT (Table name, Column name etc) in lower case:
For example:
SELECT ename, sal
FROM emp
WHERE deptno = 20;
Formatter Plus in Toad can be use to format SQL statements in this format. Select the complete
SQL statement and right click on the “Format Code” menu.
Note: It is important to write similar SQL statement in same case.
For example: Oracle will reparse following queries as they are not written in the same case
Select * from EMP;
Select * from emp;
Use EXISTS instead of DISTINCT: Use EXISTS in place of DISTINCT if you want the result
set to contain distinct values while joining tables.
For example:
SELECT DISTINCT d.deptno, d.dname
FROM dept d, emp e
WHERE d.deptno = e.deptno;
The following SQL statement is a better alternative.
SELECT d.deptno, d.dname
FROM dept d
WHERE EXISTS (SELECT e.deptno
FROM emp e
WHERE d.deptno = e.deptno);
Use of expressions and indexes: The optimizer fully evaluates expressions whenever
possible and translates certain syntactic constructs into equivalent constructs. This is done
either because Oracle can more quickly evaluate the resulting expression than the original
4
expression or because the original expression is merely a syntactic equivalent of the resulting
expression.
Any computation of constants is performed only once when the statement is optimized rather
than each time the statement is executed. Consider these conditions that test for salaries
greater than $2000.
sal > 24000/12
sal > 2000
sal*12 > 24000
If a SQL statement contains the first condition, the optimizer simplifies it into the second
condition.
Please note that optimizer does not simplify expressions across comparison operators. The
optimizer does not simplify the third expression into the second. For this reason, we should
write conditions that compare columns with constants whenever possible, rather than conditions
with expressions involving columns.
The Optimizer does not use index for the following statement:
SELECT *
FROM emp
WHERE sal*12 > 24000 ;
Instead of this use the following statement:
SELECT *
FROM emp
WHERE sal > 24000/12 ;
Use of NOT operator on indexed columns: Never use NOT operator on an indexed column.
Whenever Oracle encounters a NOT on an index column, it will perform full-table scan.
For Example:
SELECT *
FROM emp
WHERE NOT deptno = 0;
Instead use the following:
SELECT *
FROM emp
WHERE deptno > 0;
Function or Calculation on indexed columns: Never use a function or calculation on an
indexed column. If there is any function is used on an index column, optimizer will not use
index.
For Example:
5
Do not use until need exactly match string:
SELECT *
FROM emp
WHERE SUBSTR (ename, 1, 3) = 'MIL';
Use following instead:
SELECT *
FROM emp
WHERE ename LIKE 'MIL%';
Do not use the following as || is the concatenate function. Like other functions and it disables
index.
SELECT *
FROM emp
WHERE ename || job = 'MILLERCLERK';
Use the following instead
SELECT *
FROM emp
WHERE ename = 'MILLER' AND job = 'CLERK';.
Avoid Transformed Columns in the WHERE Clause: Use untransformed column values.
For example, use:
WHERE a.order_no = b.order_no
Rather than
WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))
= TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))
Combine Multiples Scans with CASE Statements: Often, it is necessary to calculate
different aggregates on various sets of tables. Usually, this is done with multiple scans on the
table, but it is easy to calculate all the aggregates with one single scan. Eliminating n-1 scans
can greatly improve performance.
Combining multiple scans into one scan can be done by moving the WHERE condition of each
scan into a CASE statement, which filters the data for the aggregation. For each aggregation,
there could be another column that retrieves the data.
The following example has count of all employees who earn less then 2000, between 2000 and
4000, and more than 4000 each month. This can be done with three separate queries.
SELECT COUNT (*)
6
FROM emp
WHERE sal < 2000;
SELECT COUNT (*)
FROM emp
WHERE sal BETWEEN 2000 AND 4000;
SELECT COUNT (*)
FROM emp
WHERE sal>4000;
However, it is more efficient to run the entire query in a single statement. Each number is
calculated as one column. The count uses a filter with the CASE statement to count only the
rows where the condition is valid. For example:
SELECT COUNT (CASE WHEN sal < 2000
THEN 1 ELSE null END) count1,
COUNT (CASE WHEN sal BETWEEN 2001 AND 4000
THEN 1 ELSE null END) count2,
COUNT (CASE WHEN sal > 4000
THEN 1 ELSE null END) count3
FROM emp;
Please feel free to write your questions/comments at vkaushik@dbapool.com