SQL 查询问题，立即解决立即给分

[caryChen2012]

第一种写法就快很多，但我不想分开来写，第二种就超级慢，都是同样的写法，为什么区别这么大，
高手们，怎么样优化下面的写法，谢谢！
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)

[lg314]

;with t1 as (
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的话分开写吧。第一种是筛选出来的结果匹配筛选出来结果，第二种是筛选出来结果匹配未筛选结果，所以慢。更新下统计信息看看吧，还有检查下索引.

[caryChen2012]

你这一个我试跑了一下，跟我的第2种写法一样慢

[快溜]

where left(inventbatchid,1) = 'P'的条件第二种不要的吗，第一种采用临时表的方式确实可以提高查询效率

[lg314]

;with t1 as (
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)

这样呢？