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で処理時間を計測する

SQL Serverでパフォーマンスを計測したかったので調査してまとめました.

処理時間を計測する

処理を開始する前にシステム日時を取得し,処理完了後に開始日時とシステム日時の差を取得します. DATEDIFFの引数を変えれば単位を変えることができます(例ではms).

DECLARE @STARTDATETIME datetime2 = SYSDATETIME()

-- ここに計測したいSQLを書く

SELECT DATEDIFF(MILLISECOND, @STARTDATETIME, SYSDATETIME()) AS PROCESSING_TIME_MS

キャッシュを消す

SQL Serverはデータバッファのキャッシュやクエリプランのキャッシュを再利用する賢いヤツなので純粋な処理時間を計測したい場合はキャッシュを消します.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

処理を待機する

念のためキャッシュを消した後数秒待機させます.

WAITFOR DELAY '00:00:10'

まとめ

以上をまとめると処理時間を計測することができます.

-- キャッシュを消す
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

-- キャッシュ消去後数秒待機する
WAITFOR DELAY '00:00:10'

-- 開始日時を取得する
DECLARE @STARTDATETIME datetime2 = SYSDATETIME()

-- ここに計測したいSQLを書く

-- 終了日時と開始日時の差から処理時間を取得する
SELECT DATEDIFF(MILLISECOND, @STARTDATETIME, SYSDATETIME()) AS PROCESSING_TIME_MS

OpenLayersでレイヤの読み込み開始/終了を検知する

例えば,OpenLayersで地図にレイヤを追加している処理があるとします.

private main(): void {
    // 地図
    let map = new ol.Map({
        layers: [
            new ol.layer.Tile({
                source: new ol.source.OSM()
            })
        ],
        target: 'map',
        view: new ol.View({
            center: [0, 0],
            zoom: 2
        })
    });

    // レイヤのソース
    const layerSrc = new ol.source.TileArcGISRest({
        url: 'http://services.arcgisonline.com/ArcGIS/rest/services/World_Street_Map/MapServer'
    });

    // レイヤ
    const layer = new ol.layer.Image({
        source: layerSrc
    });

    // 地図にレイヤを追加する
    map.addLayer(layer);
}


レイヤの読み込み開始/終了を検知するにはimageloadstart,imegeloadendのイベントハンドラを追加します.

private main(): void {
    const self = this;

    // 地図
    let map = new ol.Map({
        layers: [
            new ol.layer.Tile({
                source: new ol.source.OSM()
            })
        ],
        target: 'map',
        view: new ol.View({
            center: [0, 0],
            zoom: 2
        })
    });

    // レイヤのソース
    const layerSrc = new ol.source.TileArcGISRest({
        url: 'http://services.arcgisonline.com/ArcGIS/rest/services/World_Street_Map/MapServer'
    });

    // 読み込み開始イベントが発生したらコンソールに通知する
    layerSrc.on('imageloadstart', () => {
        console.log(self.getDateTimeStr() + ": レイヤ読み込み開始");
    });

    // 読み込み終了イベントが発生したらコンソールに通知する
    layerSrc.on('imageloadend', () => {
        console.log(self.getDateTimeStr() + ": レイヤ読み込み終了");
    });


    // レイヤ
    const layer = new ol.layer.Image({
        source: layerSrc
    });

    // 地図にレイヤを追加する
    map.addLayer(layer);
}

private getDateTimeStr(): string {
    const date = new Date();
    const year = date.getFullYear().toString();
    const month = ('0' + (date.getMonth() + 1)).slice(-2);
    const day = ('0' + date.getDate()).slice(-2);
    const hour = ('0' + date.getHours()).slice(-2);
    const min = ('0' + date.getMinutes()).slice(-2);
    const sec = ('0' + date.getSeconds()).slice(-2);
    const millisec = ('0' + date.getMilliseconds()).slice(-3);
    return year + '/' + month + '/' + day + ' ' + hour + ':' + min + ':' + sec + '.' + millisec;
}


これでブラウザのコンソールにこのように出力されます.

2019/03/11 10:22:50.577: レイヤ読み込み開始
2019/03/11 10:22:51.648: レイヤ読み込み終了


ボトルネックの調査やプログレスバーの表示などに使えそうですね.

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
}

動的に追加した要素のイベントを拾う

動的に追加した要素のイベントが拾えませんと相談されたのでサンプルを書きました.

例えば「行追加」ボタンを押したらテーブルに行が追加される場合です. 最初に表示されている行の「イベント発火!」ボタンを押すとalertが出ますが,追加した行のボタンを押してもイベントが拾えません.

<table>
  <tr>
    <th></th>
  </tr>
  <tr>
    <td>
      <button type="button" class="alert-btn">イベント発火!</button>
    </td>
  </tr>
</table>
<br />
<button type="button" id="addRow">行追加</button>
$(function(){
  var tr = '<tr><td><button type="button" class="alert-btn">イベント発火!</button></td></tr>';
  $('#addRow').click(function(){
    $('table').append(tr);
  });
  
  $('.alert-btn').click(function(){
    alert('ボタンがクリックされました');
  });
});

See the Pen cannot handle added row event by mt (@mtakeda) on CodePen.

そこでbodyのイベントを拾うようにすると動的に追加した要素のイベントを拾うことができます.

$(function(){
  var tr = '<tr><td><button type="button" class="alert-btn">イベント発火!</button></td></tr>';
  $('#addRow').click(function(){
    $('table').append(tr);
  });
  
  $('body').on('click', '.alert-btn', function(event){
    alert('ボタンがクリックされました');
  });
});

See the Pen handle added row event by mt (@mtakeda) on CodePen.

C#でクラスのフィールド名と値を取得する

久々の投稿です. 後輩にクラスからフィールド名と値ってどうやってとれるんですか?と聞かれたのでサンプルを書きました.

例えばこんなクラスがあったとします.

public static class Orange
{
    public const int Id = 1;
    public const string Name = "Orange";
    public const int Price = 100;
}

このクラスからフィールド名と値をリフレクションで取得します.

public class Program
{
    static void Main(string[] args)
    {
        var t = typeof(Orange);
        foreach (var f in t.GetFields())
        {
            var n = f.Name;
            var v = f.GetValue(t);
            Console.WriteLine(string.Format("フィールド名: {0}, 値: {1}", n, v));
        }
        Console.ReadLine();
    }
}

Visual Studioで適当にコンソールアプリ作って実行するとこんな感じに表示されます.

フィールド名: Id, 値: 1
フィールド名: Name, 値: Orange
フィールド名: Price, 値: 100

おしまい.