自己投資としてチャレンジしている内容を Blog で公開しています。今回は Java による Microsoft SQL Server で統計情報の更新を行いたいと思います。(In English: Java – Update Statistics in SQL Server on Ubuntu No.91)
▼1. SQL Server クエリの実行が遅い！？
クエリが急に遅くなった。クエリの実行に時間がかかるようになった。という状況が発生した場合、統計情報が古いことが原因で発生している可能性があります。
統計情報が古く、実際のデータに合ったクエリ実行プランが生成されていないため、効率の悪い実行プランでクエリが実行された結果、クエリの実行時間が Duration time が長くなるという状況です。
対処方法としては、統計情報の更新となります。以降では統計情報の更新 “update statistics” および、クエリ実行プランの再作成 (sp_recompile) を行う Java のコードを紹介します。
▼2. 事前準備
2-1. 参照 Java – SQL Server サンプルデータ作成 v2 on Ubuntu No.89
▼3. 統計情報を更新し、クエリプランの再作成を行う Java コード
3-1. Apache Maven によるプロジェクトを作成
mvn archetype:generate -DinteractiveMode=false -DgroupId=org.example.sqlsrv -DartifactId=sqlupdstat -DarchetypeArtifactId=maven-archetype-quickstart3-2. デフォルトで作成された App.java および AppTest.java を削除
rm ./sqlupdstat/src/main/java/org/example/sqlsrv/App.java
rm ./sqlupdstat/src/test/java/org/example/sqlsrv/AppTest.java3-3. Visual Studio Code を起動
cd ./sqlupdstat/
code .3-4. pom.xml に、SQL Server の操作のための library を追記し保存 (Ctrl+S)
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>11.2.1.jre8</version>
</dependency>
</dependencies>上の、mssql-jdbc の pom.xml の設定が無い場合、コードを実行すると以下のエラーが出力されます。
Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:sqlserver://localhost:1433;databaseName=master;user=sa;password=yourpassowrd;encrypt=false
at java.sql.DriverManager.getConnection(DriverManager.java:689)
at java.sql.DriverManager.getConnection(DriverManager.java:270)
at org.example.sqlsrv.sqlupdstat.main(sqlupdstat.java:36)3-5. sqlupdstat.java を作成
Database および Table を作成後、サンプルデータを入れて、下記のクエリで統計情報を更新しています。その後、クエリプランをクリアするため、sp_recompile のストアドプロシージャを実行しています。
# 統計情報更新のクエリ
update statistics testdb.dbo.sampletable
# 次回クエリ実行時、クエリ実行プランを再作成させるストアドプロシージャ
use testdb;
Exec sp_recompile N’dbo.sampletable’
package org.example.sqlsrv;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class sqlupdstat {
// create an insert query and save it to a string
public static String insertQuery() {
String query = "INSERT INTO testdb.dbo.sampletable (id, num, cdatetime, note) VALUES (100,0,CURRENT_TIMESTAMP,'init')";
for (int num = 0; num < 10; num++) {
String AlphaNumericString = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" + "0123456789" + "abcdefghijklmnopqrstuvxyz";
StringBuilder sb = new StringBuilder(10);
for (int i = 1; i < 10; i++) {
int index = (int) (AlphaNumericString.length() * Math.random());
sb.append(AlphaNumericString.charAt(index));
}
String randomString = sb.toString();
query += ",(" + num*10 +"," + num + ",CURRENT_TIMESTAMP,'" + randomString + "')";
}
query += ";";
return query;
}
public static void main(String[] args) throws SQLException {
String url = "jdbc:sqlserver://localhost:1433;databaseName=master;user=sa;password=yourpassword;encrypt=false";
Connection conn = DriverManager.getConnection(url);
// Create databas
String createdb = "IF DB_ID (N'testdb') IS NULL CREATE DATABASE testdb";
Statement cd = conn.createStatement();
cd.execute(createdb);
System.out.println("Database was created.");
cd.close();
// Create table
String createtable = "IF NOT EXISTS (select * from testdb.dbo.sysobjects where name like 'sampletable') CREATE TABLE testdb.dbo.sampletable (id int primary key, num int, cdatetime datetime, note varchar(50))";
Statement ct = conn.createStatement();
ct.execute(createtable);
System.out.println("Table was created.");
ct.close();
// Insert data
String insertdata = insertQuery();
//System.out.println(insertdata);
Statement id = conn.createStatement();
id.execute(insertdata);
System.out.println("Data was inserted.");
id.close();
// Update statistics
String UpdateStatistics = "update statistics testdb.dbo.sampletable";
Statement us = conn.createStatement();
us.execute(UpdateStatistics);
System.out.println("Statistics was updated.");
us.close();
// Recompile
String Recompile = "use testdb;Exec sp_recompile N'dbo.sampletable';";
System.out.println(Recompile);
Statement rc = conn.createStatement();
System.out.println("The query execution plan was updated.");
rc.close();
conn.close();
}
}3-6. 実行結果
(出力結果の一部) java -cp /tmp/cp_2mki4e7qhllr13hydrsbsfhep.jar org.example.sqlsrv.sqlupdstat Database was created. Table was created. Data was inserted. Statistics was updated. use testdb;Exec sp_recompile N'dbo.sampletable'; The query execution plan was updated.
▼4. 参考情報
以上です。参考になりましたら幸いです。