データテーブルに可能なデータ件数は、どのくらいなのでしょうか。DB(サーバー)テーブルのデータ数が多いと、うまくいきません

Question

サーバー：SQL 2003　　　VB2010 です。

データテーブルに可能なデータ件数は、どのくらいなのでしょうか。DB(サーバー)テーブルのデータ数が多いと、うまくいきません。

DataGridView に検索表示するときは、約9万件でも表示します。　しかも表示までの時間も、6～10秒です。

ところが、DataTableを作るとなると、うまくいきません。

DataGridView,DataTable-- それぞれでの許容データ件数は、どのくらいなのでしょうか？
それとも、下記のコード自体に不備がありますか？

下記、A,B,Cと、データ件数の多少で場合分けしています。
　　　 'SQL認証接続
        Dim St As String
        Dim Con As New System.Data.SqlClient.SqlConnection
        Dim SQL As System.Data.SqlClient.SqlCommand
        Dim ServerName As String = GC_SVRName   'サーバー名(またはIPアドレス)
        Dim UserID As String = GC_UserID      'ユーザーID
        Dim Password As String = GC_Password   'パスワード
        Dim DatabaseName As String = GC_DatabaseName
        St = "Server=" & ServerName & ";"
        St &= "User ID=" & UserID & ";"
        St &= "Password=" & Password & ";"
        St &= "Initial Catalog=" & DatabaseName
        Con.ConnectionString = St
        SQL = Con.CreateCommand
        Con.Open()
        Dim adapter = New SqlClient.SqlDataAdapter()

        '以下は、F_I_Salesでの新規登録のための準備--T_Sales（DB、サーバー内）のデータ件数は、10万件以上。
        Dim ds_Sales = New DataSet
        Dim sqSales As String
       
         'OK データ10万件でも可--　たった、一件の検索なので。　動きます。
        (A)--sqSales = "SELECT T_Sales.Sales_ID FROM T_Sales Where Sales_ID=14000" 
       
　　　　 'データ件数多いと、不可（検索データ件数：約10万件）---フリーズします（動きません）。　
        (B)--sqSales = "SELECT T_Sales.Sales_ID FROM T_Sales Where Sales_ID <104040 " 
       
         'データ件数少ないと、可（データ件数、約290）　　動きます。
        (C)--sqSales = "SELECT T_Sales.Sales_ID FROM T_Sales Where Sales_ID >104040 " 

        adapter.SelectCommand = New SqlClient.SqlCommand(sqSales, Con)
        adapter.SelectCommand.CommandType = CommandType.Text
        adapter.Fill(ds_Sales)
        Dim dt_Sales As New DataTable
        dt_Sales = ds_Sales.Tables(0)

　　　　下記は、点検のためのコード；
        Dim n As Integer
        n = dt_Sales.Rows.Count
        Dim foundrows() As Data.DataRow
        foundrows = dt_Sales.Select("Sales_ID=Max(Sales_ID)")
        Dim ID_Max As Integer
        ID_Max = CInt(foundrows(0).Item("Sales_ID").ToString)
        ID_Max = ID_Max + 1    '新規追加登録ID

 

　　　　下記コードは、上記データ件数が多い時の回避策--ここでは、Sales_IDの最大値+1 を求めるのが目的なので。検索数：1件
　　　　'SELECT T_Sales.Sales_ID FROM T_Sales Where Sales_ID=(Select Max(Sales_ID) from T_Sales)"  　'DB(T_Sales)内での、Sales_IDの最大値の直接検索！

10万件でアウトになるなんて、信じがたいです。　私のコードに問題あり、でしょうか？

YKsaila

 

 

 

 

Answer 1

まず、「うまくいきません」ではなくて具体的な症状を提示した方が良いですよ。
※デバッグ実行してみれば実際にどこで止まっているのかすぐわかるのでは？

恐らくですが、DataTableにセットする所が遅いのではないと思います。

ただ単に取得したデータで作成したDataTableなので、
その後のSelectメソッドで前件検索が走っているのかと・・・
※しかもSelect("Sales_ID=Max(Sales_ID)")なのでMAXを取るのとそれと比較するので２回してるかも。

DataTableに主キー(PrimaryKey)を設定するか、DefaultView.Sortを指定して並び替えるかしてみてはいかがでしょう。
※DefaultView.Sortに関してはこれを指定するタイミングで時間かかるかもしれませんが。

Answer 2

aviatorさんへ

回答、ありがとうございます。

デバッグ自体までいかないようで、DataTableそのものが作成されていないようです？　デバッグそのもができません。

データ件数が多いと、点検用のコードがありますが、DataTable のデータ数（N）は、0 です。　でも、DB のデータ数を減らすと、Nは有意の数になります。

もとは、 sqSales = "SELECT T_Sales.* FROM T_Sales "  でした。　うんとも、すんともいいませんでした。

なので、いろいろと条件を付加してみてデータ数を減らす実験をしました。　条件指定しても、そのもとでのデータ数が多いなら同じことのようです。

上の行（Where なし）で、データ数が約10万件です。　実験のため、条件は付加せずに、サーバーのこのデータの大半を削除してやると（1万件くらいにすると）、DataTableは普通に作成されます。　ただ、時間はかかります。　DataGridViewほどは、速くはありません。

DB上では、Sales_IDが主キーです。　DataTableには主キーは、設定していません（未経験）。

上のコード、A・B・Cを見ていただくとわかりますが、そこにはMax はありませんので、Maxは無関係だと思います。

個人的には、完全にデータ数に原因があるが気がするのですが？　サーバーではないので、クライアントに10万件もデータを移送することに問題があるとか？　

例えば、アクセスでのテーブル・リンクは、クライアントPC にデータを取り込んでいるのでなく、単なるリンクですよね。

でも、DataTable は、リンクでなくデータそのものをもってくるわけですから。

と、考えているのですが？

YKsaila

Answer 3

> デバッグ自体までいかないようで、DataTableそのものが作成されていないようです？　デバッグそのもができません。

と言っても1行目にブレークポイントおいて1行ずつステップ実行すればどこで止まるかはわかると思います。
まずは
1. adapter.Fill(ds_Sales) ができてるのかできていないのか、終わっているのか終わっていないのか
2. 1.が大丈夫なら点検用のコードがおかしくないか
を確認すべきだと思います。

Answer 4

以下のページに、「DataTable が格納できる最大行数は 16,777,216 行です。」と書かれています。約１０万件を取得するとフリーズするとのことですが、１行当たりのサイズによってはメモリ不足になるのかもしれません。

DataTable クラス
http://msdn.microsoft.com/ja-jp/library/system.data.datatable%28VS.80%29.aspx

ところで、DataGridViewでは約９万件でも表示できているとのことですが、この場合のデータソースはデータテーブルではないのでしょうか？

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 5

＞ところで、DataGridViewでは約９万件でも表示できているとのことですが、この場合のデータソースはデータテーブルでは＞ないのでしょうか？

そうです、データソースは、データテーブルです。

フリーズしていたのでなく、時間がかかっていたのが真相です。
   
データテーブル自体は、件数が10万件でも問題なく作成されていました。 所要時間は、ほとんど0です。
ただ、データテーブルから最大値（Max）を下記(A)のコードで検索するのには、件数に応じて時間がかかります。

（最大値をもつ行を探す<SELECT文>方式で、行っています。）

サーバーから最大値を直接持ってくるときは、ほぼ一瞬でしたが、DataTableだと時間がかかるようです。
サーバーのテーブルと、DataTableとは構造・設定が異なるからなのでしょうね。

データテーブルから最大値を得る別の方法として、DataTableを事前に降順にソートしておいて1行目を選択すれば、速いのかもしれません（実験は未です）。

追記：12/11/13　14：30　上記ソート方式でもうまくいきます。　コードは、aviator＿さんへの返信に書きました。

データテーブルの場合、件数が多い時は、直接最大値を得るのは難しいのかもしれません。　一個一個見ているからなのでしょうか？

私は、サーバーから直接最大値をとってくる方法を採りました。　これだと速いです（時間はかかりません）

以下は、調査結果です：データテーブルから最大値をもつ行取得に要する時間---dt_Sales.Select("Sales_ID=Max(Sales_ID)")

下記******に、数値を入れて実験してみました。所要時間を記します。

n＝4,300　　　    1.5秒
n=14,000   　　15.7秒
n=24,000     　57.0秒
n=34,000   　 1分58秒
n=54,000      7分21秒
n=79,000     10分31秒
n=88,000     13分18秒
n=98,000     16分31秒
n=102,500    17分50秒

       Dim sw As New System.Diagnostics.Stopwatch()
        'ストップウォッチを開始する
        sw.Start()

        Dim ds_Sales = New DataSet
        Dim sqSales As String
        sqSales = "SELECT T_Sales.Sales_ID FROM T_Sales Where Sales_ID<******" 
        adapter.SelectCommand = New SqlClient.SqlCommand(sqSales, Con)
        adapter.SelectCommand.CommandType = CommandType.Text
        adapter.Fill(ds_Sales)
        Dim dt_Sales As New DataTable
        dt_Sales = ds_Sales.Tables(0)

        Dim n As Integer
        n = dt_Sales.Rows.Count

        Dim foundrows() As Data.DataRow

　　　　’下記コードの、"Sales_ID=Max(Sales_ID)" に難点あり---件数が多いと時間がかかる。

  (A)   foundrows = dt_Sales.Select("Sales_ID=Max(Sales_ID)")

        'ストップウォッチを止める
        sw.Stop()
        '結果を表示する
        Console.WriteLine(sw.Elapsed)  

      

YKsaila

Answer 6

mars12さんへ

おっしゃる通りです。　DataTable作成までは、出来ていました。

ありがとうございました。

詳細は、trapemiyaさんへの返信に書きましたので、お読みになってください。

YKsaila

Answer 7

aviaor＿さんへ

いろいろとやってみました。

新規登録用ＩＤ取得が、当初の目的でしたから、以下のコードで間に合いますね。

DataTableへの直接セレクトだと、厳しいみたいです（件数が多くなると）。　事前に降順にソートしてみました。所要時間は0に近いです。　うまくいきます。

     '以下は、F_I_Salesでの新規登録のための準備--T_Salesのデータ件数は、10万件以上。　
       'order by Sales_ID descで事前ソートしておく　→MAX--先頭行となる。

        Dim ds_Sales = New DataSet
        Dim sqSales As String
        sqSales = "SELECT T_Sales.Sales_ID FROM T_Sales order by Sales_ID desc" 
        adapter.SelectCommand = New SqlClient.SqlCommand(sqSales, Con)
        adapter.SelectCommand.CommandType = CommandType.Text
        adapter.Fill(ds_Sales)

        Dim dt_Sales As New DataTable
        dt_Sales = ds_Sales.Tables(0)
        Dim IDM As Integer
        IDM = CInt(dt_Sales.Rows(0).Item("Sales_ID").ToString)　　'データテーブルの先頭行

        '新規追加登録ID
        Dim ID_Max As Integer
        ID_Max = IDM + 1    '新規追加登録ID

YKsaila

Answer 8

新規登録用ＩＤ取得が、当初の目的でしたから、以下のコードで間に合いますね。

そのコードに書かれている新しいIDを決定するやり方は、同時に一人しか操作しないアプリケーションであれば良いですが、複数人が操作する場合は破綻してしまいます。このようなユニークなIDは、通常、SQL Server側で決定します。今後のアプリケーション開発を考えれば、例え一人しか同時に弄らない場合でも、複数人がいじっても大丈夫なように実装するのが良いと思います。この辺りはある程度パターン化できますので、そうしておけば、今後いろいろなアプリケーションを開発する上で、工数の削減や容易なメンテナンスにつながると思います。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 9

trapemiyaさんへ

そうでした。　うっかりしていました。　当面の問題の解決に限っていましたので。

実は、以前から、次のように設定しています。

１．新規登録ボタン（実際には登録準備）を押した時点---サーバーに接続し、その時点でのSales_IDの最大値を求め、これに1加算し、新規登録ID とします。

２．この瞬間、サーバーに新規登録IDを書き込みます。---新しい行追加です（サーバーのテーブルに、です）。

３．登録（登録準備）ボタンをクリックした後の、データ書き込みは、新規（追加）登録でなく、既存データ行の修正・変更扱いとなります。　別ボタンです。　新規登録・実行、と二個ボタン用意しておきます。

４．（1）＆（2）の時点で、事前に用意してある排他テーブルに、フォーム名（または、テーブル名）・ＩＤ等の条件を書き込みます。

　　つまり、排他処理をします。　排他処理は、悲観的排他処理を採用します（クライアントＰＣが多い場合に備えて）。

　　排他処理で衝突している（待機させる）ときは、衝突クライアントPC名、その時折のID(今回は、Sales_ID、主キー)をコメント表示します。

　　そうすれば、遠隔地・階が異なる等の場合でも、連絡はつきます（ダメな場合は管理者が解除--元のＰＣも該当画面は閉じます）。

５．新規登録が終了したとき、または登録を中止したときに、排他処理を自動解除します。

（途中中止のばあいは、ＩＤが欠番になる場合がありますが、これは仕方がないと考えています）

新規登録でなく、修正・変更の場合も排他処理は同じ考えです。　

つまり、サーバー・テーブル（T_Sales）で、一つのSales_ID（データ行）にアクセスできるのは、一台のみ（クライアントPCが数10台あっても）です、アクセス中は他のクライアントには待機してもらいます。

上記(1)&(2)の時点で、他のＰＣから新規登録があっても、コンピューターの時間では同時刻はあり得ない、時間差があると考えています。　とすれば、Sales_IDは別のものになりますので、衝突しません。

もし、完全な同時刻があったとしても（可能性は、ほぼ 0 では？）、サーバーから警告がでて回避されるのではないでしょうか。　また、ID が主キーであれば、複数のＰＣから同じ主キーでは同時に書き込めないですから、必ず主キーを設定しこれをIDとして採用すればいいのですよね。二個以上主キーを設定していても、二個セットで組めばいいと思います。

この考え・方法で、よろしいでしょうか？

余分ですが、今までの問題点をまとめました（訪問者の方のために）；

DataTableでは、10万件のデータ問題なし、たぶん100万件でもOK。　

しかし、DataTableでの、Select文のMax には難点あり、となります。　 foundrows = dt_Sales.Select("Sales_ID=Max(Sales_ID)")　のコードです。

10万件のデータの場合、上記コードのMAXは異常に時間がかかります。

しかし、MAXでなく、例えば　 dt_Sales.Select("Sales_ID=71")とすれば、10万件でもほとんど一瞬です。

10万件からIDが71番のものを選ぶのと、IDが最大なものを選ぶのには、大きな違いがあるということなのでしょうね。

それにしても、時間が違いすぎます。　不思議です、なんとなく理由は想像できるのですが。

ありがとうございました。

今後も、よろしくお願いします。

2012/11/13 21:50 追記

良く考えたら、新規登録の場合は上記方式でしたら、排他処理は不要ですね。　修正・変更の場合は、必要ですが。（主キーがSales_ID、ここは書き込み禁止、ですから）　中止の場合は、そのデータ行は削除します。

2012/11/13　22：05　追記

21：50 書き込みのコメント（排他処理不要の個所）は間違いです。　新規登録中に、他のＰＣがその追加Sales_ID にアクセスできますから、やはり排他処理は必要でした。

YKsaila

Answer 10

この考え・方法で、よろしいでしょうか？

そのような仕組みにされていたのですね。それであれば問題ありません。排他も短い時間しかかかりませんし、人間が操作を放置してロックがかかりっぱなしになることもありませんから、大丈夫です。ただ、通常はそのようなことはせず、新規登録時に排他をかけて（つまりトランザクションを切って）行ってしまうのが一般的です。yksailaさんの方法だとどうしても工数が多くなってしまうと思います。例えば、登録準備中の行は何かの区分で区別しないと、他のユーザーからも読めてしまうかもしれません。

10万件からIDが71番のものを選ぶのと、IDが最大なものを選ぶのには、大きな違いがあるということなのでしょうね。

おそらくSales_IDは主キーなのでインデックスを使用して高速に探せ、Max関数はインデックスを利用しないからでしょうか？ちなみに主キーに基づいて探すのであれば、DataTable.Rows.Findメソッドがより適しています。ただ、selectでも十分に速いようなので、体感できるほどの差は出ないかもしれません。

主キーによる検索だと速いと思いますが、それ以外の列でSelectした場合は遅くなると思います。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 11

trapemiyaさんへ

ご回答、ありがとうございます。

１．排他処理について

＞---だとどうしても工数が多くなってしまうと思います。

そうなんです、工数が多いのと、複雑なのが難点です。---これは、実はちょっとした悩みの種です。

パターン化できますのでコードの制作自体は問題ないのですが、それを ”どの時点で入れるべきなのか” をフォームごとに判断しなければいけないのが大変です。　フォームもある程度類型化できるとしても、やはり具体的に場面ごとに考える必要があり頭を使います。　まあ、かなり、きついです。　でも、いくつかの利点もあるので、今のところこの方法にしています。

＞例えば、登録準備中の行は何かの区分で区別しないと、他のユーザーからも読めてしまうかもしれません。

登録準備中の行は、既存レコードの修正・変更と同じ扱いなので、他のユーザーは、修正・変更の目的では、その行にアクセスできません。”ＰＣ***さんが、使用中です！”という内容のコメントがでて、その行に書き込むことは出来ないようになっています（読めるだけなら、実害はないし、たぶん登録中と他のユーザーは判断するでしょう）。 新規登録ＩＤを取得した瞬間に排他処理をかけ、他のＰＣからはアクセスできないように設定しますから。　登録が終了したら排他解除しますから、他のＰＣからもアクセスできます。

問題点--あるレコードの修正・変更着手時に、当のユーザーがその画面を終了せず、そのままにしておくと排他解除ができません。　タバコを吸いに休憩にいくとか、最悪の場合帰宅してしまうとか、です。 何かの拍子で作業を中断して忘れてしまうこともあるでしょう。　ソフトを終了すれば自動解除できますが、終了もせずＰＣを閉じてしまうこともあるかもしれません。　

この場合、他のＰＣでは、「どこどこのＰＣが排他処理をかけたままです、--」という内容のコメントがでますから、そのＰＣを開いて処理すればいいのですが、少し面倒です。　でも、私は、このコメントを出すということが、なんとなく好きでこの方法を採用しています。

工数の多いのと、複雑なのはなんとかしたいと思っているのですが----？

この方針でもう少し頑張ってみたいと思っていますが、通常のやり方に変えたほうが技術的にはいいのでしょうか？

＞おそらくSales_IDは主キーなのでインデックスを使用して高速に探せ、Max関数はインデックスを利用しないからでしょう

＞主キーによる検索だと速いと思いますが、それ以外の列でSelectした場合は遅くなると思います。

これは、面白そうなので実験してみます。

YKsaila

Answer 12

問題点--あるレコードの修正・変更着手時に、当のユーザーがその画面を終了せず、そのままにしておくと排他解除ができません。　　

ずっと排他がかかったままなんですね。登録準備中の行を作る時のみ排他をかけるのかと思いました。.NETではこのような悲観的ロックではなく、楽観的ロックに重きが置かれているように思います。そのため、データテーブルなどもそれを意識して設計されているように思います。

この方針でもう少し頑張ってみたいと思っていますが、通常のやり方に変えたほうが技術的にはいいのでしょうか？

上でも述べましたが、.NETには楽観的ロックを比較的楽に扱える仕組みが備わっていますので、そのようなやり方をお勧めします。

＃スレッドのタイトルと話題が合わなくなってきていますので、もしこの話題を続けられるのであれば、新たな別スレッドでお願いいたします。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 13

trapemiyaさんへ

いつも、ご教示ありがとうございます。

2週間ほど出張で不在でした（作業場は自宅なのです）。

悲観的ロック・楽観的ロック等については、いくつか質問があります。

別スレッドで、書き込みます。

YKsaila

Answer 14

DataTable は、リンクでなくデータそのものをもってくるわけですから。

と、考えているのですが？

遅くなってすいません。
もうどうでも良いかもしれませんが一応、

お考えの通りデータそのものを持ってくるので、持ってきた後のDataTableには改めて主キーなりをあてないと
遅くてしかたない状態になります。

よく「DataTableは遅くて使えない」とおっしゃる人がおられますが、
これって大体が生成した物をそのまま使っていて、
毎回FullScanかましてるんですよね・・・

お邪魔しました。