註記

想要試一下 Column Stored Index,於是產生 6 千多萬筆紀錄,但在 NB 7200 轉的硬碟上,做任何事情都需要半個小時以上,但我需要比對效能,因此需要兩個資料表,建 clustered index,建 column stored index,來回數個小時不見了。為了要讓其他的練習可以繼續,啟動另一個 Hyper-V,因此不斷下修 SQL Server Max RAM,讓其從 10 幾G 降到 8G。i7 的 CPU 和 16G RAM 的 NB,其主要 Bottleneck 應該是 HD

至今,可在 NB 上練習 6千萬筆記錄,但單位都以半小時計,期待以分鐘計的軟硬體技術…

建完後,類似的語法比較(都是以第二次執行的結果):

傳統索引

CREATE NONCLUSTERED INDEX [idx2]
ON [dbo].[BigFactInternetSales2] (PromotionKey)

SELECT COUNT(*) FROM BigFactInternetSales2
GROUP BY [PromotionKey]

資料表 ‘BigFactInternetSales2’。掃描計數 9,邏輯讀取 138544,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

SQL Server 執行次數:
CPU 時間 = 19593 ms,經過時間 = 2930 ms。傳統的作法,CPU /IO 操得很兇

 

Column Stored Index

資料表 ‘BigFactInternetSales’。掃描計數 8,邏輯讀取 492,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。
資料表 ‘Worktable’。掃描計數 0,邏輯讀取 0,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。
資料表 ‘Worktable’。掃描計數 0,邏輯讀取 0,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

SQL Server 執行次數:
,CPU 時間 = 531 ms,經過時間 = 98 ms。

沒有建 Column Stored Index 的

CREATE NONCLUSTERED INDEX [idx]
ON [dbo].[BigFactInternetSales2] ([CustomerKey])
INCLUDE ([PromotionKey],[SalesAmount],[TaxAmt],[Freight],[OrderDate])
go

select t1.*,p.EnglishPromotionName
from (
select PromotionKey,Year(OrderDate) OrderDateYear,sum(SalesAmount) SumSalesAmount,
avg(Taxamt) AvgTaxAmt,Sum(Freight) SumFreight
from BigFactInternetSales2 s
join dbo.DimCustomer c on s.CustomerKey=c.CustomerKey
where c.Gender=’F’
group by PromotionKey,Year(OrderDate)) t1
join dbo.DimPromotion p on t1.PromotionKey=p.PromotionKey
ORDER BY OrderDateYear

資料表 ‘Worktable’。掃描計數 0,邏輯讀取 0,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。
資料表 ‘BigFactInternetSales2’。掃描計數 9133,邏輯讀取 255776,實體讀取 2,讀取前讀取 2488,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。
資料表 ‘DimCustomer’。掃描計數 9,邏輯讀取 974,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。
資料表 ‘DimPromotion’。掃描計數 1,邏輯讀取 20,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。
資料表 ‘Worktable’。掃描計數 0,邏輯讀取 0,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

SQL Server 執行次數:
,CPU 時間 = 32135 ms,經過時間 = 5490 ms。

 

Column Stored Index

資料表 ‘DimCustomer’。掃描計數 8,邏輯讀取 846,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。
資料表 ‘BigFactInternetSales’。掃描計數 8,邏輯讀取 35197,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。
資料表 ‘DimPromotion’。掃描計數 1,邏輯讀取 20,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。
資料表 ‘Worktable’。掃描計數 0,邏輯讀取 0,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。
資料表 ‘Worktable’。掃描計數 0,邏輯讀取 0,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

SQL Server 執行次數:
,CPU 時間 = 12168 ms,經過時間 = 1632 ms。

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: