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
You can’t perform that action at this time.