データウェアハウスなしで活動している、またはレポーティングのための分析データベースを分けている組織において、最新のデータの唯一のソースは、ライブ製造データベースにあるかもしれません。製造データベースにクエリを行う場合、最適化が重要です。非効率的なクエリは製造データベースのリソースに負担を増やし、クエリにエラーが含まれていれば、その他のユーザーに対して、パフォーマンスの低下やサービスの損失を生じる可能性があります。従って、データベースのパフォーマンスへの影響を最小限にするには、クエリを最適化することが重要です。

1.開始する前にビジネス要件を定義する

前の記事では、BI のためのビジネス要件を定義するためのベストプラクティスを取り扱いましたSQL クエリを最適化する際、次の作業を適用する必要があります。

  • 関連する利害関係者の特定 すべての関係者をクエリを開発するための議論に関係させるようにしてください。製造データベースにクエリを行う場合は、DBA チームを含めてください。
  • ビジネスの結果に重点を置きます。 クエリには確実で一意の目的が必要です。調査または重複報告書のために製造データベースに重い負担をかけることは不要な作業です。
  • 適切な要件のために議論の骨組みを作ります。 その対象の読者を特定することによって、報告書の機能と範囲を定義します。これはクエリの焦点を正しいレベルの詳細があるテーブルに合わせます。
  • 重要な質問をすることで、適切な要件を策定します。 この質問は通常、5つのWに従います – 誰 (Who)何 (What)どこ (Where)いつ (When)なぜ (Why)
  • 具体的な要件を書き、利害関係者と確認します。 製造データベースのパフォーマンスはあまりにも重要なため、不明確または曖昧な要件を含むことはできません。要件はできる限り具体的にし、クエリを実行する前にすべての利害関係者と確認します。

2.SELECT * の代わりに SELECT フィールドを定義する

説明クエリを実行する際、多くの SQL データベース開発者は、テーブルからすべての利用可能なデータにクエリを行うために、完結な表現として SELECT * (「すべて選択」と読めます) を使用します。しかし、テーブルに多くのフィールドや行がある場合、これは多くの不要なデータにクエリを行う際にデータベースリソースに重い負担をかけます。

SELECTステートメントでフィールドを定義すると、ビジネス要件を満たすために、データベースは必要なデータのみにクエリを行います。ビジネス要件が顧客のためのメーリングアドレスを要求する例を見てみましょう。

非効率的:

SELECT *
FROM Customers

このクエリは、電話番号や活動日、営業および顧客サービスからのメモなどの顧客のテーブルにも保存されたその他のデータを収集します。

効率的:

SELECT FirstName、LastName、Address、City、State、Zip
FROM Customers

このクエリは、メーリングアドレスのための必要な情報のみを収集します。

すべてのテーブルやフィールド名のインデックスを維持するために、INFORMATION_SCHEMA or ALL_TAB_COLUMNS などのシステムテーブルからクエリを実行します (MS SQL Server – 詳細はこちら)。

3.SELECT DISTINCT を回避するためにより多くのフィールドを選択する

SELECT DISTINCT は、クエリから重複内容を削除する簡単な方法です。SELECT DISTINCT は、異なる結果を生成するためのクエリにおけるすべてのフィールドをグループ化することによって機能します。しかし、この目的を達成するには、多くの処理能力が必要です。さらに、データは不正確になるまでグループ化される可能性があります。SELECT DISTINCT の使用を回避するには、 より多くのフィールドを選択し、一意の結果を生成する必要があります。

非効率的で不正確:

SELECT DISTINCT FirstName、LastName、State
FROM Customers

このクエリは、同じ氏名を持つ同じ状態で複数の人間を説明しません。デビッド・スミスやダイアン・ジョンソンなどのよくある名前はグループ化され、レコードの数が不正確になります。より大きいデータベースで、多くのデビッド・スミスやダイアン・ジョンソンによって、クエリの実行が遅くなります。

効率的で正確:

SELECT FirstName、LastName、Address、City、State、Zip
FROM Customers

より多くのフィールドを追加することによって、SELECT DISTINCT を使用することなく、重複しないレコードが返されました。データベースは、フィールドをグループ化する必要がなく、レコードの数は正確です。


動作中の Sisense:

Quality Assurance Project Status - Software Dashboard

4.WHEREでなく、INNER JOIN で結合を作成する

SQL開発者の中には、次のような WHERE 句で結合を作成することを好む人もいます。

SELECT Customers.CustomerID、Customers.Name、Sales.LastSaleDate
FROM Customers, Sales
WHERE Customers.CustomerID = Sales.CustomerID

このタイプの結合は、直積集合または交差結合とも呼ばれるデカルト結合を作成します。デカルト結合では、変数のすべての考えられる組み合わせが作成されます。この例では、合計で 1,000 のセールスのある 1,000 の顧客がいる場合、クエリはまず、1,000,000 の結果を生成し、次に CustomerID が正確に結合される 1,000 のレコードをフィルタリングします。データベースが必要な作業の 100 倍の作業を行っているため、これはデータベースリソースの非効率的な使用例です。デカルト結合は特に、2 つの大きなテーブルのデカルト結合は数 10 億、数兆の結果を生成する可能性があるため、大規模なデータベースにおいて問題です。

デカルト結合の作成を防ぐには、代わりに INNER JOIN を使用する必要があります。

SELECT Customers.CustomerID、Customers.Name、Sales.LastSaleDate
FROM Customers
   INNER JOIN Sales
   ON Customers.CustomerID = Sales.CustomerID

データベースは、CustomerID が等しい必要な 1,000 のレコードのみを生成します。

DBMS システムの中には、WHERE 結合も認識し、代わりに INNER JOIN としてそれらを自動的に実行します。そうした DBMS システムでは、WHERE 結合と INNER JOIN の間でパフォーマンスに差はありません。しかし、INNER JOIN は、すべての DBMS システムによって認識されます。あなたの環境で最高の結果が得られるよう、あなたの DBA からアドバイスが得られるでしょう。

5.HAVING の代わりに WHERE を使用してフィルターを定義する

上記のコンセプトと同様に、効率的なクエリの目標は、データベースから必要なレコードのみを収集することです。SQL 動作オーダーあたり、HAVING ステートメントは WHERE ステートメントの後に計算されます。意図が条件に基づいてクエリをフィルタリングすることであれば、WHERE ステートメントはより効率的です。

例えば、200 のセールスが 2016 年度にあり、2016 年の顧客ごとのセールスの数にクエリを行うとします。

SELECT Customers.CustomerID、Customers.Name、Count(Sales.SalesID)
FROM Customers
   INNER JOIN Sales
   ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.CustomerID, Customers.Name
HAVING Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#

このクエリは、セールステーブルから 1,000 のセールスレコードを収集し、2016 年に生成された 200 のレコードにフィルタリングを行い、最後にデータセット内のレコードをカウントします。

比較すると、WHERE 句は、収集されたレコードの数を制限します。

SELECT Customers.CustomerID、Customers.Name、Count(Sales.SalesID)
FROM Customers
  INNER JOIN Sales
  ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID、Customers.Name

このクエリは、2016 年から 200 のレコードを収集し、次にデータセット内のレコードをカウントします。HAVING 句での最初の手順が完全に除かれました。

HAVING は、集約されたフィールドでフィルタリングを行う場合にのみ使用する必要があります。上記のクエリでは、HAVING ステートメントを使用して 5 つ以上のセールスのある顧客に対してさらにフィルタリングを行います。

SELECT Customers.CustomerID、Customers.Name、Count(Sales.SalesID)
FROM Customers
   INNER JOIN Sales
   ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
HAVING Count(Sales.SalesID) > 5

6.句の最後でのみワイルドカードを使用する

町や名前などのプレーンテキストデータを検索する際、ワイルドカードはできる限り最も広い検索を作成します。しかし、最も広い検索は最も非効率的な検索でもあります。

特に末尾のワイルドカードと共に先頭のワイルドカードを使用すると、データベースは選択したフィールド内の任意の場所に対してすべてのレコードを検索します。

このクエリを検討して、「Char」で始まる町を収集します。

SELECT City FROM Customers
WHERE City LIKE ‘%Char%’

このクエリは、Charleston、Charlotte、Charlton の予想された結果を収集します。しかし、Cape Charles、Crab Orchard、Richardson などの予想されていない結果も収集します。

より効率的なクエリは次のようになります。

SELECT City FROM Customers
WHERE City LIKE ‘Char%’

このクエリは、Charleston、Charlotte、Charlton の予想された結果のみを収集します。

7.LIMITを使用してクエリ結果を標本抽出する

最初にクエリを実行する前に、LIMIT ステートメントを使用して、望ましく、意味のある結果を得られるようにします。(一部の DBMS システムでは、TOP という言葉が LIMIT と交互に使用されます。) LIMIT ステートメントは、指定したレコードの数のみを返します。LIMIT ステートメントを使用すれば、大きなクエリで製造データベースに重い負担をかけることを防ぐことができ、結局、クエリが編集または改良が必要なことが分かります。

2016 年上記からのセールスクエリにおいて、10 のレコードの制限を調査します。

SELECT Customers.CustomerID、Customers.Name、Count(Sales.SalesID)
FROM Customers
  INNER JOIN Sales
  ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
LIMIT 10

使用可能なデータセットがあるかどうかをサンプルで見てみましょう。

8.オフピーク時間中に分析クエリを実行する

製造データベースへのクエリの影響を最小限にするため、オフピーク時間にクエリを実行するスケジューリングについてDBAに相談します。クエリは、同時ユーザーが最少人数の時間帯(通常は午前3~5時の深夜)に実行する必要があります。

クエリが次の基準を多く持つほど、候補が夜に実行される可能性が増します。

  • 大きいテーブルからの選択 (>1,000,000 レコード)
  • デカルト結合または交差結合
  • ループステートメント
  • SELECT DISTINCT ステートメント
  • ネスト化したサブクエリ
  • 長い文章またはメモフィールドでのワイルドカード検索
  • 複数のスキーマクエリ
Tags: | |