Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Open棟梁:動的パラメタライズド・クエリの性能測定に使用した、各種リソース情報と、その測定結果。
<?xml version="1.0"?>
<ROOT>
SELECT * FROM SHIPPERS
<WHERE>
WHERE
<IF>SHIPPERID = @p1</IF>
<IF>hoge1 = @hoge1</IF>
・・・
<IF>hoge1 = @hogeN</IF> → ここの「N」を、10, 20, 50, 100, 200, 500, 1000 タグと増やしていって性能を測定。
</WHERE>
<PARAM>
p1, Int32, 1
</PARAM>
</ROOT>
@daisukenishino2

This comment has been minimized.

Copy link
Owner Author

@daisukenishino2 daisukenishino2 commented Aug 3, 2018

前提条件

データ

  • NorthwindデーターベースのShippersテーブルから1レコードセレクト
  • 動的パラメタライズド・クエリ(XML)のタグ数を増やすことで、
    性能が劣化するので、タグ数とオーバーヘッドを分析してグラフ化する。

測定方法

EXE

Open棟梁:動的パラメタライズド・クエリ分析ツールを使用。
その内部で、高分解能パフォーマンス・カウンタを使用。
(QueryPerformanceCounter関数)

  • ExT : 実行時間
  • CT : CPU時間
    • KT : CPU時間(カーネル・モード)
    • UT : CPU時間(ユーザ・モード)

システム情報

コチラと同じ

@daisukenishino2

This comment has been minimized.

Copy link
Owner Author

@daisukenishino2 daisukenishino2 commented Aug 3, 2018

測定結果

10タグ

平均 ExT:1.2[msec], CT:0[msec], KT:0[msec], UT:0[msec]

ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:2[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:2[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]

20タグ

平均 ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]

ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]

50タグ

平均 ExT:1.9[msec], CT:0[msec], KT:0[msec], UT:0[msec]

ExT:2[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:2[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:2[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:1[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:2[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:2[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:3[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:3[msec], CT:0[msec], KT:0[msec], UT:0[msec]

100タグ

平均 ExT:4.6[msec], CT:1.6[msec], KT:0[msec], UT:1.6[msec]

ExT:6[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:5[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:4[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:6[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:4[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:4[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:4[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:4[msec], CT:0[msec], KT:0[msec], UT:0[msec]
ExT:5[msec], CT:16[msec], KT:0[msec], UT:16[msec]
ExT:4[msec], CT:0[msec], KT:0[msec], UT:0[msec]

200タグ

平均 ExT:19.2[msec], CT:17.5[msec], KT:0[msec], UT:17.5[msec]

ExT:20[msec], CT:16[msec], KT:0[msec], UT:16[msec]
ExT:19[msec], CT:16[msec], KT:0[msec], UT:16[msec]
ExT:19[msec], CT:16[msec], KT:0[msec], UT:16[msec]
ExT:20[msec], CT:16[msec], KT:0[msec], UT:16[msec]
ExT:19[msec], CT:16[msec], KT:0[msec], UT:16[msec]
ExT:19[msec], CT:31[msec], KT:0[msec], UT:31[msec]
ExT:19[msec], CT:16[msec], KT:0[msec], UT:16[msec]
ExT:19[msec], CT:16[msec], KT:0[msec], UT:16[msec]
ExT:19[msec], CT:16[msec], KT:0[msec], UT:16[msec]
ExT:19[msec], CT:16[msec], KT:0[msec], UT:16[msec]

500タグ

平均 ExT:281[msec], CT:283[msec], KT:1.6[msec], UT:281[msec]

ExT:273[msec], CT:281[msec], KT:16[msec], UT:266[msec]
ExT:271[msec], CT:281[msec], KT:0[msec], UT:281[msec]
ExT:278[msec], CT:281[msec], KT:0[msec], UT:281[msec]
ExT:270[msec], CT:266[msec], KT:0[msec], UT:266[msec]
ExT:287[msec], CT:297[msec], KT:0[msec], UT:297[msec]
ExT:272[msec], CT:266[msec], KT:0[msec], UT:266[msec]
ExT:267[msec], CT:266[msec], KT:0[msec], UT:266[msec]
ExT:311[msec], CT:313[msec], KT:0[msec], UT:313[msec]
ExT:285[msec], CT:281[msec], KT:0[msec], UT:281[msec]
ExT:295[msec], CT:297[msec], KT:0[msec], UT:297[msec]

1000タグ

平均 ExT:3194[msec], CT:3194[msec], KT:0[msec], UT:3194[msec]

ExT:3214[msec], CT:3203[msec], KT:0[msec], UT:3203[msec]
ExT:3258[msec], CT:3250[msec], KT:0[msec], UT:3250[msec]
ExT:3088[msec], CT:3094[msec], KT:0[msec], UT:3094[msec]
ExT:3195[msec], CT:3188[msec], KT:0[msec], UT:3188[msec]
ExT:3014[msec], CT:3016[msec], KT:0[msec], UT:3016[msec]
ExT:3224[msec], CT:3234[msec], KT:0[msec], UT:3234[msec]
ExT:3004[msec], CT:3016[msec], KT:0[msec], UT:3016[msec]
ExT:3616[msec], CT:3625[msec], KT:0[msec], UT:3625[msec]
ExT:3083[msec], CT:3078[msec], KT:0[msec], UT:3078[msec]
ExT:3238[msec], CT:3234[msec], KT:0[msec], UT:3234[msec]

@daisukenishino2

This comment has been minimized.

Copy link
Owner Author

@daisukenishino2 daisukenishino2 commented Aug 3, 2018

結果の分析

XML編集を再帰的に行っているため、200-300タグを超えてくると、リニアに性能劣化するので注意する。

image

なお、バッチの性能測定で使用した、NorthwindデーターベースのOrderテーブルから自動生成した動的SQLは、約30タグ。

@daisukenishino2

This comment has been minimized.

Copy link
Owner Author

@daisukenishino2 daisukenishino2 commented May 17, 2019

自動生成Daoによる解決策

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment