Optimize SQL Server System Performance

Original http://blog.csdn.net/guoxuepeng123/article/details/7849681

1. subquery usage
A subquery is a SELECT query nested in select, insert, update, and delete
Statement or other subqueries. Subqueries can be used wherever expressions are allowed. Subqueries allow for flexible programming and can be used to implement some special functions. However, in terms of performance
Improper subquery usage may cause a performance bottleneck.
For subqueries related to a, not in, and not exists, you can use left join instead. For example:
Select pub_name
From publishers
Where pub_id not in
(Select pub_id
From titles
Where type = 'business ')
-- Can be rewritten:
Select a. pub_name
From publishers a left join titles B
On B. type = 'business' and
A. pub_id = B. pub_id
Where B. pub_id is null

Select title
From titles
Where not exists
(Select title_id
From sales
Where title_id = titles. title_id)
-- Can be rewritten:
Select title
From titles left join sales
On sales. title_id = titles. title_id
Where sales. title_id is null

B. If the subquery is not repeated, the subquery related to in and exists can be replaced by inner join. For example:
Select pub_name
From publishers
Where pub_id in
(Select pub_id
From titles
Where type = 'business ')
-- Can be rewritten:
Select distinct A. pub_name
From publishers a inner join titles B
On B. type = 'business' and
A. pub_id = B. pub_id

C. Use exists instead of related subqueries of in, such
Select pub_name
From publishers
Where pub_id in
(Select pub_id
From titles
Where type = 'business ')
-- The following statement can be used instead:
Select pub_name
From publishers
Where exists
(Select 1
From titles
Where type = 'business' and
Pub_id = publishers. pub_id)

D. Do not use the count (*) subquery to determine whether a record exists. It is best to use left join or exists. For example, someone writes a statement like this:

Select job_desc from jobs
Where (select count (*) from employee where job_id = jobs. job_id) = 0
-- Should be changed:
Select jobs. job_desc from jobs left join employee
On employee. job_id = jobs. job_id
Where employee. emp_id is null

Select job_desc from jobs
Where (select count (*) from employee where job_id = jobs. job_id) <> 0
-- Should be changed:
Select job_desc from jobs
Where exists (select 1 from employee where job_id = jobs. job_id)

AsProgramNote:
1. Pay attention to the data volume of each table.
2. Try to test the Encoding Process and unit test process in a database with a large amount of data. It is best to test the actual data.
3. Every SQL statement should be as simple as possible
4. Do not update the data of tables with triggers frequently.
5. Pay attention to the limitations of database functions and their performance

1. subquery usage
A subquery is a SELECT query nested in select, insert, update, and delete
Statement or other subqueries. Subqueries can be used wherever expressions are allowed. Subqueries allow for flexible programming and can be used to implement some special functions. However, in terms of performance
Improper subquery usage may cause a performance bottleneck.
For subqueries related to a, not in, and not exists, you can use left join instead. For example:
Select pub_name
From publishers
Where pub_id not in
(Select pub_id
From titles
Where type = 'business ')
-- Can be rewritten:
Select a. pub_name
From publishers a left join titles B
On B. type = 'business' and
A. pub_id = B. pub_id
Where B. pub_id is null

Select title
From titles
Where not exists
(Select title_id
From sales
Where title_id = titles. title_id)
-- Can be rewritten:
Select title
From titles left join sales
On sales. title_id = titles. title_id
Where sales. title_id is null

B. If the subquery is not repeated, the subquery related to in and exists can be replaced by inner join. For example:
Select pub_name
From publishers
Where pub_id in
(Select pub_id
From titles
Where type = 'business ')
-- Can be rewritten:
Select distinct A. pub_name
From publishers a inner join titles B
On B. type = 'business' and
A. pub_id = B. pub_id

C. Use exists instead of related subqueries of in, such
Select pub_name
From publishers
Where pub_id in
(Select pub_id
From titles
Where type = 'business ')
-- The following statement can be used instead:
Select pub_name
From publishers
Where exists
(Select 1
From titles
Where type = 'business' and
Pub_id = publishers. pub_id)

D. Do not use the count (*) subquery to determine whether a record exists. It is best to use left join or exists. For example, someone writes a statement like this:

Select job_desc from jobs
Where (select count (*) from employee where job_id = jobs. job_id) = 0
-- Should be changed:
Select jobs. job_desc from jobs left join employee
On employee. job_id = jobs. job_id
Where employee. emp_id is null

Select job_desc from jobs
Where (select count (*) from employee where job_id = jobs. job_id) <> 0
-- Should be changed:
Select job_desc from jobs
Where exists (select 1 from employee where job_id = jobs. job_id)

As a programmer, you should also note:
1. Pay attention to the data volume of each table.
2. Try to test the Encoding Process and unit test process in a database with a large amount of data. It is best to test the actual data.
3. Every SQL statement should be as simple as possible
4. Do not update the data of tables with triggers frequently.
5. Pay attention to the limitations of database functions and their performance