SQL query speed up

In our daily work, we may find that sometimes extracting data from database is too time-consuming that will drive people crazy. So there are several tips that can optimize the SQL query.

1. make use of temporary table.
   If you need to double-dipp into large tables, the temporary tables will be helpful in greatly decreasing the processing time.
   There are 2 usages of temporary tables, local and global temporary table. As for the local temporary table, we can simply use select column into #temp from database where condition to create a temporary table. Local temporary table will be only available on the current session. If you want to cite the temporary across the sessions, the global temporary table can be used. You can use select column into ##temp from database where condition to create a global temporary table

2. proper use condition in where-clause
   Sometimes, you are required to extract data which meet several conditions. This is an art to proper use condition in where-clause. Generally, we will obtain data by executing query SQL_training_6.png select column from database where condition1 and condition2 and condition3
   However, if we rearrange the conditions, let the condition 3 be the strictest one, condition 2 is stricter than condition 1, it will be the fastest query we can get our result. We may do a test, the left one with two conditions that condition 1 is strict and condition 2 is loos, while the right one with loose condition 1 and strict condition 2. The result shows that the right one is more efficient than the left one.

1. replace OR with UNION ALL
   In SQL, OR is an inefficient operator. If you encounter cases you have to use OR, you may rewrite query without using OR. UNION ALL is a solution that can achieve the effect of OR and save more time.The left one uses the operator OR while the right one uses the UNION ALL.

1. replace OR with IN
   There is another solution that can replace OR which is using IN. The left one adopts IN while the right one uses OR.

1. replace IN with EXISTS
   Since IN is not so efficient compared to EXISTS. So we may rewrite query using EXISTS instead. There is a comparison below. The left one is the query written by EXISTS while the right one is written by IN. The CPU processing time using EXISTS is less than using IN. The left one is the outcome of using EXISTS, you may find the CPU processing time is less than the right one using IN.

1. parallel query
   SQL server provides a parallel query. We can activate an option at the end of the query.
   usage: option(maxdop n), n denotes the number of parallel pools.
   Listed below is a comparison of the same query using NO/2/4 parallel pools. The left one uses no parallel pool versus the middle one with 2 pools anf the right one with 4 pools. You may notice that execultion of the same query with 4 parallel pools outperform 2 parallel pools. And the query without any parallel pool speeds double time than with parallel pools.

转载于:https://www.cnblogs.com/PeterShengShijie/p/9493883.html