join 效率问题。我要比较2张表里面的某个字段比如:cartonID,A表有一列,B表有1列。当然要完全配对才可以。数据表数据在500w上下。
--DIFFERENCE(aa.cartoonid,bb.carton_id)
declare @container_number varchar(20)
set @container_number='HJCU1409491'
--left join 看Nike_factory有的carton 是不是全部在Tbl_BENIKE2_STUFFING有
select * from(select a.so,c.cartoonid from dbo.Nike_factory a,Nike_factory_line b,Nike_factory_cartoon c where b.facid=a.id and b.id=c.lineid and b.container=@container_number and b.tag='P' ) aa left join
(select carton_id from Tbl_BENIKE2_STUFFING where container_number=@container_number and status='P') bb on aa.cartoonid=bb.carton_id;
--right join 看Tbl_BENIKE2_STUFFING 有的是不是全部在Nike_factory里面
select carton_id,cartoonid from(select c.cartoonid from dbo.Nike_factory a,Nike_factory_line b,Nike_factory_cartoon c where b.facid=a.id and b.id=c.lineid and b.container=@container_number and b.tag='P' ) aa right join
(select carton_id,shipping_order from Tbl_BENIKE2_STUFFING where container_number=@container_number and status='P') bb on aa.cartoonid=bb.carton_id
我做分析计划。发现left耗大概4%的资源这个还不是很慢。
我测试结果3s能执行完成这个批处理。
请大家看看。这样是否妥当。谢谢