RiCo技術農場

這篇主要是分享寬資料表設計對效能影響是否明顯，

一般為了要減少join頻率，可能會在單一資料表上設計過多欄位(提高查詢效能)，

這樣的設計在ERP上很常見(如SAP、Oracle)，因為ERP需要一次呈現較多資料，

這樣的方式較省I/O，但我個人還是不太喜歡資料表過寬，當然凡事都沒有絕對，

我還是會依照當時情況衡量，目前我有一資料表約有100個欄位(總筆數超過1千3百萬以上)，

table layout和資料大致如下

create table mytest ( c1 int identity(1,1) not null ,c2 varchar(20) ,c3 nvarchar(30) ,c4 datetime ,pro1 varchar(254) ,pro2 varchar(254) ,pro3 varchar(254) ,pro4 varchar(254) ,pro5 varchar(254) ,pro6 varchar(254) ,pro7 varchar(254) ,pro8 varchar(254) ,pro9 varchar(254) ,pro10 varchar(254) ,constraint PK_mytest primary key(c1) ) declare @step int,@maxcount int set @step=0 set @maxcount=100000 while (@step <@maxcount) begin insert into mytest values('rico',N'祥',GETDATE() ,'A','B','C','D','E','F','G','H','I','J' ) --set @step=@step+1 --end select * from mytest

資料

 

 

一開始我本想使用ID，Value的設計來處理這些資料，如下

drop table props create table props ( c1 int not null ,proid varchar(10) ,protext varchar(254) ,proval varchar(254) ,constraint PK_props primary key(c1,proid) ) insert into props select c1,'p1','pro1',pro1 from mytest insert into props select c1,'p2','pro2',pro2 from mytest insert into props select c1,'p3','pro3',pro3 from mytest insert into props select c1,'p4','pro4',pro4 from mytest insert into props select c1,'p5','pro5',pro5 from mytest insert into props select c1,'p6','pro6',pro6 from mytest insert into props select c1,'p7','pro7',pro7 from mytest insert into props select c1,'p8','pro8',pro8 from mytest insert into props select c1,'p9','pro9',pro9 from mytest insert into props select c1,'p10','pro10',pro10 from mytest select * from props

 

 

假設今天需求要呈現屬性1、2、3、4、8、9、10相關資料

資料表1一次I/O肯定比較少

--查詢ID 1,5,7 pro1,pro2,pro3,pro4,pro8,pro9,pro10

select pro1,pro2,pro3,pro4,pro8,pro9,pro10 from mytest where c1 in (1,5,7)

 

資料表2I/O肯定高，且TSQL攏長(當然也可以透過[SQL SERVER][TSQL]動態 Pivot來簡化TSQL)

select a.pro1,b.pro2,c.pro3,d.pro4,e.pro8,f.pro9,g.pro10 from ( select c1,proval as 'pro1' from props where c1 in (1,5,7) and proid='p1' ) a left join ( select c1,proval as 'pro2' from props where c1 in (1,5,7) and proid='p2' ) b on a.c1=b.c1 left join ( select c1,proval as 'pro3' from props where c1 in (1,5,7) and proid='p3' ) c on a.c1=c.c1 left join ( select c1,proval as 'pro4' from props where c1 in (1,5,7) and proid='p4' ) d on a.c1=d.c1 left join ( select c1,proval as 'pro8' from props where c1 in (1,5,7) and proid='p8' ) e on a.c1=e.c1 left join ( select c1,proval as 'pro9' from props where c1 in (1,5,7) and proid='p9' ) f on a.c1=f.c1 left join ( select c1,proval as 'pro10' from props where c1 in (1,5,7) and proid='p10' ) g on a.c1=g.c1

(擷取部分)

 

結論:

很多事情沒有絕對好或絕對壞(我當然希望每件事情都能兩全其美面面俱到)，

雖然我個人很不愛寬資料表設計，

早期主要是因為記憶體不便宜(寬資料表需要更多記憶體)，但現在記憶體價格便宜，

而且現實世界中還有效能、專案時程和轉資料順暢度...等因素，我依然選擇我不愛的寬資料表設計，

只不過我還是有稍微分類一下，把原本一個大資料表拆成9個小資料表，

或許你會說，如以後要加屬性不就只能新增資料表欄位，

資料表2設計只需新增資料就好了，

而這就是我一開始提到很多事情沒有兩全其美，凡事總是需要思考衡量後取得一個平衡。

 

 

 

參考

[SQL SERVER][TSQL]動態 Pivot