Redgate Hub (日本語)

1月 21, 2021 admin 0 Comments

T-SQLウィンドウ関数は、多くのクエリを簡単に書き込むことができ、古い手法よりもパフォーマンスが向上することがよくあります。 たとえば、LAG関数を使用することは、自己結合を行うよりもはるかに優れています。 ただし、全体的なパフォーマンスを向上させるには、フレーミングの概念と、ウィンドウ関数が結果を提供するためにソートにどのように依存するかを理解注：2019年のオプティマイザの改善がパフォーマンスにどのように影響するかについては、私の新しい記事を参照してください！,

OVER句とSorting

OVER句には、ソートを引き起こす可能性のあるオプションが二つあります。PARTITION BYとORDER BY。 パーティションはすべての画面の機能もオプションです。 ORDER BYは、ほとんどの機能で必要です。 何を達成しようとしているかに応じて、データはOVER句に基づいてソートされ、それがクエリのパフォーマンスのボトルネックになる可能性があります。

OVER句のORDER BYオプションは、データベースエンジンが正しい順序で関数を適用するために、いわば行を並べることができるようにするために必要です。, たとえば、ROW_NUMBER関数をSalesOrderIDの順に適用するとします。 結果は、関数をTotalDueの順に降順に適用する場合とは異なるように見えます。, これが例です。

1
2
3
4
5
6
7
8
9
10
11
adventureworks2017;を使用します。-またはあなたが持っているいずれかのバージョン
go
select salesorderid,
totaldue,
row_number()over(order by salesorderid)as rownum
from sales.,SalesOrderHeader;
SalesOrderIDを選択します。
TotalDue、
ROW_NUMBER()OVER(ORDER BY TotalDue DESC)As RowNum
販売から。SalesOrderHeader;

最初のクエリはORDER BYオプションとしてクラスターキーを使用しているため、ソートは必要ありません。

第二のクエリには高価なソート操作があります。,

OVER句のORDER BYは、クエリ全体に追加されたORDER BY句に接続されていませんが、これはまったく異なる可能性があります。, Here is an example showing what happens if the two are different:

1
2
3
4
5
SELECT SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(ORDER BY TotalDue DESC) AS RowNum
FROM Sales.,SalesOrderHeader
ORDER BY SalesOrderID;

クラスタ化インデックスキーはSalesOrderIDですが、行はまずTotalDueで降順に並べ替えてからSalesOrderIDに戻す必要があります。 実行計画を見てみましょう：

PARTITION BY句は、すべてのT-SQLウィンドウ関数に対してサポートされていますが、オプションです。 これは、集約クエリのGROUP BY句に似ていますが、正確には似ていません。, This example starts the row numbers over for each customer.

1
2
3
4
5
6
SELECT CustomerID,
SalesOrderID,
TotalDue,
ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SalesOrderID)
AS RowNum
FROM Sales.,SalesOrderHeader;

実行プランには、CustomerIDとSalesOrderIDの組み合わせであるソート操作のみが表示されます。

ソートのパフォーマンスへの影響を克服する唯一の方法は、OVER句専用のインデックスを作成することです。 彼の著書Microsoft SQL Server2012High-Performance T-SQL Using Window Functionsでは、Itzik Ben-GanはPOCインデックスを推奨しています。 POCは、（P）ARTITION BY、（O）RDER BY、および（c）overingの略です。, キーのPARTITION BY列およびORDER BY列の前にフィルタリングに使用される列を追加することをお勧めします。 その後の追加追加カラムを作成するために必要な、全指標として含まれます。 のようにしてなんといっても、必要なテストの見方などの指標に影響を検索すると、全体の負荷も大きくなっていました。 もちろん、作成するすべてのクエリにインデックスを追加することはできませんが、ウィンドウ関数を使用する特定のクエリのパフォーマンスが重要,

Here is an index that will improve the previous query:

1
2
3
CREATE NONCLUSTERED INDEX test ON Sales.,SalesOrderHeader
(CustomerID,SalesOrderID)
INCLUDE(TotalDue);

クエリを再実行すると、ソート操作は実行計画から消えます。

framing

私の意見では、フレーミングはt-Sqlウィンドウ関数について学ぶときに理解するのが最も難しい概念です。 構文の詳細については、”t-SQLウィンドウ関数の概要”を参照してください。, たとえば、

  • FIRST_VALUE
  • LAST_VALUE

    • 幸いにも、ほとんどの場合、フレーミングは必要ありませんが、残念ながら、フレームをスキップしてデフォルトを使用するのは簡単です。 デフォルトのフレームは、常に無制限の先行行と現在行の間の範囲です。 ORDER BYオプションが一意の列または列のセットで構成されている限り、正しい結果が得られますが、パフォーマンスヒットが表示されます。,id=”4b64f77d3b”>

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    統計ioをオンに設定します。
    go
    select customerid,
    salesorderid,
    totaldue,
    sum(totaldue)over(partition by customerid order by salesorderid)
    as runningtotal
    from sales.,SalesOrderHeader;
    SELECT CustomerID,
    SalesOrderID,
    TotalDue,
    SUM(TotalDue)OVER(CustomerIDによるパーティションOrder BY SalesOrderID
    無制限の前行と現在行の間の行)
    RunningTotal
    売上から。SalesOrderHeader;

    結果は同じですが、パフォーマンスは非常に異なります。 残念ながら、実行計画はこの場合、あなたに真実を教えてくれません。, 各クエリにはリソースの50％がかかったことが報告されています。

    統計IO値を確認すると、違いが表示されます。

    ORDER BYオプションが一意でない場合、またはLAST_VALUEを使用している場合、正しいフレームを使用することがさらに重要です。 この例では、ORDER BY列はOrderDateですが、一部の顧客は特定の日付に複数の注文を行っています。 フレームを指定しない場合、または範囲を使用する場合、この関数は一致する日付を同じウィンドウの一部として扱います。,”>

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    選択customerid,
    salesorderid,
    totaldue,
    orderdate,
    sum(totaldue)over(customerid order by orderdateによるパーティション)
    as runningtotal,
    sum(totaldue)over(customerid order by orderdateによるパーティション
    無制限の前行と現在の行の間の行)
    as販売からcorrectrunningtotal
    。,SalesOrderHeader
    ここで、CustomerIDは次のようになります(“11433″,”11078″,”18758”);

    不一致の理由は、RANGEがデータを論理的に認識し、ROWSが位置的に認識するためです。 この問題には二つの解決策があります。 一つは、ORDER BYオプションが一意であることを確認することです。 他の重要なオプションは、サポートされているフレームを常に指定することです。

    フレーミングが論理的な問題を引き起こす他の場所はLAST_VALUEです。, LAST_VALUEは、フレームの最後の行から式を返します。 デフォルトのフレーム（無制限の先行行と現在行の間の範囲）は現在の行にのみ上がるので、フレームの最後の行は計算が実行されている行です。,

    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    カスタマイズを選択します。
    salesorderid,
    totaldue,
    last_value(salesorderid)over(partition by customerid
    order by salesorderid)as lastorderid,
    last_value(salesorderid)over(partition by customerid
    order by salesorderid
    現在の行と無制限のフォローの間の行)
    as correctlastorderid
    販売から。,SalesOrderHeader
    ORDER BY CustomerID,SalesOrderID;

    ウィンドウ集計

    T-SQLウィンドウ関数の最も便利な機能の一つは、非集計式に集計式を追加する機能です。集計クエリ。 残念ながらとらえることができるよう行が低調に推移しました。 問題を確認するには、多数の論理読み取りが表示される統計IO結果を確認する必要があります。, 多数の行に対して同じクエリ内で異なる粒度で値を返す必要がある場合の私のアドバイスは、共通テーブル式（CTE）、一時テーブル、または変数などの古いテクニックのいずれかを使用することです。 Window aggregateを使用する前に事前に集計することが可能な場合は、別のオプションです。,ウィンドウ集計と別の手法の違いを示します。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    salesorderid,
    totaldue,
    sum(totaldue)over()as overalltotal
    売上から選択します。,SalesOrderHeader
    WHERE YEAR(OrderDate)=2013;
    DECLARE@OverallTotal MONEY;
    セールスから@OverallTotal=SUM(TotalDue)
    を選択します。SalesOrderHeader
    WHERE YEAR(OrderDate)=2013;
    Select SalesOrderID,
    TotalDue,
    @OverallTotal AS OverallTotal
    売上から。,SalesOrderHeader AS SOH
    WHERE YEAR(OrderDate)=2013;

    最初のクエリはテーブルを一度だけスキャンしますが、ワークテーブルには28,823の論理読み取りがあります。 第二の方法は、テーブルを二度スキャンしますが、ワークテーブルは必要ありません。

    次の例では、集計式に適用されるwindows集計を使用します。

    集計問合せでウィンドウ関数を使用する場合、式はSELECT句およびORDER BY句と同じ規則に従, この場合、ウィンドウ関数はSUM(TotalDue)に適用されます。 これはネストされた集計のように見えますが、実際には集計式に適用されるウィンドウ関数です。

    ウィンドウ関数が適用される前にデータが集計されているため、パフォーマンスは良好です。

    ウィンドウ集計の使用について知っておくべきもう一つ興味深いことがあります。 一致するOVER句定義を使用する複数の式を使用すると、パフォーマンスがさらに低下することはありません。

    私のアドバイスは、この機能を慎重に使用することです。, それは非常に便利ですが、それはうまくスケールしません。

    パフォーマンス比較

    これまでに提示された例では、小さな売上高を使用しています。AdventureWorksからSalesOrderHeaderテーブルを作成し、実行計画と論理読み取りを確認しました。 実際の生活では、顧客は実行計画や論理読み取りを気にせず、クエリの実行速度を気にします。 実行時間の違いをよりよく見るために、私はAdam MachanicのThinking Big（Adventure）スクリプトをひねりを加えて使用しました。

    このスクリプトは、30万行を超えるbigTransactionHistoryというテーブルを作成します。, 実行後、アダムスクリプトで作成した二つの部の表15 7.5百万行です。 また、クエリエディターの実行後に結果を破棄プロパティをオンにして、グリッドに入力しても実行時間に影響しないようにしました。 各テストを三回実行し、各実行の前にバッファキャッシュをクリアしました。,

    (
    ProductId,
    TransactionDate
    )
    INCLUDE
    (
    Quantity,
    ActualCost
    );
    CREATE NONCLUSTERED INDEX IX_ProductId_TransactionDate
    ON smallTransactionHistory
    (
    ProductId,
    TransactionDate
    )
    INCLUDE
    (
    Quantity,
    ActualCost
    );

    I can’t say enough about how important it is to use the frame when it’s supported., 違いを確認するために、私は四つの方法を使用して実行中の合計を計算するためのテストを実行しました：

    • カーソルソリューション
    • 相関サブクエリ
    • デフォルトのフレームを持つウィンドウ関数
    • 行を持つウィンドウ関数

    私は三つの新しいテーブルでテストを実行しました。 チャート形式の結果は次のとおりです。

    ROWSフレームで実行すると、7.5million rowテーブルは、テストを実行するときに使用していたシステム 30万行のテーブルを実行するのに約一分かかりました。, id=”4b64f77d3b”>

    1
    2
    3
    4
    5
    SELECT ProductID, SUM(ActualCost) OVER(PARTITION BY ProductID
    ORDER BY TransactionDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS RunningTotal
    FROM bigTransactionHistory;

    I also performed a test to see how window aggregates performed compared to traditional techniques., この場合、30million rowテーブルだけを使用しましたが、同じ粒度、したがって同じOVER句を使用して一、二、または三の計算を実行しました。 Window aggregateのパフォーマンスをCTEおよび相関サブクエリと比較しました。

    ウィンドウの集計は、それぞれのケースで約1.75分、最悪の結果を出しました。 CTEは、計算の数を増やすときに最高のパフォーマンスを発揮しました。, 相関サブクエリは、各計算を別々に実行する必要があるため、計算の数を増やすと悪化し、テーブルに合計四回タッチしました。,v>

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    WITH Calcs AS (
    SELECT ProductID,
    AVG(ActualCost) AS AvgCost,
    MIN(ActualCost) AS MinCost,
    MAX(ActualCost) AS MaxCost
    FROM bigTransactionHistory
    GROUP BY ProductID)
    SELECT O.,ProductID,
    ActualCost,
    AvgCost,
    MinCost,
    MaxCost
    bigTransactionHistoryからOとして
    O.ProductID=CalcsにCalcsを結合します。ProductID;

    結論

    T-SQLウィンドウ関数は、パフォーマンスに優れているとして昇格されました。 私の意見は、筆記問合せやりやすいのですが、必要なものを理解しておくこともとても良い。 割り出しができるに違いができませんの作成指数は各クエリにします。, フレーミングは理解しにくいかもしれませんが、大きなテーブルにスケールアップする必要がある場合は非常に重要です。

    コメントを残す

    メールアドレスが公開されることはありません。 * が付いている欄は必須項目です