Doogie Notes

覚え書き

SQL Server 組織ツリーの出力

共通テーブル式(CTE式)を使った再帰検索

f:id:doogie:20180919162945p:plain
              ↓
f:id:doogie:20180919164044p:plain

WITH CTE (
    COMPANY_CD
,   SECTION_CD
,   SECTION_NAME
,   PARENT_SECTION_CD
,   PATH
) AS (
    SELECT
        COMPANY_CD
    ,   SECTION_CD
    ,   SECTION_NAME
    ,   PARENT_SECTION_CD
    ,   CONVERT(VARCHAR(100), ISNULL(SECTION_CD, '')) AS PATH
    FROM
        SECTION_MST
    WHERE
        PARENT_SECTION_CD IS NULL
    UNION ALL
    SELECT
        T1.COMPANY_CD
    ,   T1.SECTION_CD
    ,   T1.SECTION_NAME
    ,   T1.PARENT_SECTION_CD
    ,   CONVERT(
            VARCHAR(100)
        ,   ISNULL(CTE.PATH, '') + ISNULL(T1.SECTION_CD, '')) AS PATH
    FROM
        SECTION_MST T1
    INNER JOIN
        CTE
    ON
        T1.COMPANY_CD        = CTE.COMPANY_CD
    AND T1.PARENT_SECTION_CD = CTE.SECTION_CD
)
SELECT * FROM CTE ORDER BY COMPANY_CD, PATH
;


【参考サイト】
www.earthlink.co.jp
scipio-afi.at.webry.info
zero-config.com

SQL Server 統計情報の更新

(統計情報の更新クエリ)

USE <データベース名>;
GO 
UPDATE STATISTICS <スキーマ>.<テーブル名> WITH FULLSCAN;
GO

(統計情報の出力)※上記統計情報の更新を確認する

SET NOCOUNT ON;
GO

SELECT
    syst.name                                 AS [テーブル名]
,   syst.object_id                            AS [テーブルオブジェクトID]
,   syss.name                                 AS [統計名]
,   syss.stats_id                             AS [統計ID]
,   STATS_DATE(syss.object_id, syss.stats_id) AS [更新日時] 
FROM
    sys.stats_columns syssc 
INNER JOIN 
    sys.stats syss 
ON 
    syssc.object_id = syss.object_id 
AND syssc.stats_id = syss.stats_id 
INNER JOIN 
    sys.tables syst 
ON 
    syssc.object_id = syst.object_id 
ORDER BY
    [更新日時] DESC
;


【参考サイト】
moriroom.my.coocan.jp

SQLServerのSELECT結果をファイルに出力する

SQLファイルのSELECTクエリの前に「SET NOCOUNT ON」を付けると出力結果ファイルに件数が出力されない。

sqlcmd -S <DBサーバ名> -U <ユーザー> -P <パスワード> -d <DB名> -i <SQLファイルパス> -s, -W -h -1 -o <出力ファイルパス>
  • s:区切文字
  • W:余分な空白を省く
  • h:-1でヘッダ非表示
  • o:出力ファイルパスを指定


【参考サイト】
qiita.com
sqlcmd ユーティリティ | Microsoft Docs

DOSバッチ バッチ処理からバッチ処理の呼出し

①「start」呼び出し
start呼び出しの場合、別ウィンドウ(別スレッド?)で呼び出される。
呼び出したバッチ処理の終了を待たずに、呼び出し元の処理は続く。

start E:\bat\test.bat 引数1

②「call」呼び出し
call呼び出しの場合、同じウィンドウで呼び出したバッチも実行される。
呼び出したバッチの終了を待って、呼び出し元の処理を続けるため、処理結果の判定を入れることができる。

start E:\bat\test.bat 引数1
IF NOT %ERRORLEVEL% == 0 GOTO ERROR
GOTO END

:ERROR
EXIT 8
:END
EXIT 0

(呼出し元バッチの戻り値設定部分)
EXITの後に「/b」を付ける

:ERROR
EXIT /b 8
:END
EXIT /b 0


【参考サイト】
バッチファイルからバッチファイルを呼び出す
呼び出したバッチファイルやサブルーチンで戻り値を取得する

SQL Server インデックス付きビューの作成

(インデックス付きのビューを作成する条件)

  1. CREATE VIEW ステートメント実行時、ANSI_NULLSオプションとQUOTED_IDENTIFIERオプションがONに設定されている
  2. ビューで参照するベーステーブルの作成時にもANSI_NULLSオプションがONに設定されている
  3. ビューが参照しているのはベーステーブルのみで、他のビュー参照していない
  4. ビューで参照されるすべてのベーステーブルは、ビューと同じデータベース内に存在し、ビューと同じ所有者である
  5. ビューはWITH SCHEMABINDINGオプションを使用して作成されている
  6. ビューで参照されるユーザ定義関数は、SCHEMABINDINGオプションを使用して作成済みである
  7. ビューでは、テーブルとユーザ定義関数について、スキマー名.オブジェクト名の2つの要素で構成される名前で参照する
  8. ビュー内の式で参照される関数は決定的である(決定的であるとは、関数の引数に同じ値を渡した場合、常に同じ値が返される状態を指す)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--ビューを作成する
CREATE VIEW [スキーマ].[ビュー名] WITH SCHEMABINDING
AS
SELECT
    COMPANY_CD
,   COMPANY_NM
,   START_DATE
,   END_DATE
FROM
    COMPANY_MST
WHERE
    COMPANY_CD = '1234'
GO

--クラスタ化インデックスを作成する
CREATE UNIQUE CLUSTERED INDEX PK_インデックス名
ON dbo.ビュー名 (COMPANY_CD) 
GO

--非クラスタ化インデックスを作成する
CREATE NONCLUSTERED INDEX IDX_インデックス名
ON dbo.ビュー名 (START_DATE,END_DATE) 
GO


【参考サイト】
www.projectgroup.info

JDBCオプションでレスポンス改善

  • SQL Server Management Studioでは処理速度が速いのに、アプリから実行すると処理速度が遅い場合、JDBCのキャストに問題があることがある。
  • SQL ServerJDBCドライバはデフォルトでUnicodeで処理される。
  • SQL ServerではvarcharがSJIS、nvarcharがUnicodeなので、テーブルのカラムの型がvarcharだったので暗黙的な変換が行われるため処理が遅くなる。

(対応方法)

  1. SQL側でキャストする
  2. JDBCの接続プロパティ「sendStringParametersAsUnicode」をfalseにする。(デフォルトはtrue)
sendStringParametersAsUnicode=false


【参考サイト】
final.hateblo.jp
d.hatena.ne.jp

DOSバッチ 関数の使い方

@echo off
setlocal

rem タイムスタンプ情報の取得
call :GET_TIMESTAMP

rem ログファイル名の設定
set BATCH_LOG=%LOG_DIR%\batch_%DATE_TMP%.log

    ・
    ・
    ・
    ・
rem 処理開始ログ出力
call :LOG INFO 処理を開始します。

rem 引数の確認
set ARG=%1
if "%ARG%" == "" (
    call :LOG ERROR 引数が不足しています。
    goto ERROR
)
    ・
    ・
    ・
    ・
goto END

rem ログ出力
:LOG
call :GET_TIMESTAMP
echo %DATE_LOG% [%BATCH_NAME%][%1] %2 >> "%BATCH_LOG%" 2>&1
exit /b 0

rem タイムスタンプ情報の取得
:GET_TIMESTAMP
set DATE_TMP=%date:~0,4%%date:~5,2%%date:~8,2%
set TIME_TMP=%time: =0%
set TIME_FMT=%TIME_TMP:~0,2%%TIME_TMP:~3,2%%TIME_TMP:~6,2%%TIME_TMP:~9,2%
set TIME_LOG=%TIME_TMP:~0,2%:%TIME_TMP:~3,2%:%TIME_TMP:~6,2%.%TIME_TMP:~9,2%
set DATE_LOG=%date:~0,4%/%date:~5,2%/%date:~8,2%
set DATE_LOG=%DATE_LOG% %TIME_LOG%
exit /b 0

rem 異常終了時の処理
:ERROR 
echo NG >> "%BATCH_LOG%" 2>&1
exit /b 8

rem 正常終了時の処理
:END
echo OK >> "%BATCH_LOG%" 2>&1
exit /b 0


【参考サイト】
maku77.github.io