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

#本人产线打杂，基础不好，工作需要写了一些语句对应重复工作，但是如果执行语句影响到服务器性能我还是手动删除吧
#在数据库中对应删除部分条码，有多个数据库多个表，使用语句删除时发现某些表删除执行的时间非常久，耗费了系统资源
#我想贴出我的语句请教如何提高语句的执行效率
#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

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 这三个字段
望采纳

你需要逐步排查具体是语句哪里慢然后再具体修改优化

三个条件语句里的虚拟表查语句单独拿出来执行是否慢。虚拟表里面的数据量可能多也可能少优化重点
in这个写法可以尝试用 exists 替换试试
delete XResults_xxICT2 。。。。换成SELECT TOP 100 XResults_xxICT2。。。。。这样限制数量能不能查出来
如果第三点限制查询指定数量很快执行可以分批次清理数据每次清理指定数量

尽量少使用in函数，比较影响性能，可以换成如下写法试试

delete XResults_xxICT2 #result inner join #O on #O.PcsBarcode = #xx.PcsBarcode 
inner join #route on #route.RouteId = #xx.RouteId 
inner join #B on #B.LotNo = #xx.LotNo

少用in函数，尽量不用子查询，可以考虑关联字段建立索引。

建议有外键关联的字段建立索引，XBarcode 字段建立索引。