SQL Server optimizations alternatives to in and not

However, in SQL performance is always relatively low, the steps to perform from SQL have the following differences in SQL with no in sql:
SQL attempts to convert the connection to multiple tables, and if the conversion is unsuccessful, execute the subquery in inside, then query the outer table record, and query the connection of multiple tables directly if the conversion succeeds. This shows that in the SQL at least one more conversion process. Normal SQL can be converted successfully, but for SQL that contains packet statistics, it cannot be converted. It is recommended to try not to use the in operator in business-intensive SQL
Not in this operation is strongly column recommended not used because it cannot apply the index of the table. It is recommended that the not EXISTS or (outer join + null-judged) scheme be used instead
There are two tables in the database, one is the current table info (id,pname,remark,impdate,upstate) and one is the Backup data table Bakinfo (id,pname,remark,impdate,upstate), To back up the current table data to a backup table, it involves not in and in operations:
First, add 100,000 test data
Copy CodeThe code is as follows:
CREATE PROCEDURE AddData
As
DECLARE @id int
Set @id =0
while (@id <100000)
Begin
INSERT INTO dbo. Info (Id,pname,remark,impdate,upstate)
VALUES (@id, convert (varchar, @id) + ' 0 ', ' abc ', GETDATE (), 0)
Set @id = @id +1
End
EXEC AddData

Using not in and in operation:
Copy CodeThe code is as follows:
SET STATISTICS time on
Go
--Backup data
Insert into Bakinfo (id,pname,remark,impdate,upstate)
Select Id,pname,remark,impdate,upstate from dbo. Info
where ID not in (SELECT ID from Dbo.bakinfo)
Go
SET STATISTICS Time off

The execution time of this operation:
Copy CodeThe code is as follows:
SQL Server profiling and compilation time:
CPU time = 0 milliseconds, elapsed time = 3 milliseconds.
SQL Server Execution Time:
CPU time = 453 milliseconds, elapsed time = 43045 milliseconds.
(100000 rows affected)
SQL Server profiling and compilation time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.
--Change the current table state
Update Info set upstate=1 where ID in (select ID from Dbo.bakinfo)

The execution time of this operation:
Copy CodeThe code is as follows:
SQL Server profiling and compilation time:
CPU time = 62 milliseconds, elapsed time = 79 milliseconds.
SQL Server Execution Time:
CPU time = 188 milliseconds, elapsed time = 318 milliseconds.
(100000 rows affected)
SQL Server profiling and compilation time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.
--Delete Current table data
Delete from Info where upstate=1 and IDs in (select IDs from Dbo.bakinfo)

The execution time of this operation:
Copy CodeThe code is as follows:
SQL Server profiling and compilation time:
CPU time = 183 milliseconds, elapsed time = 183 milliseconds.
SQL Server Execution Time:
CPU time = 187 milliseconds, elapsed time = 1506 milliseconds.
(100000 rows affected)
SQL Server profiling and compilation time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

To use the Join connection substitution scheme:
Copy CodeThe code is as follows:
SET STATISTICS time on
Go
--Backup data
Insert into Bakinfo (id,pname,remark,impdate,upstate)
Select Id,pname,remark,impdate,upstate from
(SELECT info.id,info.pname, Info.remark, Info.impdate,info.upstate, bakinfo.id as Bakid
From Info left JOIN
Bakinfo on info.id = bakinfo.id) as T
Where T.bakid is null and t.upstate=0
Go
SET STATISTICS time off;

The execution time of this operation:
Copy CodeThe code is as follows:
SQL Server profiling and compilation time:
CPU time = 247 milliseconds, elapsed time = 247 milliseconds.
SQL Server Execution Time:
CPU time = 406 milliseconds, elapsed time = 475 milliseconds.
(100000 rows affected)
SQL Server profiling and compilation time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.
--Change the current table state
Update Info Set upstate=1
From Info INNER JOIN
Bakinfo on info.id = bakinfo.id

The execution time of this operation:
Copy CodeThe code is as follows:
SQL Server profiling and compilation time:
CPU time = 4 milliseconds, elapsed time = 4 milliseconds.
SQL Server Execution Time:
CPU time = 219 milliseconds, elapsed time = 259 milliseconds.
(100000 rows affected)
SQL Server profiling and compilation time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

--Delete Current table data
Copy CodeThe code is as follows:
Delete from Info
From Info INNER JOIN
Bakinfo on info.id = bakinfo.id
where info.upstate=1

The execution time of this operation:
Copy CodeThe code is as follows:
SQL Server profiling and compilation time:
CPU time = 177 milliseconds, elapsed time = 177 milliseconds.
SQL Server Execution Time:
CPU time = 219 milliseconds, elapsed time = 550 milliseconds.
(100000 rows affected)
SQL Server profiling and compilation time:
CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

You can see that using a join scheme is a lot shorter than using in and in execution time