none
SQLServer 2017 Express on linuxで処理が遅い RRS feed

  • 質問

  • お世話になります。

    現在SQLServer2017 Express on linuxでvb.netアプリケーションの動作テストを行っています。

    linux はubuntu16.04LTSを使用しています。

    列は2列で5000行のテーブルに対してループを行い、1行ごとに主キーでSELECT文を発行して終了までの時間を測定しました。

    結果は35秒でした。

    同じ処理をpostgresで行うと1.6秒ほどで終わってしまいます。

    また、virtualbox上のwindows2000サーバー+SQLServer2000で同様の処理を行っても6秒ほどで終わります。

    なぜSQLServer2017 Express on linuxがこんなに処理に時間がかかるのかいろいろ調べているのですがよくわかりません。

    なにかヒントをいただけないでしょうか?よろしくお願いします。

    2018年9月5日 10:53

回答

  • 調べたことを共有します。

    ちょっと英語が読めないと厳しい(または Google 翻訳、Bing 翻訳、等々を使いながら)のと、これが最適解として妥当かどうかの検証が必要になります。そして、実験環境が準備できず試せていません。ごめんなさい。
    (追記)日本語版ページを見つけましたので、合わせて載せておきます。


    ■MSSQL on Centos is slow
    https://community.spiceworks.com/topic/2057009-mssql-on-centos-is-slow
    今現在で一番最後の方の投稿で以下が記載されていました。

    -- 引用ここから(和訳後)
    Linux上のSQL Serverのパフォーマンス機能に関するウォークスルーを参照してください。
    https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-performance-get-started
    メモリ最適化テーブルを使用してSQLサーバーのパフォーマンスを向上させました。 これで、Linuxサーバーは本番環境にリリースされます。 手伝ってくれてありがとう。
    -- 引用ここまで

    (追記)これの日本語版ページが以下です。
    ■SQL Server on Linux のパフォーマンス機能のチュートリアル
    https://docs.microsoft.com/ja-jp/sql/linux/sql-server-linux-performance-get-started?view=sql-server-2017



    また、以下の2つは、対策方法は未記載だった気がしますが、他の方も遅いことに困っていらっしゃるみたいでした。

    ■Toooooo slow interacting with MS sql server under Ubuntu 16.04 (both using EF or SQLDataReader)
    https://github.com/dotnet/corefx/issues/24480
    さらっと流し読みした感じでは、やり取りのみ

    ■SQL Server 4 times slow on Oracle Linux 7.4 (RHEL)
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ce546c5c-0b7e-4a27-aeeb-fdccd3c46e0f/sql-server-4-times-slow-on-oracle-linux-74-rhel?forum=sqlgetstarted
    さらっと流し読みした感じでは、やり取りのみ


    • 編集済み sutefu7 2018年9月5日 14:21 日本語版ページの追加
    • 回答としてマーク べっち 2018年9月6日 2:01
    2018年9月5日 13:22
  • 結果として、vb6+ADOの際に接続文字列を以下のようにすると劇的に速くなりました。

    Provider=SQLNCLI11.1;DataTypeCompatibility=80;MARS Connection=True;Password=[パスワード];Persist Security Info=True;User ID=[ユーザー名];Initial Catalog=[DB名];Data Source=[IPアドレス,1433]

    元の接続文字列との違い

    Provider=SQLNCLI11.1; <- Provider=SQLOLEDBから変更

    DataTypeCompatibility=80; <- 追加

    MARS Connection=True; <- 追加

    新しいSQLサーバーに対応したプロバイダー名を指定しないとダメだったということでしょうか。

    とりあえずこれで内部の動作に問題がないかテストしてみます。

    色々とわからなくて、結果的にスレッドのタイトルと内容がそぐわないものになってしまって申し訳ないです。

    ヒントをくださった皆様本当にありがとうございました。


    • 回答としてマーク べっち 2018年9月7日 4:29
    • 編集済み べっち 2018年9月7日 4:30
    2018年9月7日 4:18
  • SQL Serverを使うのであれば、わざわざ汎用的なOleDbConnectionを使うのではなく、SQL Serverに特化したSqlConnectionを使うべきでしょう。

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

    • 回答としてマーク べっち 2018年9月7日 1:25
    2018年9月6日 9:29

すべての返信

  • 調べたことを共有します。

    ちょっと英語が読めないと厳しい(または Google 翻訳、Bing 翻訳、等々を使いながら)のと、これが最適解として妥当かどうかの検証が必要になります。そして、実験環境が準備できず試せていません。ごめんなさい。
    (追記)日本語版ページを見つけましたので、合わせて載せておきます。


    ■MSSQL on Centos is slow
    https://community.spiceworks.com/topic/2057009-mssql-on-centos-is-slow
    今現在で一番最後の方の投稿で以下が記載されていました。

    -- 引用ここから(和訳後)
    Linux上のSQL Serverのパフォーマンス機能に関するウォークスルーを参照してください。
    https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-performance-get-started
    メモリ最適化テーブルを使用してSQLサーバーのパフォーマンスを向上させました。 これで、Linuxサーバーは本番環境にリリースされます。 手伝ってくれてありがとう。
    -- 引用ここまで

    (追記)これの日本語版ページが以下です。
    ■SQL Server on Linux のパフォーマンス機能のチュートリアル
    https://docs.microsoft.com/ja-jp/sql/linux/sql-server-linux-performance-get-started?view=sql-server-2017



    また、以下の2つは、対策方法は未記載だった気がしますが、他の方も遅いことに困っていらっしゃるみたいでした。

    ■Toooooo slow interacting with MS sql server under Ubuntu 16.04 (both using EF or SQLDataReader)
    https://github.com/dotnet/corefx/issues/24480
    さらっと流し読みした感じでは、やり取りのみ

    ■SQL Server 4 times slow on Oracle Linux 7.4 (RHEL)
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ce546c5c-0b7e-4a27-aeeb-fdccd3c46e0f/sql-server-4-times-slow-on-oracle-linux-74-rhel?forum=sqlgetstarted
    さらっと流し読みした感じでは、やり取りのみ


    • 編集済み sutefu7 2018年9月5日 14:21 日本語版ページの追加
    • 回答としてマーク べっち 2018年9月6日 2:01
    2018年9月5日 13:22
  • 5000行のテーブルに対してループを行い、1行ごとに主キーでSELECT文を発行
    つまりSQL文5000回の実行が遅いと言っているのでしょうか? そうであれば、そもそも使い方が間違っています。可能な限り1回で済ませるのがSQL言語です。
    2018年9月5日 18:51
  • 回答ありがとうございます。

    レスポンスのテストをしているだけで、確かに本来はこのような使い方はしませんね。

    2018年9月6日 0:09
  • 回答ありがとうございます。

    ベストプラクティスも設定しているのですが、やはり改善しませんね。CPU使用率が75%になっています。同一サーバー上に立ち上げているpostgresにアクセスした場合はほとんどCPU使用率はあがりません。sqlserverの文字コードの設定関係なのか?

    なにか無駄な処理が動いているのかな?

    2018年9月6日 0:34
  • SQL Server 2017はLinuxの方がパフォーマンスが良いというデータもあります。
    とりあえず、以下を参考にして何か負荷をかけている機能を探してみると良いかもしれません。

    SQLServer: CPU負荷をかけている機能を見つけ出す
    https://qiita.com/maaaaaaaa/items/730ede5934ff843f5fc2

    (参考)
    Linux版Microsoft SQL Server 2017がベンチでWindows版を上回る理由 - 日本マイクロソフトが最新SQL Server 2017を紹介
    https://news.mynavi.jp/article/20171026-mssqlserver2017/


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

    • 回答としてマーク べっち 2018年9月6日 2:01
    • 回答としてマークされていない べっち 2018年9月6日 9:09
    2018年9月6日 1:01
  • SQLプロファイラで確認したところ、それぞれのSQLはほぼ瞬間的に終わっているので、SELECTに時間がかかっているのではなくて、その後のクライアントとの通信で時間(CPU使用)がかかっているような気配です。引き続き調査します。
    2018年9月6日 2:59
  • 調査したところ、.netアプリからアクセスする際に、SqlConnectionを使用すると1000件ループでselectを発行した場合のレスポンスが0.5秒でOleDbConnectionを使用すると30秒ということがわかりました。

    OleDbConnectionを使っているということ自体が間違いということでしょうか?

    2018年9月6日 8:22
  • SQL Serverを使うのであれば、わざわざ汎用的なOleDbConnectionを使うのではなく、SQL Serverに特化したSqlConnectionを使うべきでしょう。

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

    • 回答としてマーク べっち 2018年9月7日 1:25
    2018年9月6日 9:29
  • ありがとうございます。

    やはりSQL Serverを使うのであればSqlConnectionを使うべきですよね。

    ただ、古いVB6で作ってあるアプリケーションが事情(開発元倒産により仕方なく救済のために弊社がソースごと引き継いだ。しかしDBサーバーは老朽化のため更新した)によりまだ運用中で、それがADOを使用していてOleDb接続なのです。そのうち.Netアプリでリプレイスするのですが現時点でどうにかOLEDB接続でもう少しレスポンスがあがらないものかと調査中です。単発の操作ならさほど問題にならないけどバッチ処理になると、塵も積もれば山となるみたいな感じで猛烈に遅くなるので困っております。VB6アプリを変更せずにOLEDBドライバと接続文字列を変更するぐらいでどうにか対応できないものかと。

    ご存知の方いらっしゃいましたら教えてください。

    よろしくお願いいたします。

    2018年9月7日 1:34
  • 元質問文はVB.NETでの接続となっていましたが、このコメントでは「VB6アプリを変更せずに」となっています。先に私が回答したのもパフォーマンス測定の観点がそもそも間違っていないかを指摘しましたが、質問の前提が全く異なっていませんか?

    確認ですが、既存のVB6アプリは質問文に書かれているような大量のSELECTを繰り返すものですか? その前提で、DB変更による速度低下を抑制したい、というのが質問の趣旨なのですか?

    2018年9月7日 1:42
  • VB6アプリを変更せずにOLEDBドライバと接続文字列を変更するぐらいでどうにか対応できないものかと。

    接続文字列は Provider=SQLOLEDB となっていますよね? もし Provider=MSDASQL となっているとOLE DBからODBC経由をしてしまうため効率が悪くなっています。

    Microsoft OLE DB Provider for SQL Server の概要がSQL Server 2017向けのドキュメントとなっています。機械翻訳で肝心のパラメーターまで翻訳されてしまっているので、適宜、右上の「英語で読む」を併用されるといいかと。他のページ例えばhttps://msdn.microsoft.com/ja-jp/library/cc426831.aspxなどは日本語ではありますが古いドキュメントであり、最新のパラメーターが記述されていないなど参考にならない部分もあります。

    Packet Sizeの調整でパフォーマンス向上の余地があるかもしれませんね。

    2018年9月7日 2:04
  • すみません。質問に言葉が足りませんでしたね。最初はlinux on SQLServerが遅いと思い込んでいたのですが、調べていくうちにそうではなくて、oledbconnectionだと遅いということがわかってきたということです。vb.netでもvb6でもoledbだと遅いので。

    また、アプリ内部で大量のSELECT文を発行する部分があり、もちろん元々の開発会社が何故こんなプログラムを書いたのか疑問ではありますが、単発のSELECT文を発行するにしてもSqlConnectionとOleDbConnectionではレスポンスに極端な差がありますので、実行ファイル自体に手を加えずにドライバとか接続文字列で速度低下を抑制できれば良いかなと。

    2018年9月7日 2:25
  • ありがとうございます。packetsizeも試したのですが変化ないですね。

    vb6アプリはADOを使用していて Provider=SQLOLEDB になっています。

    ちなみにSELECT文一件のレスポンスは

    VB6+ADO(Provider=SQLOLEDB) -> virtualbox上のwindows2000サーバー+SQLServer2000:0.01秒

    VB6+ADO(Provider=SQLOLEDB) -> ubuntu16.04LTS+SQLServer2017:0.04秒

    vb.net+SqlConnection -> ubuntu16.04LTS+SQLServer2017:0.01秒

    vb.net+OleDbConnection -> ubuntu16.04LTS+SQLServer2017:0.04秒

    となったのでOLEDBだと4倍遅いですね。

    運用中の現場でもバッチ処理は4倍時間がかかっています。時間はかかるけど正常に終わります。

    「この現象に注力するよりプログラム直せよ!」というのは承知のうえです。正直、質問することすら恥ずかしい。

    実際にまだVB6アプリを捨てきれないけどDBサーバーは更新するという場面はゼロではないと思うので、

    私以外の方の知見にもなればと思います。

    VB6アプリを変更せずにOLEDBドライバと接続文字列を変更するぐらいで対応できないか引き続き調査します。

    2018年9月7日 2:48
  • 結果として、vb6+ADOの際に接続文字列を以下のようにすると劇的に速くなりました。

    Provider=SQLNCLI11.1;DataTypeCompatibility=80;MARS Connection=True;Password=[パスワード];Persist Security Info=True;User ID=[ユーザー名];Initial Catalog=[DB名];Data Source=[IPアドレス,1433]

    元の接続文字列との違い

    Provider=SQLNCLI11.1; <- Provider=SQLOLEDBから変更

    DataTypeCompatibility=80; <- 追加

    MARS Connection=True; <- 追加

    新しいSQLサーバーに対応したプロバイダー名を指定しないとダメだったということでしょうか。

    とりあえずこれで内部の動作に問題がないかテストしてみます。

    色々とわからなくて、結果的にスレッドのタイトルと内容がそぐわないものになってしまって申し訳ないです。

    ヒントをくださった皆様本当にありがとうございました。


    • 回答としてマーク べっち 2018年9月7日 4:29
    • 編集済み べっち 2018年9月7日 4:30
    2018年9月7日 4:18