iT邦幫忙

0

SQL年度區間查詢

sql

資料表表格如下
CD_2010
CD_2011
CD_2012
CD_2013
資料表格是每年度開一個新的表
假設要查區間2010/10-2013/02
就必須
select * from cd_2010 where month(date) >=10
union all
select * from cd_2011
union all
select * from cd_2012
union all
select * from cd_2013 where month(date) <=02

請問各位前輩除了這種方式還有更好的方式查詢嗎?因資料量一大就跑很久。

2 個回答

6
richardsuma
iT邦大師 3 級 ‧ 2013-07-22 16:24:29
最佳解答

資料表格是每年度開一個新的表

好處就是 資料 會被侷限在當年度,資料量不會太大。
但是 跨年度 就會出現妳所提問的問題。

如果妳要使用下列SQL語法,建議不要用萬用字元(*),請使用精準的欄位名稱,
還有把條件設定正確與完整。
select * from cd_2010 where month(date) >=10
union all
select * from cd_2011
union all
select * from cd_2012
union all
select * from cd_2013 where month(date) <=02

另外,可以建立一個view,並建立妳需要的索引,會加快妳查詢的速度。

或者,將年度資料彙整至另外一個彙總查詢的table,
不一定要跟現有資料table在一起使用。

不知道妳們公司使用的資料庫是那一個版本的,
資料量會大到需要依年份建立table,以現行資料庫要容納幾億筆,應該不成問題才是?

lisahsu iT邦新手 5 級 ‧ 2013-07-23 09:25:50 檢舉

richardsuma大大
目前SQL Server版本是MSSQL 2000,每一個年度table約有200萬筆資料,已有建立一個VIEW的方式,且索引是by 日期,但查詢一次約略20分鐘左右,真是令人困擾…

試試加大 Server 的 RAM。
更換在快一點的硬碟。
確認view是否還有其他可以建立的「複合索引」來加快速度。
如果還有機會,更新 MSSQL 版本,不過要花不少費用。

8
poiu124pat
iT邦新手 3 級 ‧ 2013-07-23 11:11:29

我們公司也是用SQL2000,有一個資料表就三千萬筆資料了,每個月都還在以數十萬筆以上的資料在增加,查詢時也不會花到那麼久的時間。
這很明顯就是資料庫沒有規劃好,也沒有優化的結果。
請善用索引,select的效能就會差很多了。你的結果效能會以爆炸性的方式去改善的。(ex..20分鐘變成2分鐘)!
要記得,硬體的優化都是排在最後面的。

另外,你那樣的做法一般簡單來說是用view的方式,也可以將會用到跨年度查詢的資料另外儲存成一個table中,只專門做查詢的動作(也只儲存查詢會用到的資料就好),也可以避免同一個資料表在查詢與寫入時發生的lock死結現象,再針對查詢的語法去對該table進行優化。
在更進階的,就是寫資料表函數,將where與union 以程式化的方式組合,傳遞參數後輸出,這樣也是另一種優化方式,不過這樣的方法比較進階,還要與前端配合才行,而且寫法會很複雜。

我要發表回答

立即登入回答