MSSQL查詢時, 給予參數方式的效率

• 問題

• 請教各位先進, 在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去分別查詢上述的"執行計劃", 奇怪的是居然不一樣, 是否有那位先進能不吝指導.

2015年4月1日 上午 01:58

所有回覆

• Using parameters in ad hoc query, optimizer doesn't know value of parameters when compile the query therefore may generate inefficient plan.
2015年4月1日 上午 02:28
• 非預存程序的查詢應該都要編譯吧? 不知我的認知是否有誤? 故上列語句不論那一條皆應編譯, 而這編譯會差那麼多的時間嗎?

另外我覺得訝異的句法幾乎都一樣, 只是一個是傳參數, 另一個是直接給值, 但其"執行計劃"郤不一樣, 不知MSSQL的"執行計劃"是如何產生的? 我無法將"執行計劃"匯出, 否則就其"執行計劃"真可好好討論一下.

2015年4月1日 上午 02:42
• Yes, compile takes about same time but the issue is whether optimizer has enough info to generate better plan. For second one, optimizer knows what needed and is able to generate right plan based on stats. But optimizer doesn't know what needed for first query therefore generated a 'safe' plan, not necessary good one.
2015年4月1日 上午 03:34