none
[VB 2005搭配SQL Server 2005] 關於從SQL Server 2005讀取大量資料時候的問題 RRS feed

  • 問題

  •  

    各位老師好

    今天我想請問的是

    若我用VB 2005撰寫的程式碼從SQL Server 2005讀取大量資料筆數的時候

    要怎樣才能避免 記憶體滿載與系統繁忙時候的問題

     

    我現在的做法是

    例如資料庫有100萬筆資料

    我需要在報表內,列出所有筆數

    我用SQLDataAdapter.Fill 方法 把所有資料載入到DataTable內

    再把這個DataTable指給報表去顯示出來

    我想請問,這樣子做算是快的方法嗎?若不是,有沒有什麼快,效率好的方法

     

     

     

    另外還有一個問題是

    假如有100萬筆資料,我有一個需求是載入這100萬筆資料,然後在我的軟體裡面繪圖圖形

    我現在的做法是  載入這100萬筆資料到陣列Array裡面,然後再用陣列去繪出圖形

     

    可是這樣一來我發現.一來慢,二來記憶體滿檔,如此就造成系統一直當機

     

    請問有沒有什麼方法可以改善或避免??

     

     

     

    謝謝

    感激不盡...

    2008年1月20日 上午 08:49

解答

  • 索引屬於資料庫重要結構之一,依照關聯式資料庫基本原則,這是規劃期就該處理的,而不是資料查詢期才處理。

     

    使用管理工具 (SQL Server Management Studio) 選到資料表,滑鼠右鍵選設計,要加入索引的欄位滑鼠右鍵就可以選擇,主索引鍵跟一般索引的差異書上都會寫,先看一下再決定。

     

    2008年1月25日 上午 10:04
  • 資料庫設計是一種藝術,索引是一種工具,但當設計不良的資料庫你用在好的工具都沒用.

    我看過很多人問問題,說這題怎麼解,我看了題目,基本上很單純,但我不會解,太久沒解這種問題,因為那是我開始學資料庫時的寫法,以前會用很複雜的語法,用好幾百行去解,但現在的資料庫只要幾行就解出來.

     

    資料庫設計須正規劃,老實說書上寫什麼五層正規劃,我都看不懂,我都用很直覺得去設計,只將有意義的資料去存,資料有重複的要在正規劃,千萬不要設計很大的資料表然後去索引,記得你每一個索引(PK除外),都會做出索引表,所以資料量會變大,又要整理索引表,所以看需求去規劃.

     

    而要有效率的查詢,跟資料庫,跟網路,跟環境....都有關係,所謂變則通,像有一篇說一個Table 有好幾百萬的Record 怎麼有效率的Count出來,你Count的語法解不出來,那你就做一個Table,裡面放一個Record Count ,然後你在 Insert,Delete時,直接加減這個欄位就可以了.

     

    如果以你這個有時間序的問題,你就用一個DataTime 的欄位去當PK,這個PK本來就有索引,或用Indetity 去當PK也可以.

    查詢時正排,反排都很快,也可以輕易的下某一區段的查詢,或使用非同步設計樣式,一段一段的讀,在應用程式在組成完整的資料.

     

    2008年1月25日 下午 03:17
  •  好說 寫信:

    資料庫設計是一種藝術,索引是一種工具,但當設計不良的資料庫你用在好的工具都沒用.

    我看過很多人問問題,說這題怎麼解,我看了題目,基本上很單純,但我不會解,太久沒解這種問題,因為那是我開始學資料庫時的寫法,以前會用很複雜的語法,用好幾百行去解,但現在的資料庫只要幾行就解出來.

     

    資料庫設計須正規劃,老實說書上寫什麼五層正規劃,我都看不懂,我都用很直覺得去設計,只將有意義的資料去存,資料有重複的要在正規劃,千萬不要設計很大的資料表然後去索引,記得你每一個索引(PK除外),都會做出索引表,所以資料量會變大,又要整理索引表,所以看需求去規劃.

     

    而要有效率的查詢,跟資料庫,跟網路,跟環境....都有關係,所謂變則通,像有一篇說一個Table 有好幾百萬的Record 怎麼有效率的Count出來,你Count的語法解不出來,那你就做一個Table,裡面放一個Record Count ,然後你在 Insert,Delete時,直接加減這個欄位就可以了.

     

    如果以你這個有時間序的問題,你就用一個DataTime 的欄位去當PK,這個PK本來就有索引,或用Indetity 去當PK也可以.

    查詢時正排,反排都很快,也可以輕易的下某一區段的查詢,或使用非同步設計樣式,一段一段的讀,在應用程式在組成完整的資料.

     

     

    索引在資料庫中很重要,除非你想要每次查詢時都用 TABLE SCAN,然後每次都要花 O(n) 的時間去查詢,而不用更節省時間的索引,別忘了 O(logn) 才是王道,不懂什麼叫 O(n), O(logn) 沒關係,但不能不知道索引的重要性,尤其是大型資料表,n 愈大的話,節省時間的優勢會愈明顯。

     

    當 n = 1,000,000 時,TABLE SCAN 會用掃瞄 1,000,000 筆的時間,而 INDEX SCAN 只會花到 log1000000 筆的時間。

     

    很多人都認為索引不重要,然後在資料大量增加的時候,再來抱怨 SQL Server 很慢。

     

    如果連索引都無法解決時,就要用 Snapshot Table 的技巧來處理,然後善用 SQL Server Agent 的自動化工作來產生 Snapshot Table 的資料,查詢時就直接查 Snapshot Table 的資料即可。

     

    就算索引會變大,也不能不用它,這是必要之惡。

    2008年1月25日 下午 03:35
    版主

所有回覆

  • 一般狀況請利用分頁查詢。

     

    繪圖請考慮最多必要量,比如說 A4 橫印寬度為 27 cm ,約為 10.7 英吋,假設你印表機解析度為 1200 DPI ,最多解析度為 12,840 點,所以你只需要 1 萬點的資料就能把圖畫得很好,你若沒有用到滿版寬,所需的點數會更少,可依實際資料換算需要的量,若有瞬間極值,只需要抓最大值、最小值分別跑一次,再崁入繪圖即可。

     

    2008年1月20日 上午 09:51
  • 謝謝璉大

     

    第二個問題我已經瞭解了

    關於分頁查詢這部分,可否再解釋得更詳細一點呢??

     

    謝謝~~

     

    2008年1月21日 上午 01:09
  • 分頁查詢的意思是

    不必要一次就將100萬筆資料回傳 這樣查詢的效能 以及回傳的效能會很差

    因此可先挑選部分資料回傳 資料量少 效能自然就好

    可以參考以下

    http://forums.microsoft.com/MSDN-CHT/ShowPost.aspx?PostID=1799401&SiteID=14

     

    2008年1月21日 上午 01:38
  • 資訊系統,所謂資訊系統是資料經過分析,比較,過濾,排序......後所產生的結果.

    我相信大部分的人都是查詢資訊,而不是資料,所以你一次讀取100萬筆可能某地方的概念有一點偏差(工程程式,我沒研究所以排外).

    所以比如資料庫有一百萬張訂單,但通常他們只要幾張訂單,還是幾月至幾月的訂單,或著是月績效,季績效...

    這些資訊,雖然是從好幾萬筆數據產生,但通常使用者最後只要幾個欄位的數據而已.

    而如果你是全部資料全部讀近來,這跟以前使用者用簿子抄寫,一筆一比去翻去查有何差別.

     

    而如果以妳的問題,要讀取好幾百萬筆資料才能Layout圖形,妳就要考慮轉演算法的問題,將點陣圖改成向量圖.

    或者像寫遊戲一樣,走走走,走到某房子,然後去讀取房子的圖形,在顯示圖形,或者走到螢幕的某邊,比如螢幕右邊的300點,然後用非同步方式將右邊的圖形算出來...千萬不能全部Down下來,除非你是在寫A片,一定要全部Down,不然絕對不可行.

    2008年1月21日 上午 02:27
  • 小弟用的是測試程式

    所以資料載入時必須做全DateBase條件式搜尋,載入時會固定搜尋某個特定範圍,或是資料庫內與某個特定條件相符合的資料

    都要載入進去

    不像是查詢類的資料庫

     

    我現在盡量避免記憶體滿載,似乎就比較順一點

    現在20萬筆的數據.做搜尋與載入大概是1分鐘(我不是全部載入,是有條件或範圍,但是似乎是免不了資料庫的完全搜尋動作)

     

     

    2008年1月24日 上午 05:20
  • 你有沒有設資料表的索引?

    如果沒有,會造成每次查詢都是 TABLE SCAN,當然會有效能損耗。

     

    20 萬筆一分鐘太長了,應該要縮小到 5 秒以內,除非查詢本身太複雜或者是每列資料量都很大。

     

    2008年1月24日 上午 05:32
    版主
  • 請教小朱老師

     

    資料表的索引要怎麼設定與應用ㄋ

     

    一些書跟技術文件都講得好複雜說

    有點看不懂

    2008年1月24日 上午 05:43
  • 最簡單的判斷方法是,以你查詢時下的條件欄位為主,只要有被當做條件搜尋的欄位都要加上索引。

     

    你也可以用 SQL Server Management Studio 去執行你的查詢,然後看查詢計畫。

    如果有出現 TABLE SCAN,那就要在該指令所掃描的資料表上加索引,然後調整 SQL 指令。

    2008年1月24日 上午 05:50
    版主
  • 那請問小朱老師

    加上索引的步驟與語法,或是該注意的地方有那些?

     

    謝謝

    2008年1月24日 上午 06:04
  • 妳把你的需求寫一下,我們替你看看問題的所在,有時問題點根本不在這裡.

    老實說,妳一次那麼大的資料量,就算用索引,或各種方式也都不符需求.

    如果用網路去傳的話也會TimeOut 的問題.

    反正妳這個問題要從需求面分析,不然會有許多不確定因素產生.

     

    2008年1月24日 上午 06:06
  • 各位老師好

    我目前是沒有設索引

    所以我不知道我20萬筆再讀要花一分鐘是不是沒設定索引的關係

    如果照小朱大大這樣解釋的話.很有可能是這樣

     

    所以我才想設定索引看看

     

    我的資料表結構並不會很複雜

    只是單純的一些數據(整數或是single型態)

     

    我在做的是測試機器的軟體,機器反覆再跑.資料就一直儲存起來

    這些資料會拿來做曲線,報表,或是計算

     

    都是在本機作業.所以沒有網路的問題

     

    謝謝各位

    2008年1月24日 上午 06:15
  • 建立索引:

    http://msdn2.microsoft.com/zh-tw/library/ms190197.aspx

     

    你還是去買本書或翻線上文件比較好,這種設計要講起來就可能可以寫一本書了。

     

    2008年1月24日 上午 06:23
    版主
  • 了解,看起來單純多了.

    你增加一個時間欄位去做索引.

    然後用非同步方式去一個範圍,一個範圍的讀取就好了.

    2008年1月24日 上午 06:31
  • 謝謝

    可是關於建立索引的方式與應用我有點不清楚

    目前我看得VB 2005教學書只有一本有教到索引,不過小弟還很淺,他寫得比較深入一點

    所以小弟看不太懂

    請問小朱老師 ,市面上有哪本書有教這方面的嗎?

    可以推薦一下嗎?

    我去買來看....

     

     

    謝謝

    2008年1月24日 下午 02:46
  • 你可以看看:

    http://www.delightpress.com.tw/book.aspx?book_id=SKUD00008

     

    以及英文書:

    MCITP Training Kit: Designing and Optimizing Data Access by using SQL Server 2005

     

    但書還是以自己看的下去為首選。
    2008年1月24日 下午 02:53
    版主
  • 答案就在題目中了.

    你就已經提到"VB 2005搭配SQL Server 2005",所以你要找 SQL Server 2005 管理的書找起. 程式的書很少會提起的.

    而且Index 是在 SQL Manager,或相關的Tool 設,跟程是邏輯關係不大,所以如果你還在想在VB 下達建立所引的語法就大錯特錯了.

    2008年1月25日 上午 09:40
  •  

    謝謝

    那本我買了,就等到貨時來看一下,希望寫得淺顯一點^^

     

    根據好說大大的說法,是在manager裡面建立囉?或是在studio裡面建立資料庫時就要建立?

    那在VB裡面查詢應用ㄋ,可否解釋更進一步?

     

     

    謝謝

    感激不盡

    2008年1月25日 上午 09:57
  • 索引屬於資料庫重要結構之一,依照關聯式資料庫基本原則,這是規劃期就該處理的,而不是資料查詢期才處理。

     

    使用管理工具 (SQL Server Management Studio) 選到資料表,滑鼠右鍵選設計,要加入索引的欄位滑鼠右鍵就可以選擇,主索引鍵跟一般索引的差異書上都會寫,先看一下再決定。

     

    2008年1月25日 上午 10:04
  • 索引可在建資料表時建立,也可以事後再建立。

    2008年1月25日 上午 10:11
    版主
  • 資料庫設計是一種藝術,索引是一種工具,但當設計不良的資料庫你用在好的工具都沒用.

    我看過很多人問問題,說這題怎麼解,我看了題目,基本上很單純,但我不會解,太久沒解這種問題,因為那是我開始學資料庫時的寫法,以前會用很複雜的語法,用好幾百行去解,但現在的資料庫只要幾行就解出來.

     

    資料庫設計須正規劃,老實說書上寫什麼五層正規劃,我都看不懂,我都用很直覺得去設計,只將有意義的資料去存,資料有重複的要在正規劃,千萬不要設計很大的資料表然後去索引,記得你每一個索引(PK除外),都會做出索引表,所以資料量會變大,又要整理索引表,所以看需求去規劃.

     

    而要有效率的查詢,跟資料庫,跟網路,跟環境....都有關係,所謂變則通,像有一篇說一個Table 有好幾百萬的Record 怎麼有效率的Count出來,你Count的語法解不出來,那你就做一個Table,裡面放一個Record Count ,然後你在 Insert,Delete時,直接加減這個欄位就可以了.

     

    如果以你這個有時間序的問題,你就用一個DataTime 的欄位去當PK,這個PK本來就有索引,或用Indetity 去當PK也可以.

    查詢時正排,反排都很快,也可以輕易的下某一區段的查詢,或使用非同步設計樣式,一段一段的讀,在應用程式在組成完整的資料.

     

    2008年1月25日 下午 03:17
  •  好說 寫信:

    資料庫設計是一種藝術,索引是一種工具,但當設計不良的資料庫你用在好的工具都沒用.

    我看過很多人問問題,說這題怎麼解,我看了題目,基本上很單純,但我不會解,太久沒解這種問題,因為那是我開始學資料庫時的寫法,以前會用很複雜的語法,用好幾百行去解,但現在的資料庫只要幾行就解出來.

     

    資料庫設計須正規劃,老實說書上寫什麼五層正規劃,我都看不懂,我都用很直覺得去設計,只將有意義的資料去存,資料有重複的要在正規劃,千萬不要設計很大的資料表然後去索引,記得你每一個索引(PK除外),都會做出索引表,所以資料量會變大,又要整理索引表,所以看需求去規劃.

     

    而要有效率的查詢,跟資料庫,跟網路,跟環境....都有關係,所謂變則通,像有一篇說一個Table 有好幾百萬的Record 怎麼有效率的Count出來,你Count的語法解不出來,那你就做一個Table,裡面放一個Record Count ,然後你在 Insert,Delete時,直接加減這個欄位就可以了.

     

    如果以你這個有時間序的問題,你就用一個DataTime 的欄位去當PK,這個PK本來就有索引,或用Indetity 去當PK也可以.

    查詢時正排,反排都很快,也可以輕易的下某一區段的查詢,或使用非同步設計樣式,一段一段的讀,在應用程式在組成完整的資料.

     

     

    索引在資料庫中很重要,除非你想要每次查詢時都用 TABLE SCAN,然後每次都要花 O(n) 的時間去查詢,而不用更節省時間的索引,別忘了 O(logn) 才是王道,不懂什麼叫 O(n), O(logn) 沒關係,但不能不知道索引的重要性,尤其是大型資料表,n 愈大的話,節省時間的優勢會愈明顯。

     

    當 n = 1,000,000 時,TABLE SCAN 會用掃瞄 1,000,000 筆的時間,而 INDEX SCAN 只會花到 log1000000 筆的時間。

     

    很多人都認為索引不重要,然後在資料大量增加的時候,再來抱怨 SQL Server 很慢。

     

    如果連索引都無法解決時,就要用 Snapshot Table 的技巧來處理,然後善用 SQL Server Agent 的自動化工作來產生 Snapshot Table 的資料,查詢時就直接查 Snapshot Table 的資料即可。

     

    就算索引會變大,也不能不用它,這是必要之惡。

    2008年1月25日 下午 03:35
    版主
  •  

    謝謝  小朱,好說,還有璉大

    這篇真的讓我受益良多了,越艱深的技術,基本的觀念越是重要,受教了

    書我已經買了.我會朝著今天說的這些方面去嘗試

    有問題再上來請教各位

     

    謝謝各位老師,感激不盡

     

    2008年1月25日 下午 04:17
  • 小朱大大可能誤會我的意思.

    我的說法/想法是設計比實作重要.

    我不是說索引不好.

    而就資料庫設計方面,就是正規劃.

    一個比喻.

    一個設計師設計一個Table 200 欄位,建 150 個索引,產生 150 個索引表.

    一個設計師設計正規劃後 10 Table 每個 25 欄位(因為多PK/FK) ,每個 Table 自我 PK 一個索引,外加 1-2個索引.

     

    在這裡我相信第二個設計師的資料量,查詢速度....應該會比第一個好多了.

     

    索引有好有壞/任何工具都是這樣子的,如果只有好沒有壞的東西,Tool 就不會有選項,讓開發人員去選,所以要用時都要考慮.

     

    但這只是個人之見而已,每個方案/環境/需求....都要分析,要索引就要索引,當然索引也不是亂加,你要分析過使用者查詢方式,不然所有Table 的所有欄位都加不是更快.

    2008年1月28日 上午 02:09