SQL ServerでManagement Studioでは速いのにWEBアプリでのみSQLの実行が遅い場合に確認すること。

ついこないだSQL Serverのユーザファンクションの性能問題について書きましたが、実はそのあとも性能問題が発生しました。

final.hateblo.jp

Javaを使ったWEBアプリで検索処理が20secくらいかかるという問題が発生しました。

SQLが悪いと疑ってSQLログからSQLを抽出し、SQLをManagement Studioで実行しましたが、結果は2secで返ってきて問題はありませんでした。

ググるといろいろ実行計画のキャッシュを削除すればいいとかいろいろ試したのですが、さっぱりでした。

困ったときはプロファイラということでプロファイラを実行したあとにWEBアプリを実行すると以下のようなSQLが実行されていることがわかりました。

declare @p1 int
set @p1=10
exec sp_prepare @p1 output,N'@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000)'
,N'SELECT * FROM SCHEDULE WITH (NOLOCK) WHERE RoomNo = @P0  AND STATUS =  @P1)',1
select @p1

使っているWEBアプリはHibernateを使っていましたが、普通のSQLのSQLが実行されていると思っていたので驚きました。

それでプロファイラから抽出したSQL(@p1=nullに変更する必要があります)をManagement Studioで実行したのですが、結果は2secで返ってきました。

WEBアプリが実行されているのを忠実に再現してみようと考えて、eclipseのDBViewrから抽出したSQLを実行したところ、20secで返るようになり、再現しました。

JDBCについて調べたところ、どうやらSQLServerのJDBCドライバはデフォルトでUnicodeで処理されるようです。

SQL ServerではvarcharがSJIS、nvarcharがUnicodeなので、SQLがnvarcharとして実行されており、テーブルのカラムの型がvarcharだったので暗黙的な変換が行われるため、遅くなっているということがわかりました。

いくら暗黙的変換と言えども遅すぎな気はしますが。

対策として以下の２つが考えられます。

• SQLのほうでキャスト（キャスト処理に若干時間がかかるが、暗黙的変換の比ではない）
• JDBCの接続プロパティ「sendStringParametersAsUnicode」をfalseにする。（デフォルトはtrue）

接続プロパティについては以下を参照。
接続プロパティの設定 | Microsoft Docs

近年、Unicode対応が進んでいるので、デフォルト値としては当然かもしれませんが、昔からある大企業の基幹システムなどホストと連携したりしているシステムは いまだにSJISで固められていたりします。(規約でnvarcharの使用禁止されている)
今回もそんなシステムでした。
なので接続プロパティを変更する方法で対応しました。
nvarcharがほとんどで一部のテーブルだけSJISになっているのであればキャストで対応ですね。

この問題、わりとメジャーっぽいんですが、SQL ServerでしかもSJISで縛られているシステムを開発したことなかったのでわかりませんでした。

また、開発環境ではデータベースの構築時の設定（詳しくは調べてないですが、照合順序かと）が検証環境と異なっていたらしくこの問題が起きていませんでした。

今回の事象はSQL Serverのみで起こる問題でしたが、
SQLの性能を調査する際は、プロファイラで実行されているSQLを抽出し、WEBアプリがJDBC接続だったらJDBC接続で確認するなど接続方法、データベース設定を発生する環境と同じにして再現確認するのが重要！と実感しました。