請教各位先進, 在MSSQL查詢時發現在查詢條件時, 給予參數的方式不同, 效率也不同, 是否可告知為什麼? 可有方法改善? 範例如下
Declare @btime char(12),
@etime char(12),
@sdate char(8)
Set @btime = '201501030800'
Set @etime = '201501040800'
Set @sdate = '20150103'
Select @sdate sdate, f.mac_no, f.ser_no, p.emp_no, f.mac_pno, r.ret_no, f.fal_weigh
From FallData f with(nolock)
Left Join PackDetail p with(nolock) on (f.mac_no = p.mac_no and f.fal_ftime = p.fal_ftime)
Left Join fcheck c with(nolock) on (f.mac_no = c.mac_no and f.ser_no = c.ser_no)
Left Join retditem r with(nolock) on (f.fal_ftime = r.fal_ftime)
Where (f.fal_ftime >= @btime and f.fal_ftime < @etime) <---------------此處是以參數方式, 查詢的速度較慢
And (c.mac_no is not null and c.ser_no is not null and c.ser_no <> '' and c.emp_no1 is not null and c.emp_no1 <> '')
And (p.mac_no is not null and p.ser_no is not null and p.ser_no <> '')
------------------------------------------
Declare @btime char(12),
@etime char(12),
@sdate char(8)
Set @btime = '201501030800'
Set @etime = '201501040800'
Set @sdate = '20150103'
Select @sdate sdate, f.mac_no, f.ser_no, p.emp_no, f.mac_pno, r.ret_no, f.fal_weigh
From FallData f with(nolock)
Left Join PackDetail p with(nolock) on (f.mac_no = p.mac_no and f.fal_ftime = p.fal_ftime)
Left Join fcheck c with(nolock) on (f.mac_no = c.mac_no and f.ser_no = c.ser_no)
Left Join retditem r with(nolock) on (f.fal_ftime = r.fal_ftime)
Where (f.fal_ftime >= '201501030800' and f.fal_ftime < '201501040800') <-------------此處條件值直接給予, 查詢速度較快
And (c.mac_no is not null and c.ser_no is not null and c.ser_no <> '' and c.emp_no1 is not null and c.emp_no1 <> '')
And (p.mac_no is not null and p.ser_no is not null and p.ser_no <> '')
查詢時間差異至少1/3, 因為這只是總查詢的一小部份. 我曾透過Microsoft SQL Server Management Studio去分別查詢上述的"執行計劃", 奇怪的是居然不一樣, 是否有那位先進能不吝指導.
