SQLを修正して修正前と結果が変わらないことを確認する

パフォーマンスチューニングをおこなう際に修正したSQLの取得結果が修正前と変わらないことを確かめるために調べました.

事前準備

例えばこんなテーブルとデータがあったとします.

CREATE TABLE SAMPLE_ROLL
(
    ID int,
    NAME nvarchar(20),
    PRIMARY KEY(ID)
);

CREATE TABLE SAMPLE_USER
(
    ID int,
    NAME nvarchar(20),
    ROLL_ID int,   
    PRIMARY KEY(ID),
    FOREIGN KEY(ROLL_ID)
      REFERENCES SAMPLE_ROLL(ID)
);

INSERT INTO SAMPLE_ROLL(ID, NAME) VALUES(1, 'ADMIN01');
INSERT INTO SAMPLE_ROLL(ID, NAME) VALUES(2, 'TEST01');
INSERT INTO SAMPLE_ROLL(ID, NAME) VALUES(3, 'TEST02');

INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(1, 'user01', 1);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(2, 'user02', 1);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(3, 'user03', 2);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(4, 'user04', 3);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(5, 'user05', 3);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(6, 'user06', 3);

あるSQLが遅いとします.

SELECT * FROM SAMPLE_USER u WHERE u.ROLL_ID IN (SELECT r.ID FROM SAMPLE_ROLL r WHERE r.NAME LIKE 'TEST%');

チューニング

INをEXISTSで書き直します.

SELECT * FROM SAMPLE_USER u WHERE EXISTS (SELECT * FROM SAMPLE_ROLL r WHERE r.NAME LIKE 'TEST%' and u.ROLL_ID = r.ID);

確認

修正前と修正後で取得結果が変わらないことを確かめるにはEXCEPTで差が無いことを確認します.

SELECT * FROM SAMPLE_USER u WHERE u.ROLL_ID IN (SELECT r.ID FROM SAMPLE_ROLL r WHERE r.NAME LIKE 'TEST%')
EXCEPT
SELECT * FROM SAMPLE_USER u WHERE EXISTS (SELECT * FROM SAMPLE_ROLL r WHERE r.NAME LIKE 'TEST%' and u.ROLL_ID = r.ID)

サンプル

一気通貫で確認したいかたは以下のsqlで.

BEGIN tran

CREATE TABLE SAMPLE_ROLL
(
    ID int,
    NAME nvarchar(20),
    PRIMARY KEY(ID)
);

CREATE TABLE SAMPLE_USER
(
    ID int,
    NAME nvarchar(20),
    ROLL_ID int,   
    PRIMARY KEY(ID),
    FOREIGN KEY(ROLL_ID)
      REFERENCES SAMPLE_ROLL(ID)
);

INSERT INTO SAMPLE_ROLL(ID, NAME) VALUES(1, 'ADMIN01');
INSERT INTO SAMPLE_ROLL(ID, NAME) VALUES(2, 'TEST01');
INSERT INTO SAMPLE_ROLL(ID, NAME) VALUES(3, 'TEST02');

INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(1, 'user01', 1);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(2, 'user02', 1);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(3, 'user03', 2);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(4, 'user04', 3);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(5, 'user05', 3);
INSERT INTO SAMPLE_USER(ID, NAME, ROLL_ID) VALUES(6, 'user06', 3);

SELECT * FROM SAMPLE_USER u WHERE u.ROLL_ID IN (SELECT r.ID FROM SAMPLE_ROLL r WHERE r.NAME LIKE 'TEST%')
EXCEPT
SELECT * FROM SAMPLE_USER u WHERE EXISTS (SELECT * FROM SAMPLE_ROLL r WHERE r.NAME LIKE 'TEST%' and u.ROLL_ID = r.ID)

ROLLBACK tran

SQL Serverで領域に含まれるフィーチャーを取得する

ArcGIS ServerのREST APIでデータを取得するのが面倒だからArcGIS Serverを介さずにデータを取得できないか試してみたらできました.


例えばフィーチャーを格納するテーブルがあって

CREATE TABLE SAMPLE_FEATURE(SHAPE geometry NULL);

こんな感じにデータが格納されているとします.

INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(133 33)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(133 34)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(134 33)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(134 34)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(135 34)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(135 35)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(135 36)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(136 35)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(136 36)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(136 37)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(137 36)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(137 37)', 4326));


全件取得する場合はこんな感じです.

SELECT SHAPE.STX AS X, SHAPE.STY AS Y FROM SAMPLE_FEATURE;
|  X  |  Y |
|-----|----|
| 133 | 33 |
| 133 | 34 |
| 134 | 33 |
| 134 | 34 |
| 135 | 34 |
| 135 | 35 |
| 135 | 36 |
| 136 | 35 |
| 136 | 36 |
| 136 | 37 |
| 137 | 36 |
| 137 | 37 |


ある領域に含まれるフィーチャーのみを抽出したい場合は,領域をあらわすポリゴンと交差するかを判定します.

DECLARE @extent_wkt nvarchar(1000) = 'POLYGON ((134 36,136 36,136 34,134 34,134 36))'
DECLARE @polygon geometry = geometry::STGeomFromText(@extent_wkt, 4326)

SELECT SHAPE.STX AS X, SHAPE.STY AS Y FROM SAMPLE_FEATURE WHERE SHAPE.STIntersects(@polygon) = 1;
|  X  |  Y |
|-----|----|
| 134 | 34 |
| 135 | 34 |
| 135 | 35 |
| 135 | 36 |
| 136 | 35 |
| 136 | 36 |

Linux初心者がとあるプロジェクトで手順書が無いサーバーの構築を命じられたときに使用したコマンドをまとめた

Linux初心者に突然以下の仕事が振られました.

  • 試験環境サーバー構築
  • シェルの要所要所でログを出すようにする

今回構築するサーバーはなんと本番環境しかなく,まともな手順書がないっていうもう残念な感じでした. そんな中でもtry & errorでなんとか構築できたのでその際に使用したコマンドたちを備忘録としてさらします.

パッケージ管理

パッケージインストール

sudo apt-get install apache2

パッケージ削除

sudo apt-get remove apache2

パッケージ管理更新

sudo apt-get update && sudo apt-get upgrade

パッケージ完全削除

  • 設定ファイルごと削除する
sudo apt-get purge apache2

ファイル操作

ファイルコピー

  • /usr/local に tmpsrc ファイルがごそっと入る
sudo cp -r /tmp/tmpsrc /usr/local

ディレクトリ操作

ディレクトリ作成

mkdir -p testdir

カレントディレクトリ表示

pwd

解凍

tar.gzの解凍

tar -zxvf xxxx.tar.gz

.tgzの解凍

tar xvzf xxxx.tgz -C /tmp/tmpsrc

所有権変更

# -Rでディレクトリの中身も含めて再帰的に処理する
# 所有者をtestuserに,所有グループをrootにする
chown -R testuser:root /home/testuser/example01 /home/testuser/example02

ログイン

ディレクトリを変えずにログインする

su testuser

ディレクトリをユーザーのルートに切り替えてログインする

su - testuser

ユーザー

ユーザー作成

sudo useradd testuser

ユーザー削除

sudo userdel testuser

ユーザー一覧

cat /etc/passwd

グループ

グループを作成

sudo groupadd testuser

グループを削除

sudo groupdel testuser

グループ一覧

cat /etc/group

FW

FWインストール

sudo apt install firewalld

FWポート開放

sudo firewall-cmd --zone=public --add-port=8080/tcp --permanent

FW設定反映

sudo firewall-cmd --reload

パーミッション

パーミッション設定

モード(数字) モード(アルファベット) 権限
4 r 読み取り
2 w 書き込み
1 x 実行

「所有者」「所有グループ」「その他」の順に権限をモードの合計値で設定する.

chmod 777 /tmp/test.txt

パーミッション確認

ls -l

cron

コマンドやシェルスクリプトを定期的に自動実行するデーモン.

cron.d

  • /etc/cron.d 配下に設定を追加する
sudo cp /etc/crontab /etc/cron.d/cron_test
vi /etc/cron.d/cron_test
# m h dom mon dow user  command
# 10分ごとに実行する
*/10 * * * *   userA echo "hello new world!"

その他

スクリーン初期化

clear

環境変数設定

export HOGE=aiueo

ロギング

  • コマンドの実行履歴をファイルに出力する.
script /tmp/20190207.log
  • ロギングをとめる
exit
  • 自作ロガー
#!/bin/bash
#
# 指定したファイルにログを出力します。
#
# Example:
#   . ./logger.sh
#   write_log "/tmp/sample.log" "this is test."
# 
# Args:
#   $1 - ログ出力先
#   $2 - ログメッセージ
#
# Return:
#   0
#
function write_log() {
  nowd=`date '+%Y/%m/%d'`
  nowt=`date '+%H:%M:%S'`
  echo "${nowd} ${nowt}: $2" >> $1
  return 0
}