Write SQL details to note checklist Summary _mssql

Source: Internet
Author: User
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
--=====================================*/

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.