none
SQLServer2008R2で20万件のテーブルのデータ抽出が遅い RRS feed

  • 質問

  • CPU:AMD Opteron 6376 2.31GHz
    メモリ:8GB
    のサーバー上でSQLServer2008R2 WebEditionを実行してます。

    ASP.NETでWEBアプリを作成しており、「t_test」というテーブルに20万件ほどのデータが入っている状況です。
    定義は
    varchar(50)のフィールドが15個、
    varchar(max)のフィールドが5個(それぞれ2000バイト程度のデータが入ってます)、
    intのフィールドが2個(内、一つが主キー)、
    datetimeのフィールドが4個
    のテーブルとなっています。

    このテーブルにサーバー上で以下のクエリを実行したときに、
    4秒ほどかかってしまいWEBサイト自体が5秒ぐらいかかっている状況なのですが、
    一般的に20万件のテーブルだとこのぐらいの速度なのでしょうか?

    世の中のサイトを見るともっとデータ数が多そうなのに、割とサクサクと動いている気がしています。
    サーバーのスペックや台数構成の問題もあるかと思いますが、
    例えばCore-i7-6700 3.40GHz、メモリ8GBのPC上のSQL2012Expressでは1秒かかるかかからないかで返ってきています。

    SELECT a.* 
    FROM( 
     SELECT COUNT(*) OVER() AS RowCnt, ROW_NUMBER() OVER(ORDER BY test.d DESC, test.a) AS RowNum, 
     test.a, test.b, test.c, test.d, test.e, test.f, test.g 
     FROM t_test AS test 
    ) AS a 
    WHERE a.RowNum BETWEEN 1 and 30 

    ※上記「test.a」が主キーでクラスター化インデックスとして登録されてます。
    ※COUNTを無くすと2秒ほど速くなりますがページングをする為に必要です。

    また、単純に
    select * from t_test
    をサーバー上で実行した場合でも、全件抽出に40秒ほどかかります。
    この時点でおかしいでしょうか?

    この例だと、条件・ソート無しですが、インデックスの貼りようはありますでしょうか?


    2018年6月27日 6:59

回答

  • きゅうきゅうきゅうさん

    サーバースペックにもよりますが、限界まで高速にするなら下記のチューニングが有効かと思います。

    詳しい説明は省きますが、速くなるはずです。

    (↓クエリは実行はしてないので、エラーがでるかもです、、適宜修正してください。分からなければ聞いてください)

    --test.a は含めなくてもPKなのでインデックスのリーフページに含まれます create index IX_Name on t_test (d desc, a asc) select b.* --test2はselectに必要なカラムをSELECTするためだけにわざとJOINさせる ,test2.b ,test2.c ,test2.d ,test2.e ,test2.f ,test2.g --件数はここで個別にとりましょう ,(select COUNT(*) from t_test) as RowCnt from ( --上記インデックスを作成することで、ここのサブクエリは爆速になるはずです select a.* from ( select ROW_NUMBER() over ( order by test.d desc ,test.a ) as RowNum ,test.a from t_test as test ) as a where a.RowNum between 1 and 30 ) as b --test2はselectに必要なカラムをSELECTするためだけにわざとJOINさせる join t_test as test2 on b.a = test2.a --order by b.RowNum --昇順に並び替えたければこちらも使ってください。このorder byの有無で速度はほとんど変わらないはずです


    ↑これで遅い場合は

    set statistics time on
    (select COUNT(*) from t_test)

    そもそもこのクエリでどのくらい時間がかかるか教えてください。ミリ秒単位で実行時間を取得できます。
    https://docs.microsoft.com/ja-jp/sql/t-sql/statements/set-statistics-time-transact-sql?view=sql-server-2017

    >select * from t_test
    >をサーバー上で実行した場合でも、全件抽出に40秒ほどかかります。
    >この時点でおかしいでしょうか?

    サーバー上で実行、というのは、結果を表示させてますでしょうか?(SQLServerManagementStudioなど使用している?)結果の表示にかなり時間がかかるため、一概に40秒すべてが抽出処理にかかった時間というわけではありません。

    また、おかしいかどうか、というのはきゅうきゅうきゅうさんが求める処理速度によって変わってきます。
    遅いと感じていらっしゃるのであれば(実際、4秒は一般的にかなり遅いかと思います)、クエリのチューニングやサーバーのスケールアップ等の検討が必要になると思います。

    2018年6月27日 9:31

すべての返信

  • 受け取るアプリケーション側について一切書かれていませんが、そちらには原因はないのでしょうか? 例えば varchar(50) や varchar(max) とありますが、受け取るアプリケーション側でUnicodeに変換していたりしませんか?
    2018年6月27日 7:18
  • 返信ありがとうございます。
    また、言葉足らずで申し訳ありません。

    受け取り側は同じサーバー上のASP.NET(C#)で動作しており、
    SQLDataSourceにバインドして表示しています。

    最初はアプリ側を疑ったのですが、
    アプリ側で発行しているSQL文をサーバーのSQLServer Management Studio上のクエリで直接実行しても
    遅かったためそもそもDB側で何とかしないといけないと考えました。
    (テーブル設計の問題か、SQLServerのメモリ設定等の問題か)

    単純な
    select * from t_test
    というクエリでも遅い為、サーバーのスペック不足なのかもしれませんが・・・



    2018年6月27日 9:03
  • きゅうきゅうきゅうさん

    サーバースペックにもよりますが、限界まで高速にするなら下記のチューニングが有効かと思います。

    詳しい説明は省きますが、速くなるはずです。

    (↓クエリは実行はしてないので、エラーがでるかもです、、適宜修正してください。分からなければ聞いてください)

    --test.a は含めなくてもPKなのでインデックスのリーフページに含まれます create index IX_Name on t_test (d desc, a asc) select b.* --test2はselectに必要なカラムをSELECTするためだけにわざとJOINさせる ,test2.b ,test2.c ,test2.d ,test2.e ,test2.f ,test2.g --件数はここで個別にとりましょう ,(select COUNT(*) from t_test) as RowCnt from ( --上記インデックスを作成することで、ここのサブクエリは爆速になるはずです select a.* from ( select ROW_NUMBER() over ( order by test.d desc ,test.a ) as RowNum ,test.a from t_test as test ) as a where a.RowNum between 1 and 30 ) as b --test2はselectに必要なカラムをSELECTするためだけにわざとJOINさせる join t_test as test2 on b.a = test2.a --order by b.RowNum --昇順に並び替えたければこちらも使ってください。このorder byの有無で速度はほとんど変わらないはずです


    ↑これで遅い場合は

    set statistics time on
    (select COUNT(*) from t_test)

    そもそもこのクエリでどのくらい時間がかかるか教えてください。ミリ秒単位で実行時間を取得できます。
    https://docs.microsoft.com/ja-jp/sql/t-sql/statements/set-statistics-time-transact-sql?view=sql-server-2017

    >select * from t_test
    >をサーバー上で実行した場合でも、全件抽出に40秒ほどかかります。
    >この時点でおかしいでしょうか?

    サーバー上で実行、というのは、結果を表示させてますでしょうか?(SQLServerManagementStudioなど使用している?)結果の表示にかなり時間がかかるため、一概に40秒すべてが抽出処理にかかった時間というわけではありません。

    また、おかしいかどうか、というのはきゅうきゅうきゅうさんが求める処理速度によって変わってきます。
    遅いと感じていらっしゃるのであれば(実際、4秒は一般的にかなり遅いかと思います)、クエリのチューニングやサーバーのスケールアップ等の検討が必要になると思います。

    2018年6月27日 9:31
  • > 受け取り側は同じサーバー上のASP.NET(C#)で動作しており、
    > SQLDataSourceにバインドして表示しています。

    SqlDataSource でのクエリやレコードの抽出方法などは無駄がないようにきちんと考えてますでしょうか?

    > 単純な select * from t_test

    だとすると、それが投げられるたび 20 万件取得してくると思いますけど、それを表示してもユーザーが 20 万件のレコードを見るなんてことはないと思いますから無駄なことをしているのでは?

    GridView などと組み合わせて、ソートとかページングをしていると、ソートするたび、ページングするたび 20 万件取得してくるはずですが、そのあたり考えてますでしょうか?

    2018年6月27日 9:35
  • 単純な select * from t_test というクエリでも遅い為、サーバーのスペック不足なのかもしれませんが・・・

    「スペック不足」というあいまいな判断をすべきではありません。利用状況モニター等でパフォーマンスを確認し、CPU / Memory / Storage / Network、いずれが原因となっているかを特定すべきです。

    例えば、(実際のデータよりもはるかに多い)大量のディスクアクセスが発生している場合、データベースファイルの問題を疑います。インデックスの断片化により非効率なディスクアクセスが強要されればパフォーマンスが低下するのも当然です。その場合は一次的には断片化の解消を行うとともに、恒久的にはテーブルの更新方法を改善すべきです。

    ともあれ、原因調査を行えるのは質問者さんだけであることをご認識ください。

    2018年6月27日 9:36
  • SQLServer Management Studioもアプリケーションですから、大量のデータを処理するには時間がかかることもあると思います。
    SQLCMDで20万件を表示するだけでも結構な時間がかかると思います。
    よって、全件抽出が遅いからと言って、必ずしもテーブル設計やサーバー能力が足らないと判断することはできないと思います。

    ROW_NUMBERを使わないページング方法を試されると良いかもしれません。
    test.dとtest.aの仕様がわからないので適当ですが、

    select top(30) *
    from t_test
    where test.d  <= @現在表示中の最大のtest.d and
               (test.d * 100000 + test.a) > @現在表示中の最大の『test.d * 100000 + test.a』
    order by test.d desc, test.a

    #どの程度、オプティマイザーががんばってくれるかどうか・・・


    ★良い回答には質問者は回答済みマークを、閲覧者は投票を!



    • 編集済み trapemiya 2018年6月27日 10:35 SQL修正
    2018年6月27日 10:28
  • 早速やってみました。
    結果、半分以下の2秒程度になりました!
    (元のSQL文から「COUNT(*) OVER() AS RowCnt」を無くした場合と同じ時間となりました。)

    --件数はここで個別にとりましょう
    	,(select COUNT(*) from t_test) as RowCnt

    COUNT集計は、このように単体のSELECTでやったほうが早いのでしょうか?

    (実際はこれに条件が加わることになるため、COUNTのSELECT文にもWHERE句が入りますが。)

    set statistics time on
    (select COUNT(*) from t_test)

    ↑こちらの結果は

    SQL Server の構文解析とコンパイルの時間: 
     CPU 時間 = 0 ミリ秒、経過時間 = 7 ミリ秒。
    
     SQL Server 実行時間: 
    、CPU 時間 = 0 ミリ秒、経過時間 = 0 ミリ秒。
    
    (1 行処理されました)
    
     SQL Server 実行時間: 
    、CPU 時間 = 611 ミリ秒、経過時間 = 149 ミリ秒。
    

    となりました。

    また、
    select * from t_test
    については以下の通りでした。(18万件で30秒ぐらいでした。)
    SQL Server の構文解析とコンパイルの時間: 
     CPU 時間 = 0 ミリ秒、経過時間 = 2 ミリ秒。
    
     SQL Server 実行時間: 
    、CPU 時間 = 0 ミリ秒、経過時間 = 0 ミリ秒。
    
    (178432 行処理されました)
    
     SQL Server 実行時間: 
    、CPU 時間 = 7578 ミリ秒、経過時間 = 30137 ミリ秒。
    ※いずれもSQLServerManagementStudioでの実行です。

    また、ちょっと話がずれますが、
    そもそもSQLServer2008でページングをする為にROW_NUMBERを使用してますが
    2012にアップグレードしてOFFSETを使用したほうが速度的には速いのでしょうか?

    2018年6月27日 10:32
  • きゅうきゅうさん

    お試しいただきありがとうございます。2秒!遅いですね、、思い描いた実行プランで実行されていれば200ミリ秒くらいで完了すると思っていたのですが、、、

    select * from t_test については、いったん考えなくて大丈夫だと思います(アプリ側で実際に実行するクエリではないと思いますので)

    >COUNT集計は、このように単体のSELECTでやったほうが早いのでしょうか?

    すべては実行プランによるため、一概にはいえません。また、きゅうきゅうきゅうさんの環境で試せないため、COUNTを切り離して考えたほうがチューニングしやすかったというのもあります。

    個人的にはまだまだ劇的に速くできるのではと思っています。実行プランを貼っていただくこと可能でしょうか?

    >そもそもSQLServer2008でページングをする為にROW_NUMBERを使用してますが
    >2012にアップグレードしてOFFSETを使用したほうが速度的には速いのでしょうか?

    これもすべては実行プランがどうなるか次第ですね、、OFFSETの方がオプティマイザが高速なクエリを作ってくれやすい、とかはあるかもしれませんが、ROW_NUMBERでまだまだ速くできるはずです


    >(実際はこれに条件が加わることになるため、COUNTのSELECT文にもWHERE句が入りますが。)

    もしや実際に実行するクエリが少し違いますか、、?
    実際に実行しているクエリをフルでもらえればもっとちゃんとチューニングできると思います。

    ※参考になった場合は、ぽちっと「投票」や「回答としてマーク」もお願いいたします。


    2018年6月27日 10:39
  • ページングするなら自己流ではなく ObjectDataSource に備わっている機能を利用できる形でクエリを書いてはいかがですか?

    COUNT (*) は別のクエリで抽出するようにできます。

    詳しくは以下の記事を見てください。

    ObjectDataSource でページング
    http://surferonwww.info/BlogEngine/post/2010/08/26/Paging-with-ObjectDataSource.aspx

    ストアド + ADO.NET + Repeater + 独自ページャーを使う方法もあります。

    カスタムページャー
    http://surferonwww.info/BlogEngine/post/2010/08/25/Custom-pager.aspx


    • 編集済み SurferOnWww 2018年6月28日 9:54 Typo 訂正
    2018年6月28日 9:51
  • すみません。
    別件でバタバタしており、確認が遅くなりました。。。
    たくさんの方にアドバイス頂き、大変助かります。

    > 佐祐理 様
    アプリ側で文字コードを変換するような処理は入れていないです。
    また、単純に「スペック不足」と言ってしまいましたが、
    仰る通り、ディスクなのかメモリなのかCPUなのか、
    遅いクエリ自体は分かっているので、それを実行したときの「なぜ遅いか」の分析方法となるかと思います。
    (やはり「実際の実行プラン」というものを参考にする形なのでしょうか。)

    > SurferOnWww 様
    現状は、Repeater+SqlDataSource+PagedDataSourceという形で画面表示とページングに
    対応していましたが、SqlDataSourceはページングに関わらず全件取得する為、遅い。
    ということが分かったため、表示する分だけ取得するSQL文にして、
    ページングを自前で実装しようと考えました。
    そこで、ROW_NUMBERで表示文取得するSQL文を作成しましたが、
    SQLServerManagementStudio上で実行したところ、それがあまりにも遅かった為、
    表示の仕組みの前にそれを改善しようと考えた次第です。
    (試しに表示の仕組みを変えず、SqlDataSourceに30件取得のROW_NUMBERのSQLを実装したところ
     SQLServerManagementStudio上のクエリ実行と、サイトのページ表示の時間がほぼ同じでしたので
     このクエリを速くしないとページ表示も速くならないと思いました。)

    表示の仕組みについては、速度改善後、ぜひ参考にさせて頂こうと思います。
    (ObjectDataSourceのほうが一般的なんでしょうか?)

    > trapemiya 様
    なるほど。こういう書き方もあるのですね。
    こちらのSQL文、まだ検証できてませんが、一度試して速度比較してみようと思います。
    検証できたらまたお返事します。

    > maaaaaaaa8 様
    実際に実行するクエリは確かに違います。
    記載した形でも遅かったので、その状態で書いてしまいました。
    JOIN句やWhere句にいくつかのパターンがあるので、載せられるクエリを準備してみますが、
    基本はインデックスの貼り方でしょうか?
    インデックスの貼り方も勉強中ではありますが、
    varchar(max)のフィールドの曖昧検索などもあり、どうインデックスを貼れば良いか調べている状態です。
    「付加列を含む非クラスター化インデックス」というのも試したのですが、
    いまいち効果が感じられず。。
    フルテキストインデックスを作成してCONTAINSを使うも、
    SQLServerManagementStudio上では動くのですが、ASP.NET経由ですとCONTAINSが使えないというエラーになります。



    現在、分からないことが多すぎて、改善に向けた調査の手順を模索中ですが、
    もしチューニングの情報サイト等ありましたら教えて頂けると助かります。


    2018年7月3日 4:27
  • > (ObjectDataSourceのほうが一般的なんでしょうか?)

    どちらが一般的という話ではなくて、適材適所ということになると思います。

    ObjectDataSource を使うとビジネスロジックを分離して、プレゼンテーション層、ビジネスロジック層、データ層の 3 層構造にできるというメリットがあります。なので、私が紹介した前者の記事のような実装が可能になりますし、そのための機能も ObjectDataSource には備わっています。

    3 層構造とかは全く必要なく、2 層構造で何の問題もないのでお手軽に済ませたい場合は SqlDataSource という選択になると思います。

    2018年7月3日 5:38
  • きゅうきゅうきゅうさん

    実行するクエリによって、チューニングパターンは変わってきます。クエリが少しでも違えば、僕が意図していたチューニング効果がでていない可能性が非常に高いです。

    インデックスの張り方も大事ですが、一番大事なのはIOを減らすという意識です。そのためには実行プランの理解も必要になってきます。

    実際のクエリはいくつかのパターンがあるということで、リクエストされたURLによってクエリを動的に構築して実行するような検索機能を実装されているのではないかと推測しています。

    かなりシンプルなクエリであれば、マネジメントスタジオでサジェストされたインデックスを作成すればよいのですが、今回のようにいくつかのパターンがあるクエリに最適なインデックス設計とクエリチューニングというのは、SQLServerのインデックス構造や、SQLの一般的な概念であるカーディナリティや選択度といった概念の理解が必要になってきます。

    チューニングでおすすめの書籍としては、

    http://sql-performance-explained.jp/

    こちらです。SQLServer特化ではなく、どのDB製品でも通用するであるチューニングテクニックが身につくと思います。

    また、SQLServerのインデックス構造の理解には自習書がおすすめです。

    https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0ahUKEwim47a_qYLcAhWXUd4KHep6DdMQFggpMAA&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F5%2FA%2F2%2F5A29D047-2D83-4ACD-919F-4614847E8392%2FSQL11_SelfLearning15_Index.pdf&usg=AOvVaw0Bz5YU5_0CMm9BNbdBHmmg

    断片化のところ等は読み飛ばして構わないので、インデックス構造だけでもチェックしてみてください。

    具体的なクエリを貼っていただければ、ある程度具体的なアドバイスはできると思います。(データ分布までは把握できないので、あくまで推測になってしまいますが、、)

    2018年7月3日 6:44