请教一个sqlsever2008查询/删除语句，提高我语句的执行效率

Question

#本人产线打杂，基础不好，工作需要写了一些语句对应重复工作，但是如果执行语句影响到服务器性能我还是手动删除吧
#在数据库中对应删除部分条码，有多个数据库多个表，使用语句删除时发现某些表删除执行的时间非常久，耗费了系统资源
#我想贴出我的语句请教如何提高语句的执行效率
#delete XResults_xxICT2 where PcsBarcode in(select XBarcode from #O )and RouteId in (select RouteId from #route ) and lotno in(select distinct LotNo from #B ) 此条语句执行了3分钟（这个是不行的，此表数据量是最小的，所以不明白为什么执行最久）
原语句：
select * into #O from XResults where XBarCode in(X)
go
select distinct routeid into #route from #O where RouteId in (90,100,110) --添加要删除的工位
select distinct lotno into #B from XStatus where XBarcode in(select XBarcode from #O)
go
if ((select COUNT(XBarCode ) from PackingOrderDetial where XBarcode in(select XBarcode from #O))=0)
Begin
if ( ( select COUNT(XBarcode ) from XStatus where XBarcode in(select XBarcode from #O) and Status !=0)=0)
Begin
if((select COUNT(Xbarcode) from LotReplacementRecord where XBarcode in(select XBarcode from #O))=0 )
Begin
delete XResults where XBarcode in(select XBarcode from #O )and RouteId in (select RouteId from #route ) and lotno in(select LotNo from #B)
delete XResults_RFICT where XBarcode in(select XBarcode from #O )and RouteId in (select RouteId from #route ) and lotno in(select LotNo from #B)
delete XResults_RFICT2 where XBarcode in(select XBarcode from #O )and RouteId in (select RouteId from #route ) and lotno in(select LotNo from #B)
print '已删除相应工位数据'
end
else
Begin
insert into #B (LotNo ) select distinct oldlotno from LotReplacementRecord where XBarcode in(select XBarcode from #O)

delete XResults where XBarcode in(select XBarcode from #O )and RouteId in (select RouteId from #route ) and lotno in(select distinct LotNo from #B )
delete XResults_RFICT where XBarcode in(select XBarcode from #O )and RouteId in (select RouteId from #route ) and lotno in(select distinct LotNo from #B )
delete XResults_RFICT2 where XBarcode in(select XBarcode from #O )and RouteId in (select RouteId from #route ) and lotno in(select distinct LotNo from #B )
print '已加上旧LOT删除相应工位数据'
end
end
else
begin
print '有X状态为1'
end
End
else
begin
print '有X已打票'
End

drop table #O ,#B,#route

Answer 1

declare @varRouteld int，@varBarCode varchar(100)
@varRouteld=90 //工位中的一个(90,100,110) 删完一个再删另外一个
@varBarCode="3423432143123412" //要删除的条码
select distinct lotno into #B from XStatus where XBarcode=@varBarCode
delete XResults where XBarcode=@varBarCode and RouteId=@varRouteld and lotno in (select LotNo from #B)
delete XResults_RFICT where XBarcode =@varBarCode and RouteId=@varRouteld and lotno in (select distinct LotNo from #B )
delete XResults_RFICT2 where XBarcode =@varBarCode and RouteId=@varRouteld and lotno in (select distinct LotNo from #B )
drop table #B
go

---

或者建立索引XBarcode，RouteId，lotno 这三个字段
望采纳