如何在sql server中只使用一个表排除记录查看我的查询优化

hzbexzde 于 2022-12-17 发布在 SQL Server

关注(0)|答案(1)|浏览(129)

declare
@copyFromId bigint =234
,@copyToId bigint =229
as
\-----------New Working ------------

\--first temp table start--
select
FareId, FromZoneId,ToZoneId,Price,CompanyRate
into #temp1
from
Fare_ZoneWisePricing
where
FareId =@copyFromId
\--End first temp table --
\--start second temp table--
select
FareId, FromZoneId,ToZoneId,Price,CompanyRate
into #temp2
from
Fare_ZoneWisePricing
where FareId=@copyToId
\--End second temp table--

\---------------Main Operation start--------------------
insert into Fare_ZoneWisePricing(fareid,fromzoneid,tozoneid,price,companyrate)
SELECT  
@copyToId
,FromZoneId
,ToZoneId
,Price
,CompanyRate  
FROM
\#temp1 a
WHERE not exists (
SELECT
FromZoneId
,ToZoneId
,Price
,CompanyRate
FROM
\#temp2 b
where
a.FromZoneId =b.FromZoneId and a.ToZoneId =b.ToZoneId
GROUP BY
FromZoneId
,ToZoneId
,Price
,CompanyRate
)

\------------------End Main Operation---------------------
drop table #temp1
drop table #temp2

declare 
@copyFromId bigint =234
,@copyToId bigint =229
as
-----------New Working ------------

--first temp table start--
select 
    FareId, FromZoneId,ToZoneId,Price,CompanyRate 
    into #temp1 
from 
    Fare_ZoneWisePricing 
where 
    FareId =@copyFromId
--End first temp table --
--start second temp table--
select 
    FareId, FromZoneId,ToZoneId,Price,CompanyRate 
    into #temp2 
from 
    Fare_ZoneWisePricing 
where FareId=@copyToId
--End second temp table--

---------------Main Operation start--------------------
insert into Fare_ZoneWisePricing(fareid,fromzoneid,tozoneid,price,companyrate)
SELECT  
    @copyToId
   ,FromZoneId
   ,ToZoneId
   ,Price
   ,CompanyRate  
FROM 
    #temp1 a 
WHERE not exists (
                    SELECT 
                         FromZoneId
                        ,ToZoneId
                        ,Price
                        ,CompanyRate 
                    FROM 
                        #temp2 b 
                    where 
                        a.FromZoneId =b.FromZoneId and a.ToZoneId =b.ToZoneId 
                    GROUP BY 
                        FromZoneId
                       ,ToZoneId
                       ,Price
                       ,CompanyRate
                )

------------------End Main Operation---------------------
drop table #temp1
drop table #temp2

sql-server

来源：https://stackoverflow.com/questions/74811414/how-to-exclude-record-in-sql-server-using-only-one-table-check-my-query-optimiza

1条答案

按热度按时间

6ie5vjzr1#

不需要EXISTS (SELECT x, y, z FROM table WHERE correlation GROUP BY x, y, z)，EXISTS（）只需要有任何一行匹配相关性，因此您只需要EXISTS (SELECT * FROM table WHERE correlation)。
将所有内容都插入临时表中（只是内联查询）也可能没有什么好处 （事实上，会有很大的开销）。
这意味着您的脚本可以简化为...

DECLARE
  @copyFromId BIGINT = 234
 ,@copyToId   BIGINT = 229

---------------Main Operation start--------------------
INSERT INTO
  Fare_ZoneWisePricing (
    fareid, fromzoneid, tozoneid, price, companyrate
  )
SELECT  
    @copyToId
   ,copyFrom.FromZoneId
   ,copyFrom.ToZoneId
   ,copyFrom.Price
   ,copyFrom.CompanyRate  
FROM
    Fare_ZoneWisePricing   AS copyFrom
WHERE
      FareId = @copyFromId
  AND NOT EXISTS (
            SELECT *
              FROM Fare_ZoneWisePricing  AS copyTo
             WHERE copyTo.FareId     = @copyToId
               AND copyTo.FromZoneId = copyFrom.FromZoneId
               AND copyTo.ToZoneId   = copyFrom.ToZoneId 
          )

下一个优化是确保(FareId, FromZoneId, ToZoneId)上有索引。

赞(0）回复(0）举报 2022-12-17

我来回答

如何在sql server中只使用一个表排除记录查看我的查询优化

1条答案

相关问题

热门标签

最新问答