【SQLServer】【java】jdbcからSQLを実行した場合のSQLステートメントと実行プラン

jdbcからSQLを実行した場合、SQLserver側でどのようなステートメント・実行プランとして現れるのかまとめてみました。

接続プロパティSelectMethodが未指定の場合、もしくはdirectの場合

まずはSelectMethodが未指定の場合で確かめてみます。
デフォルトがdirectであるため、directでも同様の動きとなるかと。

Statementクラスによる実行

import java.sql.Connection;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class SQLserver_Statement_test {
    public static void main(String[] args) {
        try{
            Driver d = (Driver)Class.forName(
            "com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
            String connUrl = "jdbc:sqlserver://YUUSUKE-VAIO\\INS_NISHI2016;" +
                    "database=Param_testDB;integratedSecurity=false;user=sa;password=system;";
            Connection conn = d.connect(connUrl, new Properties());
            
            Statement stmt = conn.createStatement();
            String SQL = "SELECT * FROM [dbo].[Param_test] " + 
                         "WHERE col2 = 500000 "+
                         "/*test_sql_Statement*/";            
            ResultSet rs = stmt.executeQuery(SQL);
            
            while(rs.next()){
            System.out.println(rs.getString("col1"));
            }
            rs.close();
            stmt.close();
            
        }
        catch(Exception e){
                e.printStackTrace();
                }
    }    
}

java.sql.statementによる実行の場合、SQLserver側では簡易パラメータ化されていなければAdhocクエリとなります。

PreparedStatementクラスによる実行

import java.sql.Connection;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.Properties;

public class SQLserver_Prepared_Statement_test {
    public static void main(String[] args) {
        try{
            Driver d = (Driver)Class.forName(
            "com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
            String connUrl = "jdbc:sqlserver://YUUSUKE-VAIO\\INS_NISHI2016;" +
                             "database=Param_testDB;integratedSecurity=false;user=sa;password=system;";
            Connection conn = d.connect(connUrl, new Properties());
            
            String SQL = "SELECT * FROM [dbo].[Param_test] " + 
                         "WHERE col2 = ?"+
                         "/*test_sql_Prepared_Statement*/";
            PreparedStatement pstmt = conn.prepareStatement(SQL);
            pstmt.setInt(1, 500002);
            ResultSet rs = pstmt.executeQuery();
            
            while(rs.next()){
            System.out.println(rs.getString("col2"));
            }
            rs.close();
            pstmt.close();
            
        }
        catch(Exception e){
                e.printStackTrace();
                }
    }    
}

java.sql.Preparedstatementによる実行の場合、SQLserver側ではpreparedクエリとなります。
実行プランを作成する際には、バインド変数により実行プランを作成します。パラメータスニッフィングが有効であれば、プラン作成時のパラメータで最適化される実行プランが次回以降再利用されることになりますので、実行プラン作成時のパラメータには注意が必要です。
（例えば検索画面などで曖昧検索や未入力による全件検索が発生しうる場合など）

これは以前もまとめておりますので、詳細は以下を参照ください。
【SQL server】パラメータスニッフィングによる実行プランのパフォーマンス低下 - 小物SEのメモ帳

実際にSQLserver側でステートメントを確認してみると、バインド変数のままステートメントが実行されていることがわかるかと思います。
ただし接続プロパティSendStringParametersAsUnicodeを指定していない場合、デフォルトでは文字列パラメータはNVARCHARとして実行されるため、VARCHARが存在する場合暗黙型変換が発生します。
この場合、同プロパティをfalseとして、NVARCHAR型のカラムにはNプレフィックスを付与するなどする必要があります。
Microsoftのblogにも記載があり、ありがちなミスであるため、データ型が正しいか確認しておくべきでしょう。
DO’s&DONT’s #2: 絶対にやらなければいけないこと – データ型を一致させる – Microsoft SQL Server Japan Support Team Blog

接続プロパティSelectMethodがcursorの場合

次にSelectMethodがcursorの場合で確かめてみます。

このときSQLserver側ではサーバサイドのカーソルが要求されることになります。
カーソル処理は、パフォーマンスがよくないことがありますので、使う際はよく検討しましょう。
まとめてデータを取得可能であればカーソルを使用しない方が処理効率はよいかと。

Statementクラスによる実行

import java.sql.Connection;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class SQLserver_Statement_cursor_test {
   public static void main(String[] args) {
        try{
            Driver d = (Driver)Class.forName(
            "com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
            String connUrl = "jdbc:sqlserver://YUUSUKE-VAIO\\INS_NISHI2016;" +
                    "database=Param_testDB;integratedSecurity=false;user=sa;password=system;SelectMethod=cursor";
            Connection conn = d.connect(connUrl, new Properties());
            
            Statement stmt = conn.createStatement();
            String SQL = "SELECT * FROM [dbo].[Param_test] " + 
                         "WHERE col2 = 500001 "+
                         "/*test_sql_Statement_cursor*/";            
            ResultSet rs = stmt.executeQuery(SQL);
            
            while(rs.next()){
            System.out.println(rs.getString("col1"));
            }
            rs.close();
            stmt.close();
            
        }
        catch(Exception e){
                e.printStackTrace();
                }
    }    
}

基本的には接続プロパティSelectMethodが未指定の場合と同様ですが、次レコードを参照するためのカーソル処理”FETCH API_CURSOR…”がステートメントに、実行プランに”FETCH CURSOR”が追加されています。

なぜかこの場合プランがうまく表示できませんでした…
XML表示で確認すると内部まで確認できます。

PreparedStatementクラスによる実行

import java.sql.Connection;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.Properties;

public class SQLserver_Prepared_Statement_cursor_test {
    public static void main(String[] args) {    
       try{
            Driver d = (Driver)Class.forName(
            "com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
            String connUrl = "jdbc:sqlserver://YUUSUKE-VAIO\\INS_NISHI2016;" +
                             "database=Param_testDB;integratedSecurity=false;user=sa;password=system;SelectMethod=cursor";
            Connection conn = d.connect(connUrl, new Properties());
            
            String SQL = "SELECT * FROM [dbo].[Param_test] " + 
                         "WHERE col2 = ? " +
                         "/*test_sql_Prepared_Statement_cursor*/";
            PreparedStatement pstmt = conn.prepareStatement(SQL);
            pstmt.setInt(1, 500003);
            ResultSet rs = pstmt.executeQuery();
            
            while(rs.next()){
            System.out.println(rs.getString("col2"));
            }
            rs.close();
            pstmt.close();
            
        }
        catch(Exception e){
                e.printStackTrace();
                }
    }    
}

こちらも基本的には接続プロパティSelectMethodが未指定の場合と同様で、次レコードを参照しFETCHしていく処理が追加されています。

ただし次のようなカーソルの処理対象データが外部結合等で大きくなる場合、中間データがtempdbのCWT_PrimaryKeyに保存されることがあるため、多数のカーソル処理が集中するとtempdbへのIOが集中しボトルネックとなることがあります。

import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class SQLserver_Prepared_Statement_cursors_test {
    public static void main(String[] args) {    
       try{
            Driver d = (Driver)Class.forName(
            "com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
            String connUrl = "jdbc:sqlserver://YUUSUKE-VAIO\\INS_NISHI2016;" +
                             "database=Param_testDB;integratedSecurity=false;user=sa;password=system;SelectMethod=cursor";
            Connection conn = d.connect(connUrl, new Properties());
            
            String SQL = "SELECT [param].[col1] FROM [dbo].[Param_test] AS [param] " + 
                         "LEFT OUTER JOIN  [Param_testDB].[dbo].[Parallel_test] AS [parallel] " +
                         "        ON  [param].[col2] = [parallel].[col2] " +
                         "WHERE [param].[col2] BETWEEN ? " +
                         "                         AND ? " + 
                         "ORDER BY 1 " +
                         "/*test_sql_Prepared_Statement_cursor*/";
            PreparedStatement pstmt = conn.prepareStatement(SQL);
            pstmt.setInt(1, 500000);
            pstmt.setInt(2, 500003);
            
            ResultSet rs = pstmt.executeQuery();
            
            while(rs.next()){
            System.out.println(rs.getString("col1"));
            }
            rs.close();
            pstmt.close();
            
        }
        catch(Exception e){
                e.printStackTrace();
                }
    }    
}

このあたりは静的カーソルについて前回まとめておりますので、そちらをご確認いただければ。
【SQL Server】tempdbのCWT_PrimaryKeyとは - 小物SEのメモ帳

java.sql.CallableStatementによるストアドの実行

java.sql.CallableStatementによる実行は、IN/OUTパラメータを伴うストアドプロシージャの呼び出しに使います。

USE [Param_testDB]
GO
DROP PROCEDURE IF EXISTS [dbo].[usp_stored_procedure_test]
GO

CREATE PROCEDURE [dbo].[usp_stored_procedure_test]
	@input_param INT,
	@output_param INT OUT
AS
BEGIN
	SET NOCOUNT ON;
	SELECT @output_param = col2 
	FROM [dbo].[Param_test] 
    WHERE col2 = @input_param
RETURN
END  
GO

import java.sql.Connection;
import java.sql.Driver;
import java.sql.CallableStatement;
import java.sql.Types;
import java.util.Properties;

public class SQLserver_Callable_Statement_test {
   public static void main(String[] args) {
        try{
            Driver d = (Driver)Class.forName(
            "com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
            String connUrl = "jdbc:sqlserver://YUUSUKE-VAIO\\INS_NISHI2016;" +
                    "database=Param_testDB;integratedSecurity=false;user=sa;password=system";
            Connection conn = d.connect(connUrl, new Properties());
            
            String SQL = "{call dbo.usp_stored_procedure_test(?,?)}";
            
            CallableStatement cstmt = conn.prepareCall(SQL);
            cstmt.setInt(1,500004);
            cstmt.registerOutParameter(2, Types.INTEGER);
            cstmt.execute();
            
            System.out.println(cstmt.getInt(2));
            cstmt.close();
            
        }
        catch(Exception e){
                e.printStackTrace();
                }
    }    
}

SQLserver側からすると保存されているストアドプロシージャが呼び出されて実行されています。
SQLテキストとしては"CREATE PROCEDURE…"から始まっていることも特徴となるかと。

ストアドプロシージャとなるため、実行プランとしてはPreparedStatementと同様に実行プラン作成時のバインド変数には注意が必要です。
ちなみにストアドプロシージャのjavaからの実行時のそのほかのパターンは以下の通りです。

パターン	実行方法
IN/OUTパラメータが必要ないストアドプロシージャ	java.sql.statement
INパラメータが必要なストアドプロシージャ	java.sql.Preparedstatement
OUTパラメータが必要なストアドプロシージャ	java.sql.CallableStatement

詳しくはMicrosoftのドキュメントをみていただければと思います。
https://docs.microsoft.com/en-us/sql/connect/jdbc/using-statements-with-stored-procedures

また、今回使ったSQLステートメントと実行プランの確認に使ったクエリも以下に残しておきます。
例によって使いまわしですが。

USE master
GO

SET NOCOUNT ON
GO

/*********************************************/
-- sort
-- 0 : Total CPU Usage TOP100
-- 1 : Average CPU Usage TOP100
-- 2 : Total Elapsed Time TOP100
-- 3 : Average Elapsed Time TOP100
-- 4 : Total IO Page TOP100
-- 5 : Average IO Page TOP100
/*********************************************/
DECLARE @sort INT

SET @sort = 0

SELECT TOP 100 CASE 
		WHEN @sort = 0
			THEN rank() OVER (
					ORDER BY total_worker_time DESC
						,sql_handle
						,statement_start_offset
					)
		WHEN @sort = 1
			THEN rank() OVER (
					ORDER BY (total_worker_time + 0.0) / (execution_count * 1000) DESC
						,sql_handle
						,statement_start_offset
					)
		WHEN @sort = 2
			THEN rank() OVER (
					ORDER BY total_elapsed_time DESC
						,sql_handle
						,statement_start_offset
					)
		WHEN @sort = 3
			THEN rank() OVER (
					ORDER BY (total_elapsed_time + 0.0) / (execution_count * 1000) DESC
						,sql_handle
						,statement_start_offset
					)
		WHEN @sort = 4
			THEN rank() OVER (
					ORDER BY (total_logical_reads + total_logical_writes) DESC
						,sql_handle
						,statement_start_offset
					)
		WHEN @sort = 5
			THEN rank() OVER (
					ORDER BY (total_logical_reads + total_logical_writes) / (execution_count + 0.0) DESC
						,sql_handle
						,statement_start_offset
					)
		END AS [row_no]
	,db_name(st.dbid) AS [database_name]
	,creation_time
	,last_execution_time
	,(total_worker_time + 0.0) / 1000 AS [total_worker_time(ms)]
	,(total_worker_time + 0.0) / (execution_count * 1000) AS [AvgCPUTime(ms)]
	,(total_elapsed_time + 0.0) / 1000 AS [total_elapsed_time(ms)]
	,(total_elapsed_time + 0.0) / (execution_count * 1000) AS [AvgElapsedTime(ms)]
	,total_logical_reads AS [LogicalReads(page)]
	,total_logical_writes AS [logicalWrites(page)]
	,total_logical_reads + total_logical_writes AS [AggIO(page)]
	,(total_logical_reads + total_logical_writes) / (execution_count + 0.0) AS [AvgIO(page)]
	,execution_count
	,total_rows
	,st.TEXT AS [batch_query_text]
	,CASE 
		WHEN sql_handle IS NULL
			THEN ' '
		ELSE (
				SUBSTRING(st.TEXT, (qs.statement_start_offset + 2) / 2, (
						CASE 
							WHEN qs.statement_end_offset = - 1
								THEN LEN(CONVERT(NVARCHAR(MAX), st.TEXT)) * 2
							ELSE qs.statement_end_offset
							END - qs.statement_start_offset
						) / 2)
				)
		END AS [statement_query_text]
	,plan_generation_num
	,[cp].objtype
	,qp.query_plan
FROM sys.dm_exec_query_stats AS [qs]
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS [st]
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS [qp]
INNER JOIN  sys.dm_exec_cached_plans AS [cp]
			ON [cp].[plan_handle] = [qs].[plan_handle]
WHERE total_worker_time > 0
ORDER BY CASE 
		WHEN @sort = 0
			THEN total_worker_time
		WHEN @sort = 1
			THEN (total_worker_time + 0.0) / (execution_count * 1000)
		WHEN @sort = 2
			THEN total_elapsed_time
		WHEN @sort = 3
			THEN (total_elapsed_time + 0.0) / (execution_count * 1000)
		WHEN @sort = 4
			THEN (total_logical_reads + total_logical_writes)
		WHEN @sort = 5
			THEN (total_logical_reads + total_logical_writes) / (execution_count + 0.0)
		END DESC
OPTION (RECOMPILE)