SQLServer改善效能寫法

SQLServer改善效能Tip

提高效能的方法

*資料庫適當建立索引(index)增加搜尋的速度

*資料表增加欄位-於A表的相異2筆資料中，只有其中1筆資料填入B表，故B表只需開1個欄位放A表的資料

1.子查詢的關聯性(ON)，ON的後面優先放該子查詢索引再放關聯欄位

2.子查詢中盡量不用Where條件，將該Where條件可放在子查詢的關聯性(ON)的後面

select AID,AName
from ATest as at
inner join (select BID,BName,BType from BTest where BTest.BType = 'momo') as bt
on bt.BID = aa.AID

select AID,AName
from ATest as at
inner join Btest as bt
on bt.BType ='momo' and bt.BID = at.AID

3.子查詢中使用OR條件相當於IN條件，效能需看該SQL指令

select AID,AName,ASeq01,ASeq02,ASeq03
from ATest as at
inner join Btest as bt
on bt.BType ='momo' and bt.BID = at.AID
and at.ASeq01 = bt.BSeq OR at.ASeq02 = B.BSeq OR at.ASeq03 = B.BSeq

select AID,AName,ASeq01,ASeq02,ASeq03
from ATest as at
inner join Btest as bt
on bt.BType ='momo' and bt.BID = at.AID
and bt.BSeq in (at.ASeq01,at.ASeq02,at.ASeq03)

4.使用數字做為代號比用字串做為代號較有效率，且數字可做運算，其缺點為數字代碼在資料庫須為固定意義