第一种写法就快很多,但我不想分开来写,第二种就超级慢,都是同样的写法,为什么区别这么大,
高手们,怎么样优化下面的写法,谢谢!
1,IF object_id('tempdb..#tmp1') is not null
drop table #tmp1
select INVENTBATCHID, ITEMID, PRODDATE, KTL_GOLDCOST, KTL_STONECOST,KTL_ACCCOST, KTL_LABOURCOST,
DESCRIPTION, PRODORDERQTY, KTL_PRODID,RECID,
(select top 1 ITEMID+
(case THK_INVENTD2ID when '' then THK_INVENTD2ID else '-'+THK_INVENTD2ID end)
+(case THK_INVENTD3ID when '' then THK_INVENTD3ID else '-'+THK_INVENTD3ID end)
+(case THK_INVENTD4ID when '' then THK_INVENTD4ID else '-'+THK_INVENTD4ID end)
+(case THK_INVENTDGOLDTYPEID when '' then THK_INVENTDGOLDTYPEID else '-'+THK_INVENTDGOLDTYPEID end)
from BOMVERSION
where BOMID = THK_RefBOMId and ITEMID <>'') as PRODUCTID
into #tmp1 from inventbatch as y
where left(inventbatchid,1) = 'P'SELECT * FROM #tmp1 y
where not exists(select 1 from #tmp1
where y.INVENTBATCHID=INVENTBATCHID and RECID>y.RECID)
2,select INVENTBATCHID, ITEMID, PRODDATE, KTL_GOLDCOST, KTL_STONECOST,KTL_ACCCOST, KTL_LABOURCOST,
DESCRIPTION, PRODORDERQTY, KTL_PRODID,
(select top 1 ITEMID+
(case THK_INVENTD2ID when '' then THK_INVENTD2ID else '-'+THK_INVENTD2ID end)
+(case THK_INVENTD3ID when '' then THK_INVENTD3ID else '-'+THK_INVENTD3ID end)
+(case THK_INVENTD4ID when '' then THK_INVENTD4ID else '-'+THK_INVENTD4ID end)
+(case THK_INVENTDGOLDTYPEID when '' then THK_INVENTDGOLDTYPEID else '-'+THK_INVENTDGOLDTYPEID end)
from BOMVERSION
where BOMID = THK_RefBOMId and ITEMID <>'') as PRODUCTID into #tmp8 from INVENTBATCH as y
where not exists(select 1 from INVENTBATCH
where y.INVENTBATCHID=INVENTBATCHID and RECID>y.RECID)
select INVENTBATCHID, ITEMID, PRODDATE, KTL_GOLDCOST, KTL_STONECOST,KTL_ACCCOST, KTL_LABOURCOST,
DESCRIPTION, PRODORDERQTY, KTL_PRODID,RECID,
(select top 1 ITEMID+
(case THK_INVENTD2ID when '' then THK_INVENTD2ID else '-'+THK_INVENTD2ID end)
+(case THK_INVENTD3ID when '' then THK_INVENTD3ID else '-'+THK_INVENTD3ID end)
+(case THK_INVENTD4ID when '' then THK_INVENTD4ID else '-'+THK_INVENTD4ID end)
+(case THK_INVENTDGOLDTYPEID when '' then THK_INVENTDGOLDTYPEID else '-'+THK_INVENTDGOLDTYPEID end)
from BOMVERSION
where BOMID = THK_RefBOMId and ITEMID <>'') as PRODUCTID
from inventbatch as y
where left(inventbatchid,1) = 'P')
SELECT * FROM t1 y
where not exists(select 1 from t1
where y.INVENTBATCHID=INVENTBATCHID and RECID>y.RECID)
sql server 2000的话分开写吧。第一种是筛选出来的结果匹配筛选出来结果,第二种是筛选出来结果匹配未筛选结果,所以慢。更新下统计信息看看吧,还有检查下索引.
select INVENTBATCHID, ITEMID, PRODDATE, KTL_GOLDCOST, KTL_STONECOST,KTL_ACCCOST, KTL_LABOURCOST,
DESCRIPTION, PRODORDERQTY, KTL_PRODID,RECID,
(select top 1 ITEMID+
(case THK_INVENTD2ID when '' then THK_INVENTD2ID else '-'+THK_INVENTD2ID end)
+(case THK_INVENTD3ID when '' then THK_INVENTD3ID else '-'+THK_INVENTD3ID end)
+(case THK_INVENTD4ID when '' then THK_INVENTD4ID else '-'+THK_INVENTD4ID end)
+(case THK_INVENTDGOLDTYPEID when '' then THK_INVENTDGOLDTYPEID else '-'+THK_INVENTDGOLDTYPEID end)
from BOMVERSION
where BOMID = THK_RefBOMId and ITEMID <>'') as PRODUCTID
from inventbatch as y
where left(inventbatchid,1) = 'P')
SELECT * FROM t1 y
where not exists(select 1 from t1
where y.INVENTBATCHID=INVENTBATCHID and RECID>y.RECID)
option(hash join)这样呢?