[SQL] Sql效能陷阱及優化

 如題，把一些工作上遇到的SQL效能問題記錄下來:

1.    盡量不要在 WHERE 條件中，把Table的欄位進行 Convert , Cast ... 轉換處理！

    原因: 此動作會造成 Table 中每筆資料都會轉置，當資料量大時，會造成效能瓶頸。

    解決辦法: 將 WHERE 要比較的參數進行轉換，而不是對 Table 的參數進行轉換，甚至可以先在方 DECLARE 一個變數先轉換好，在放到 SQL 語句中。

2.    IN、JOIN、EXISTS 如何選擇:

    2.1    盡量不要在 IN 中處理子查詢、CTE！

        原因:  因為每次 IN 都會重新查一次子查詢、CTE，資料量大時，效能極差

        解決辦法:  將 子查詢 以 CTE 方式處理，之後再 JOIN 或 EXISTS

    2.2   面臨 子查詢、CTE， JOIN 或 EXISTS 兩種取捨！

        解決思路: 
                            a.    理論上效能優劣: EXISTS  >  JOIN，因為一旦 EXISTS 尋找找到特定內容，查詢執行就會停止，而 JOIN 將一直持續到最後。
                            b.    若資料量小，效能優劣: JOIN  >  EXISTS
                            c.    若資料量大，效能優劣: EXISTS  >  JOIN                            

                           

IN	EXISTS	JOINS
It works like a multiple OR operator. So, it exempts us to write OR multiple times in a query.	It returns TRUE value if matching is found.	It is used to join two or more tables into a single table.
All the values inside IN operator will be scanned and then decision is made.	If we get TRUE value for a single condition, it will stop its execution.	It will first check whether matching takes place or not and then join the two tables on the basis of matching columns in both tables.
It returns TRUE, FALSE as well as NULL values.	It returns either TRUE or FALSE.	It returns NULL entry in joined table if matching is not present.
It can be used both in nested queries as well as with values as we have seen in the example above.	It is only used on nested queries.	JOIN can also be used with nested queries.
It is less efficient when IN is used with subquery because the entire subquery will execute first by the relational database and then the final execution takes place on the basis of condition specified. However, for larger relational table IN might work faster than EXISTS and JOIN in subquery.	In case of EXISTS we know that it will return either TRUE or FALSE on the basis of the condition specified by the user. So, for a small table entry in the subquery EXISTS work more efficient than IN.	It is similar to EXISTS operator. If the subquery table has relatively lesser data then execution will be more efficient as compared to IN.

參考資料:

1.    Can I get better performance using a JOIN or using EXISTS?

2.    Difference Between JOIN, IN and EXISTS Clause in SQL