※この記事は【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してるなど）

３．補足

こんなん当たり前だろ？っていうあなた、あなたはいい職場に居ます。
問題がないように見えても、１で調べた環境の弱点部分に負担が集中しているのは避けるべき。
　環境に合わせたコードバランスが大事。
とはいえ、効率化のあまり複雑で誰もメンテできないようなコードは非推奨。

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