SQLServer 2017 Express on linuxで処理が遅い

Question

お世話になります。

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

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

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

結果は３５秒でした。

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

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

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

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

Answer 1

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

ちょっと英語が読めないと厳しい（または 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



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

■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
さらっと流し読みした感じでは、やり取りのみ

Answer 2

5000行のテーブルに対してループを行い、１行ごとに主キーでSELECT文を発行

つまりSQL文5000回の実行が遅いと言っているのでしょうか？ そうであれば、そもそも使い方が間違っています。可能な限り1回で済ませるのがSQL言語です。

Answer 3

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

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

Answer 4

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

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

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

Answer 5

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/

---

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

Answer 6

SQLプロファイラで確認したところ、それぞれのSQLはほぼ瞬間的に終わっているので、SELECTに時間がかかっているのではなくて、その後のクライアントとの通信で時間（CPU使用）がかかっているような気配です。引き続き調査します。

Answer 7

調査したところ、.netアプリからアクセスする際に、SqlConnectionを使用すると1000件ループでselectを発行した場合のレスポンスが0.5秒でOleDbConnectionを使用すると30秒ということがわかりました。

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

Answer 8

SQL Serverを使うのであれば、わざわざ汎用的なOleDbConnectionを使うのではなく、SQL Serverに特化したSqlConnectionを使うべきでしょう。

---

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

Answer 9

ありがとうございます。

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

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

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

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

Answer 10

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

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

Answer 11

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の調整でパフォーマンス向上の余地があるかもしれませんね。

Answer 12

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

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

Answer 13

ありがとうございます。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だと４倍遅いですね。

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

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

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

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

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

Answer 14

結果として、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サーバーに対応したプロバイダー名を指定しないとダメだったということでしょうか。

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

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

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