【VB6・VB】古いコードのリプレース その４ SQL

※この記事は【VB6・VB】古いコードのリプレース のシリーズその４です。

リプレースしてさあ本番データを入れると「遅い」という事がよくあった。
何が遅い？実はSQLだ。ということがたまにあります。
ということでSQL本職ではないですが、あくまで最低限の範囲でできることをまとめてみた。

---

SQLを扱うプログラムの改善

##１．環境チェック
まずやることは環境確認。

• サーバ側(データベース)PCのスペック/状態
• サーバとの接続環境
• ホスト側のPCのスペック/状態

サーバ側のHDDが一杯だったとか間抜けな話が割とよくあるので、忘れずにまずこれを行うこと。
メモリとか、SSDか否かも割と大事です。
問題がなくとも、環境に偏りがあることも割とよくある(ホスト側だけ古いとか)。
しっかり確認して問題なければ２へ。

##２．ソース確認

次はその該当ソースがなぜ重いのかを調査する。
私が見てきたVB案件では、大体が以下の３つに集約される。

###単にINDEXが張られていない
単純ミス。

対処法：

張りましょう。
※ただしそのテーブルに対して複数のソート方法があるときは、使う箇所の頻度や優先度を考慮すること。

###必要な処理に対してSQL発行回数が異常に多いクソコードがある
よくあるのが、ソースの何処かで
「データが１行ずつほしい…せや！SQLから１行ずつループで取ったろ！」
……って思想がまかり通ってる箇所があるパターン。
小規模開発だと特にそれでも動いてしまうので、問題が顕在化しにくい。
十年とか十五年とか運用してる間にデータが増えてやっと発覚する。

例1 Forループずつ毎回発行している

Forループが好きで好きで１レコードずつ取ってこないと気が済まない前任者が書いたコード。

例：こんなクソコード

Function hogehoge(Byval iCnt As Integer) As String() '引数iCnt：件数的な何か
    Dim Employees As String(iCnt)

    For i As Integer = 0 To iCnt
        'こんな感じのSQLを発行してReaderに入れる..
        'SELECT EMPLOYEE FROM 社員リスト WHERE INDEX = i.ToString()
        Employees(i) = Reader(0)("EMPLOYEE") '必ず１件しか無い
    Next

    Return Employees 
End Function

対処法：
こういうのは発行回数を減らすこと。

修正例

Function hogehoge(Byval iCnt As Integer) As String() '引数iCnt：件数的な何か
    Dim Employees As String(iCnt)
    'こんな感じのSQLを発行してReaderに入れる..
    'SELECT EMPLOYEE FROM 社員リスト WHERE INDEX BETWEEN = 0 AND iCnt.ToString()

    Dim i As Integer = 0  
    While (Readerの件数がある限り)'INDEXが欠けていなければiCnt件存在
        Employees(i) = Reader(i)("EMPLOYEE")
        i += 1
    End While

    Return Employees
End Function

このhogehoge関数の戻り値String()は正直クソダサイので、時間があれば何かコレクションで返すように直したいところです。

例2 サブクエリが不十分

また、以下みたいな「サブクエリ？JOIN？知らない子ですね…」という前任者のコード。

例：こんなクソコード

Sub GetSqlAndView()
    'こんな感じのSQLを発行してReaderに入れる..
    ' SELECT * FROM 社員リスト

    While (Readerの件数がある限り)
        'なにか補足データを取得する..
        Dim Hosoku1,Hosoku2,Hosoku3 as String
        If Reader("勤続年数")> 10 Then
            'こんな感じの補足SQLを発行してHosoku1に入れる..          
            'SELECT 勤続ボーナス FROM 勤続テーブル WHERE INDEX = Reader("INDEX")
        End If
        If Reader("扶養有無") = True Then
            'こんな感じの補足SQLを発行してHosoku2に入れる..   
            'SELECT 扶養控除 FROM 扶養テーブル WHERE INDEX = Reader("INDEX")
        End If
        If Reader("役職") = "社長" Then
            'こんな感じの補足SQLを発行してHosoku3に入れる..    
            'SELECT 役員報酬 FROM 役員テーブル WHERE INDEX = Reader("INDEX")
        End If

        '画面に表示する
        Me.DataGridView.Rows.Add(
        Reader("EMPROYEE"), Hosoku1(0)("勤続ボーナス"),Hosoku2(0)("扶養控除"),Hosoku3(0)("役員報酬"))
    End While
End Sub

対処法：
なるべくまとめて件数を減らす。
ただし、ごく僅かしか発生しないようなケースの場合は、もう１回発行したほうが効率が良いことも。
実際のデータで動かして確認すべき。

例：修正例

Sub GetSqlAndView()
    'こんな感じのSQLを発行してReaderに入れる..
    'SELECT 社員リスト.*,勤続テーブル.勤続ボーナス,扶養テーブル.扶養控除 FROM 社員リスト
    ' LEFT JOIN 勤続テーブル
    '  ON INDEX = 社員リスト.INDEX AND 社員リスト.勤続年数 > 10
    ' LEFT JOIN 扶養テーブル
    '  ON INDEX  = 社員リスト.INDEX AND 社員リスト.扶養有無 = True

    While (Readerの件数がある限り)
        'ごく僅かなケースのみ、補足データを取得する..
        Dim Hosoku as String
        If Reader("役職") = "社長" Then
            'こんな感じのSQLを発行してHosokuに入れる..    
            'SELECT 役員報酬 FROM 役員テーブル WHERE INDEX = Reader("INDEX")
        End If

        '画面に表示する
        Me.DataGridView.Rows.Add(
        Reader("EMPROYEE"), Reader("勤続ボーナス"),Reader("扶養控除"),Hosoku(0)("役員報酬"))
    End While
End Sub

###膨大なテーブルを参照するSQLを１回で発行している

上とは逆のパターン。
あまりにたくさんのテーブルをサブクエリやJOINで使っているため、サーバ側の負担となっている。
特にホスト側よりサーバ側のスペックの方が低い場合はこれを疑うべき。

###その他
それ以外に原因がある案件にあたったら追記します。

• レコードが１つ増えるたびに非効率的な配列拡張をしているのが原因の場合も。
  （DataGridViewにセル単位で値を入れたり、大量にArray.Resizeしてるなど）

#３．補足

• こんなん当たり前だろ？っていうあなた、あなたはいい職場に居ます。

• 問題がないように見えても、１で調べた環境の弱点部分に負担が集中しているのは避けるべき。
  　環境に合わせたコードバランスが大事。

• とはいえ、効率化のあまり複雑で誰もメンテできないようなコードは非推奨。