難題:同樣的查詢,配制低的服務器反而速度快,怎麼辦?
1、Win2000+SQL2000(4CPU+2G內存 20萬HP服務器)
select part_no into #temp From dbo.sh_outin_histoy where date>'2006-04-03' and date<'2006-04-04' and d_class='調撥'
select * from sh_outin_histoy where part_no in (select part_no from #temp)
and d_class in ('領用','補料') and date>'2006-04-03' and date<'2006-04-04'
時間是:0秒
select * from sh_outin_histoy a where part_no in (select part_no From dbo.sh_outin_histoy b where date>'2006-04-03' and date<'2006-04-04' and d_class='調撥' )
and d_class in ('領用','補料') and date>'2006-04-03' and date<'2006-04-04'
時間是:3分53秒
2、NT+SQL2000(2CPU+1G內存 5萬HP服務器)
select part_no into #temp From dbo.sh_outin_histoy where date>'2006-04-03' and date<'2006-04-04' and d_class='調撥'
select * from sh_outin_histoy where part_no in (select part_no from #temp)
and d_class in ('領用','補料') and date>'2006-04-03' and date<'2006-04-04'
時間是:0秒
select * from sh_outin_histoy a where part_no in (select part_no From dbo.sh_outin_histoy b where date>'2006-04-03' and date<'2006-04-04' and d_class='調撥' )
and d_class in ('領用','補料') and date>'2006-04-03' and date<'2006-04-04'
時間是:0秒
為什麼查詢有這麼大的差別呢?
查了幾次都這樣。