Copy Code code as follows:
/*
--Note: Prepare data (can be skipped, very time-consuming)
CREATE TABLE Check1_t1
(
ID INT,
C1 CHAR (8000)
)
CREATE TABLE Check1_t2
(
ID INT,
C1 CHAR (8000)
)
DECLARE @i INT
SET @i=1
While @i<=10000
BEGIN
INSERT into Check1_t1 SELECT @i, ' C1 '
INSERT into Check1_t2 SELECT 10000+@i, ' C1 '
SET @i=@i+1
End
CREATE TABLE Check2_t1
(
ID INT,
C1 CHAR (8000)
)
DECLARE @i INT
SET @i=1
While @i<=10000
BEGIN
INSERT into Check2_t1 SELECT @i, ' C1 '
SET @i=@i+1
End
INSERT into Check2_t1 VALUES (10001, ' C2 ')
INSERT into Check2_t1 VALUES (10002, ' C1 ')
CREATE TABLE Check3_t1
(
ID INT,
C1 CHAR (7000)
)
CREATE TABLE Check3_t2
(
ID INT,
C1 CHAR (7000)
)
DECLARE @i INT
SET @i=1
While @i<=20000
BEGIN
IF @i%2 =0
BEGIN
INSERT into Check3_t1 SELECT @i, ' C1 '
End
ELSE
BEGIN
INSERT into Check3_t1 SELECT @i, ' C2 '
End
IF @i%100=0
BEGIN
INSERT into Check3_t2 SELECT @i, ' C1 '
INSERT into Check3_t2 SELECT @i+50000, ' C2 '
End
SET @i=@i+1
End
CREATE TABLE Check4_t1
(
ID INT,
C1 CHAR (500),
)
DECLARE @i INT
SET @i=1
While @i<=500000
BEGIN
IF @i%100000 =0
BEGIN
INSERT into Check4_t1 SELECT @i, ' C2 '
End
ELSE
BEGIN
INSERT into Check4_t1 SELECT @i, ' C1 '
End
SET @i=@i+1
End
CREATE nonclustered INDEX ncix_c1 on check4_t1 (C1)
CREATE TABLE Check5_t1
(
ID INT,
C1 CHAR (10),
)
DECLARE @i INT
SET @i=1
While @i<=10000
BEGIN
INSERT into Check5_t1 SELECT @i, ' C1 '
IF @i%2=0
BEGIN
INSERT into Check5_t1 SELECT @i, ' C1 '
End
SET @i=@i+1
End
*/
--=====================================
--1, union ALL instead of union
DBCC dropcleanbuffers
DBCC Freeproccache
--Test One: (26s) Execution plan: Table scan-> sort-> Merge Join
SELECT id,c1 from check1_t1--1w bar data
UNION
SELECT id,c1 from Check1_t2--1w bar data
--Test Two: (4s) Execution plan: Table scan-> table Scan Series
SELECT id,c1 from check1_t1--1w bar data
UNION All
SELECT id,c1 from Check1_t2--1w bar data
--Summary: It's quite time-consuming to test the union sort and to re merge, and if not, it's best to add all
--=====================================
--2, Exists instead of Count (*)
DBCC dropcleanbuffers
DBCC Freeproccache
----Test One: (7s) Execution plan: Table scan-> flow polymerization-> compute vector
DECLARE @COUNT INT
SELECT @COUNT =count (*) from check2_t1 WHERE c1= ' C1 '--1w strip data
IF @COUNT >0
BEGIN
PRINT ' S '
End
----test two: (0s) execution plan: Constant scan/table scan-> nested loops-> COMPUTE scalar
IF EXISTS (SELECT 1 from check2_t1 WHERE c1= ' C1 ')--1w bar data
BEGIN
PRINT ' S '
End
--Summary: To determine whether there is, with exist, no need to count (*) all the records of the table, scan once
--=====================================
Alternative to--3, in (Select COL1 from Table)
DBCC dropcleanbuffers
DBCC Freeproccache
--Test One: (3s) Execution plan: Table scan-> Hash Match
SELECT id,c1 from Check3_t2--400 line
where ID in (SELECT ID from check3_t1 WHERE c1= ' C1 ')--2w rows
--Test two: (1s) execution plan: Table scan-> parallelism-> bitmap-> sort-> Merge Join-> degree of parallelism
SELECT a.id,a.c1 from Check3_t2 A
INNER JOIN check3_t1 B on a.id=b.id WHERE b.c1= ' C1 '
--Test three: (3s) Execution plan: Table scan-> Hash Match
SELECT a.id,a.c1 from Check3_t2 A
where EXISTS (SELECT 1 from Check3_t1 B where b.id=a.id and b.c1= ' C1 ')
--Summary: can use inner JOIN as much as possible, SQL Server in the query will optimize the associated table
--=====================================
--4, not Exists instead of in
--Test One: (8s) Execution plan: Table scan-> nested loops-> Hash Match
SELECT id,c1 from Check3_t1--2w line
where ID not in (SELECT ID from check3_t2 WHERE c1= ' C1 ')--400 rows
--Test Two: (4s) Execution plan: Table scan-> Hash Match
SELECT a.id,a.c1 from Check3_t1 A
Where not EXISTS (SELECT 1 from Check3_t2 B where b.id=a.id and b.c1= ' C1 ')
--Summary: Try not to use not in because it calls a nested loop, we recommend using not exists instead of not in
--=====================================
--5, avoid using any function on the condition column
DROP TABLE Check4_t1
CREATE nonclustered index ncix_c1 on CHECK4_T1 (C1)--plus nonclustered indexes
---test one: (4s) execution plan: Index scan
SELECT * from Check4_t1 WHERE RTRIM (C1) = ' C2 '
---test two: (0s) execution plan: Index Lookup
SELECT * from Check4_t1 WHERE c1= ' C2 '
--Summary: The Where condition uses the function to the index field, causes the index lookup to become the index scan, thus the query efficiency drops greatly
--=====================================
--6, execute dynamic SQL with sp_executesql
DBCC dropcleanbuffers
DBCC Freeproccache
CREATE PROC Up_check5_t1 (
@ID INT
)
As
SET NOCOUNT on
DECLARE @count INT,
@sql NVARCHAR (4000)
SET @sql = ' SELECT @count =count (*) from check5_t1 WHERE ID = @ID '
EXEC sp_executesql @sql,
N ' @count int OUTPUT, @ID int ',
@count OUTPUT,
@ID
PRINT @count
CREATE PROC Up_check5_t2 (
@ID INT
)
As
SET NOCOUNT on
DECLARE @sql NVARCHAR (4000)
SET @sql = ' DECLARE @count INT; SELECT @count =count (*) from check5_t1 WHERE ID = ' + CAST (@ID as VARCHAR (10)) + '; PRINT @count '
EXEC (@sql)
---test one: instantaneous
DECLARE @n INT
SET @n=1
While @n<=1000
BEGIN
EXEC up_check5_t1 @n
SET @n=@n+1
End
---test two: 2s
DECLARE @n INT
SET @n=1
While @n<=1000
BEGIN
EXEC Up_check5_t2 @n
SET @n=@n+1
End
CREATE CLUSTERED INDEX cix_id on Check5_t1 (ID)
DBCC dropcleanbuffers
DBCC Freeproccache
--View Cache schedule
SELECT a.size_in_bytes ' bytes occupied ',
Total_elapsed_time/execution_count ' Average Time ',
Total_logical_reads/execution_count ' Logical reading ',
Usecounts ' reuse Times ',
SUBSTRING (D.text, (STATEMENT_START_OFFSET/2) + 1, (case statement_end_offset
WHEN-1 THEN datalength (text)
ELSE Statement_end_offset
End-statement_start_offset)/2) + 1) ' statement '
From Sys.dm_exec_cached_plans A
CROSS apply Sys.dm_exec_query_plan (A.plan_handle) C,
Sys.dm_exec_query_stats b
CROSS apply Sys.dm_exec_sql_text (b.sql_handle) d
WHERE A.plan_handle = B.plan_handle
Order by Total_elapsed_time/execution_count DESC;
--Summary: Through the implementation of the following cache plan can be seen, the first full use of the cache plan, the query achieved very good results;
And the second one wastes the cache plan, which causes the cache to be filled up quickly, which is rather undesirable.
--=====================================
Substitution method for--7 and left Join
--Test an Execution plan: Table scan-> Hash Match
SELECT a.id,a.c1 from Check3_t1 A--2w Line
Left JOIN check3_t2 B on a.id=b.id WHERE b.c1= ' C1 '--400 line
--Test two Execution plan: Table scan-> Hash Match
SELECT a.id,a.c1 from Check3_t1 A
Right JOIN check3_t2 B on a.id=b.id WHERE a.c1= ' C1 '
--Test three Execution plan: Table scan-> Hash Match
SELECT a.id,a.c1 from Check3_t1 A
INNER JOIN check3_t2 B on a.id=b.id WHERE b.c1= ' C1 '
--Summary: Three statements, in the execution plan exactly the same, are the inner join plan,
-Because in test one and test two, where statements contain fields from the left and right tables, SQL Server will automatically follow inner join if it finds a field with this table
Supplementary test: (1s) execution plan: Table scan-> parallelism-> bitmap-> sort-> Merge Join-> parallelism
SELECT a.id,a.c1 from Check3_t2 A--400 Line
INNER JOIN check3_t1 B on a.id=b.id WHERE a.c1= ' C1 '--2w line
--Summary: Here is a more interesting place, if the main table and the correlation table data is very different, go to the execution plan to go the other way
--=====================================
--8, ON (A.id=b.id and A.tag=3)
--Test A
SELECT a.id,a.c1 from Check3_t1 A
INNER JOIN check3_t2 B on a.id=b.id and a.c1= ' C1 '
--Test Two
SELECT a.id,a.c1 from Check3_t1 A
INNER JOIN check3_t2 B on a.id=b.id WHERE a.c1= ' C1 '
--Summary: Inner joins: Filter criteria for both the left and right tables can be placed in the WHERE clause
--Test A
SELECT a.id,a.c1,b.c1 from Check3_t1 A
Left JOIN check3_t2 B on a.id=b.id and b.c1= ' C1 '
--Test Two
SELECT a.id,a.c1,b.c1 from Check3_t1 A
Left JOIN check3_t2 B on a.id=b.id WHERE b.c1= ' C1 '
--Summary: Left OUTER join: When the filter condition in the right table is placed in the ON clause and the result after the WHERE clause is not the same
--=====================================
--9, assigning to variables, plus top 1
--Test One: (3s) Execution plan: Table scan
DECLARE @ID INT
SELECT @ID =id from check1_t1 WHERE c1= ' C1 '
SELECT @ID
--Test two: (0s) execution plan: Table scan-> first few lines
DECLARE @ID INT
SELECT top 1 @ID =id from check1_t1 WHERE c1= ' C1 '
SELECT @ID
--Summary: To assign a value to a variable is best to add top 1, one from the query efficiency enhancement, two for accuracy, if the table check1_t1 have multiple values, will take the last record assigned to @id
--=====================================
--10, consider whether it is appropriate to use case statements
DECLARE @S Int=1
SELECT * from Check5_t1
WHERE c1= (case @S if 1 THEN C1 ELSE ' C2 ' end)
SELECT * from Check5_t1
WHERE @s=1 OR c1= ' C2 '
/*--=====================================
, check if the statement requires distinct. Execution plan: Table scan-> hash matching-> parallelism-> ordering
SELECT distinct C1 from CHECK3_T1
, disable select *, specify a specific column name
Select C1 from CHECK4_T1
SELECT * FROM Check4_t1
, Insert into Table (*), specify a specific column name
, Isnull, do not need to use Isnull for the field, also can produce the problem that cannot effectively use the index,
and avoid the same principle of using functions on a filtered column.
, nested subqueries, and query conditions to ensure that the subquery has the smallest result set
--=====================================*/
