SQL Server Performance Optimization-active temporary tables

Continue tuning. This morning I analyzed the SQL statement with the next processing time of 40 seconds.
Select *
From table
Where t_table_id in
(
Select distinct S. t_table_id
From
(
Select distinct A. t_table_id, A. BT
From
(Select left (BT, 4) as bbt, * from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0),
(Select distinct left (BT, 4) as bbt, t_table_id from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0) B
Where B. bbt like A. bbt and A. t_table_id <> B. t_table_id
And a. bbt not like '% AAAA %' and A. bbt not like '% BBBB %' and A. bbt not like '% CCCC %'
And a. bbt not like '% dddd %' and A. bbt not like '% eeee %' and A. bbt not like '% FFFF %'
And a. bbt not like '% AAAA %' and A. bbt not like '% BBBB %' and A. bbt not like '% CCCC %'
And a. bbt not like '% dddd %' and A. bbt not like '% eeee %' and A. bbt not like '% FFFF %'
-- Order by A. BT
Union all
Select distinct A. t_table_id, A. BT
From
(Select right (BT, 5) as bbt, * from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0),
(Select distinct right (BT, 5) as bbt, t_table_id from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0) B
Where B. bbt like A. bbt and A. t_table_id <> B. t_table_id
And a. bbt not like '% AAAA %' and A. bbt not like '% BBBB %' and A. bbt not like '% CCCC %'
And a. bbt not like '% dddd %' and A. bbt not like '% eeee %' and A. bbt not like '% FFFF %'
And a. bbt not like '% AAAA %' and A. bbt not like '% BBBB %' and A. bbt not like '% CCCC %'
And a. bbt not like '% dddd %' and A. bbt not like '% eeee %' and A. bbt not like '% FFFF %'
And a. bbt not like '%' + (select right (convert (varchar (10), getdate ()-1, 20), 2) + '%'
And B. bbt not like '%' + (select right (convert (varchar (10), getdate ()-1, 20), 2) + '%'
) S
) Order by Bt
It can be considered as a repeated operation on the same table, and the statement contains too many full table scans.
I personally think that the graphical interface is good for SQL Server's execution plan, but sometimes it is not intuitive for the quantified I/O, CPU, and cost output. In addition, the execution plan like this SQL statement, it is estimated that the entire Screen of 1600*1200 cannot be displayed. It can be considered that the screen is basically invisible.

The SQL statement can only be divided into several small SQL statements to gradually locate the bottleneck, for example
Select left (BT, 4) as bbt, * from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0
Select distinct left (BT, 4) as bbt, t_table_id from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0
The execution of these two statements is very fast and the result set is relatively small, but the merge of the two statements and the addition of related conditions is very slow.
Directly construct two temporary tables, all of which are full table scans and join each other with two temporary tables. After the test, it takes only one second.
Build the following two SQL temporary tables and perform the same test.
Finally, all of them are combined for testing and found to be 2 ~ 3 seconds
In fact, we can optimize the construction of some temporary tables, but if the results are met, we are reluctant to try again.

I tried to use CTE, but it seems that the effect is not good.
The following is an optimized SQL example.
/*
With temp1
(Select left (BT, 4) as bbt, * from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0 ),
Temp2
(Select distinct left (BT, 4) as bbt, t_table_id from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0 ),
Temp3
(Select left (BT, 5) as bbt, * from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0 ),
Temp4
(Select distinct left (BT, 5) as bbt, t_table_id from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0)
*/
Print convert (varchar, getdate (), 9)
Select left (BT, 4) as bbt, * into # temp1 from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0
Select distinct left (BT, 4) as bbt, t_table_id into # temp2 from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0
Select right (BT, 5) as bbt, * into # temp3 from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0
Select distinct right (BT, 5) as bbt, t_table_id into # temp4 from table where FSRQ> getdate ()-1 and gkbz = 1 and scbz = 0
Select
(Select MS from xtclb where dm = lmxz and lb in (130,131) as 'column select ',
BT, mtly, czy
From table
Where t_table_id in
(
Select distinct S. t_table_id
From
(
Select distinct A. t_table_id, A. BT
From
# Temp1,
# Temp2 B
Where B. bbt like A. bbt and A. t_table_id <> B. t_table_id
And a. bbt not in ('aaa', 'bbbbbb', 'cccccc', 'ddddd', 'eee', 'ffffff ')
And B. bbt not in ('aaa', 'bbbbbb', 'cccccc', 'ddddd', 'eee', 'ffffff ')
Union all
Select distinct A. t_table_id, A. BT
From
# Temp3,
# Temp4 B
Where B. bbt like A. bbt and A. t_table_id <> B. t_table_id
And a. bbt not like '% AAAA %' and A. bbt not like '% BBBB %' and A. bbt not like '% CCCC %'
And a. bbt not like '% dddd %' and A. bbt not like '% eeee %' and A. bbt not like '% FFFF %'
And a. bbt not like '%' + (select right (convert (varchar (10), getdate ()-1, 20), 2) + '%'
And a. bbt not like '% AAAA %' and A. bbt not like '% BBBB %' and A. bbt not like '% CCCC %'
And a. bbt not like '% dddd %' and A. bbt not like '% eeee %' and A. bbt not like '% FFFF %'
And B. bbt not like '%' + (select right (convert (varchar (10), getdate ()-1, 20), 2) + '%'
) S
) Order by Bt
-- Option (loop join );
-- 34
Print convert (varchar, getdate (), 9)
/*
Drop table # temp1
Drop table # temp2
Drop table # temp3
Drop table # temp4
*/