optimization of one SQL statement (SQL Server)

The earliest notation:

 withT as(SELECT  Case  whenCol1 is NULL ORCol1=N"'  ThenCol2ElseCol1End  asCode, Case  whenCol1 is NULL ORCol1=N"'  Then 1 Else 0 End  asFlag fromYMWHEREcol_076between '2018-07-25'  and '2018-08-03'  andcol_478=N'xx'  andcol_346 likeN'%dd%'), D as(SELECTCode,province,city fromAdds)SELECTProvince asProvince, City asCityCOUNT(1) Number of votes from (SELECTa.dr_250 asprovince,a.dr_251 asCity fromTINNER JOINTB asA ona.dr_203=T.codeWHERET.flag=0 UNION  All SELECTD.province,d.city fromTINNER JOIND onD.code=T.codeWHERET.flag=1 UNION  All SELECT "'  asProvince,"'  asCity fromTWHERECode is NULL ORCode=N"') asSGROUP  byprovince,city;

The latest wording:

SELECT  Case  whenCol1 is NULL ORCol1=N"'  ThenCol2ElseCol1End  asCode, Case  whenCol1 is NULL ORCol1=N"'  Then 1 Else 0 End  asFlag into#T fromYMWHEREcol_076between '2018-07-25'  and '2018-08-03'  andcol_478=N'xx'  andcol_346 likeN'%dd%';SELECTCode,province,city into#D fromAdds;SELECTProvince asProvince, City asCityCOUNT(1) Number of votes from (SELECTa.dr_250 asprovince,a.dr_251 asCity from#TINNER JOINTB asA ona.dr_203=#T. CodeWHERE#T. Flag=0UNION  AllSELECT#D. Province, #D. City from#TINNER JOIN#D on#D. Code=#T. CodeWHERE#T. Flag=1UNION  AllSELECT "'  asProvince,"'  asCity from#TWHERECode is NULL ORCode=N"') asSGROUP  byprovince,city;DROP Table#T;DROP Table#D;

The new wording is much higher than the original writing performance (the original statement execution time out), the biggest reason is the understanding of the WITH statement is wrong!!!

optimization of one SQL statement (SQL Server)