T-SQLプログラマーの7つの大罪

読みやすいコードを書くだけでは十分ではありません。コードをすばやく実行する必要もあります。

このようなT-SQLコードを記述するための3つの基本的なルールがあり、うまく機能します。 これらは累積的です-これらのすべてのルールの実装は、コードにプラスの影響を与えます。 それらのいずれかをスキップまたは変更すると、コードのパフォーマンスに悪影響を及ぼす可能性が高くなります。

• データストレージ構造に基づいて書き込みます。datetime型のデータを格納する場合は、varcharなどではなくdatetimeを使用します。
• インデックスの可用性に基づいて記述します。インデックスがテーブル上に構築され、そこにある必要がある場合は、これらのインデックスを最大限に活用できるようにコードを記述します。 クラスター化インデックスが1つだけであることを確認し、テーブルごとに最も効率的な方法で使用されるようにします。
• クエリオプティマイザーを支援するために書く：クエリオプティマイザーはDBMSのエキサイティングな部分です。 残念ながら、たとえば、ある表現が別の表現から、次に別の表現からデータを受け取った場合など、ネストされた表現を含むなど、解析するのが「難しい」クエリを書くことで、彼の仕事を非常に複雑にすることができます。 オプティマイザーがどのように機能するかを時間をかけて理解し、害を及ぼさずに役立つようにクエリを作成します。

T-SQLコードでよくある間違いがいくつかあります-しないでください。

間違ったデータ型を使用する

理論的には、このエラーを回避することは非常に簡単ですが、実際には非常に一般的です。 たとえば、データベースで何らかのタイプのデータを使用しています。 パラメータと変数で使用してください！ はい、SQL Serverはあるデータ型を別のデータ型に暗黙的にキャストできることを知っています。 ただし、暗黙的な型変換が発生した場合、または自分で列のデータ型を別の型に変換する場合は、列全体に対して変換を実行します。 WHERE句または結合条件の列に対してこの変換を実行すると、常にテーブルスキャンが表示されます。 この列には優れたインデックスを構築できますが、この列に格納されている値に対してCASTを実行して、たとえば、この列に格納されている日付を条件で使用したchar型と比較するため、インデックスは使用されません。

信じられない？ このクエリを見てみましょう。

SELECT e.BusinessEntityID, e.NationalIDNumber FROM HumanResources.Employee AS e WHERE e.NationalIDNumber = 112457891; 

よく書かれた非常にシンプル。 このテーブルで作成されたインデックスでカバーする必要があります。 しかし、実行計画は次のとおりです。



このクエリは十分に高速で、テーブルは小さいため、インデックスのスキャンに必要な読み取りは4回だけです。 SELECTステートメントの小さな感嘆符に注意してください。 そのプロパティに目を向けると、以下が表示されます。



そうだね。 これは、実行計画に影響する型変換が進行中であるという警告（SQL Server 2012の新機能）です。 要するに、これはリクエストが間違ったデータ型を使用しているためです。

 SELECT e.BusinessEntityID, e.NationalIDNumber FROM HumanResources.Employee AS e WHERE e.NationalIDNumber = '112457891'; 

そして、次のクエリ実行プランを取得します。



そして、ここでは、4つではなく2つの読み取り操作のみが使用されています。 そして、はい、私は要求を非常に速く、少し速くしたことを理解しています。 しかし、何百万もの行がテーブルに格納されている場合はどうなりますか？ ええ、それから私はヒーローになります。

正しいデータ型を使用してください。

結合条件の準備およびWHERE句での関数の使用

関数といえば、結合条件または引数として列を渡すWHERE式で使用される関数のほとんどは、インデックスの適切な使用を妨げます。 引数として列を取る関数を使用するクエリの速度がどれほど遅いかがわかります。 以下に例を示します。

 SELECT a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID FROM Person.Address AS a WHERE '4444' = LEFT(a.AddressLine1, 4) ; 

この関数LEFTは、引数として列を受け取り、この実行計画に変換します。



その結果、必要なデータを見つけるために316回の読み取り操作が実行され、9ミリ秒かかります（非常に高速なディスクを使用しています）。 これは、「4444」がこの関数によって返されるすべての行と比較する必要があるためです。 SQL Serverはテーブルを単にスキャンすることさえできず、行ごとにLEFTを実行する必要があります。 ただし、次のようなことができます。

 SELECT a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID FROM Person.Address AS a WHERE a.AddressLine1 LIKE '4444%' ; 

そして、ここではまったく異なる実行計画が表示されます。



クエリには3回の読み取りと0ミリ秒が必要です。 さて、客観性のために1ミリ秒にします。 これにより、パフォーマンスが大幅に向上します。 そしてすべては、インデックスによる検索に使用できる関数を使用しているためです（以前はsargeableと呼ばれていました- 一般に翻訳不可能な単語：SARG-Search Arguments -able、関数がSARGeableの場合-列を次のように渡すことができます）とにかく、引数とインデックスシークが使用されます。SARGeableではない場合は-悲しいことに、インデックススキャンが常に使用されます-約翻訳者 ）。 いずれの場合でも、WHERE式または検索条件で関数を使用しないでください。または、インデックスによる検索条件で使用できる関数のみを使用してください。

マルチステートメントUDFの使用

ロシア語版msdnの複数ステートメントUDFは、「いくつかの命令で構成されるユーザー定義関数」とほぼ翻訳されますが、私の意見では、どうやら奇妙に聞こえます。したがって、見出しおよび本文全体で、この用語の翻訳を避けようとしました-約。 翻訳者

基本的に、彼らはあなたをtrapにかけます。 一見すると、この素晴らしいメカニズムにより、T-SQLを実際のプログラミング言語として使用できます。 これらの関数を作成して別の関数から呼び出すことができ、これらの古いストアドプロシージャとは異なり、コードを再利用できます。 これはすごい。 大量のデータに対してこのコードを実行しようとするまで。

これらの関数の問題は、テーブル変数に基づいて構築されることです。 テーブル変数を意図した目的に使用する場合、テーブル変数は非常に便利です。 一時テーブルとは明らかな違いが1つあります。統計はそれらに基づいて構築されません。 この違いは非常に役立つ場合があります。 統計情報がない場合、オプティマイザーは、テーブル変数またはUDFで実行されるクエリは1行のみを返すと想定します。 1行。 本当に複数の行が返されると便利です。 しかし、ある日、数百または数千の行が返され、ユーザーは1つのUDFを別のUDFと組み合わせることにします...パフォーマンスは、非常に速く、非常に非常に強く低下します。

例は十分に大きいです。 以下にいくつかのUDFを示します。

 CREATE FUNCTION dbo.SalesInfo () RETURNS @return_variable TABLE ( SalesOrderID INT, OrderDate DATETIME, SalesPersonID INT, PurchaseOrderNumber dbo.OrderNumber, AccountNumber dbo.AccountNumber, ShippingCity NVARCHAR(30) ) AS BEGIN; INSERT INTO @return_variable (SalesOrderID, OrderDate, SalesPersonID, PurchaseOrderNumber, AccountNumber, ShippingCity ) SELECT soh.SalesOrderID, soh.OrderDate, soh.SalesPersonID, soh.PurchaseOrderNumber, soh.AccountNumber, a.City FROM Sales.SalesOrderHeader AS soh JOIN Person.Address AS a ON soh.ShipToAddressID = a.AddressID ; RETURN ; END ; GO CREATE FUNCTION dbo.SalesDetails () RETURNS @return_variable TABLE ( SalesOrderID INT, SalesOrderDetailID INT, OrderQty SMALLINT, UnitPrice MONEY ) AS BEGIN; INSERT INTO @return_variable (SalesOrderID, SalesOrderDetailId, OrderQty, UnitPrice ) SELECT sod.SalesOrderID, sod.SalesOrderDetailID, sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderDetail AS sod ; RETURN ; END ; GO CREATE FUNCTION dbo.CombinedSalesInfo () RETURNS @return_variable TABLE ( SalesPersonID INT, ShippingCity NVARCHAR(30), OrderDate DATETIME, PurchaseOrderNumber dbo.OrderNumber, AccountNumber dbo.AccountNumber, OrderQty SMALLINT, UnitPrice MONEY ) AS BEGIN; INSERT INTO @return_variable (SalesPersonId, ShippingCity, OrderDate, PurchaseOrderNumber, AccountNumber, OrderQty, UnitPrice ) SELECT si.SalesPersonID, si.ShippingCity, si.OrderDate, si.PurchaseOrderNumber, si.AccountNumber, sd.OrderQty, sd.UnitPrice FROM dbo.SalesInfo() AS si JOIN dbo.SalesDetails() AS sd ON si.SalesOrderID = sd.SalesOrderID ; RETURN ; END ; GO 

素晴らしい構造。 これにより、非常に単純なクエリを作成できます。 さて、例えばここに：

 SELECT csi.OrderDate, csi.PurchaseOrderNumber, csi.AccountNumber, csi.OrderQty, csi.UnitPrice FROM dbo.CombinedSalesInfo() AS csi WHERE csi.SalesPersonID = 277 AND csi.ShippingCity = 'Odessa' ; 

1つ、非常に単純な要求。 これは彼の実行計画で、これも非常に簡単です。



2.17秒しかかからず、148行を返し、1456の読み取り操作を使用します。 この関数のコストはゼロであり、テーブル（テーブル変数）のスキャンのみがリクエストのコストに影響することに注意してください。 うーん、本当ですか？ ゼロコストUDF実行ステートメントの背後にあるものを見てみましょう。 このリクエストは、キャッシュから関数実行プランを取得します。

 SELECT deqp.query_plan, dest.text, SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1, (deqs.statement_end_offset - deqs.statement_start_offset) / 2 + 1) AS actualstatement FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE deqp.objectid = OBJECT_ID('dbo.CombinedSalesInfo'); 

そして、ここで実際に何が起こるかです：



うわー、これらの小さな機能とテーブルのスキャンのもういくつかがここに隠されているように見えます。 さらに、Hash Match結合演算子は、tempdbに書き込みを行い、実行にかなりのコストがかかります。 別のUDFの実行計画を見てみましょう。



こっち！ そして今、クラスター化インデックススキャンが表示されます。このスキャンでは、多数の行がスキャンされます。 これはもはや素晴らしいことではありません。 一般に、このような状況全体では、UDFはますます魅力的ではないように見えます。 もし、私たちが直接知らないなら、テーブルに直接アクセスしてみてください。 このように、例えば：

 SELECT soh.OrderDate, soh.PurchaseOrderNumber, soh.AccountNumber, sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID JOIN Person.Address AS ba ON soh.BillToAddressID = ba.AddressID JOIN Person.Address AS sa ON soh.ShipToAddressID = sa.AddressID WHERE soh.SalesPersonID = 277 AND sa.City = 'Odessa' ; 

これで、このクエリを完了すると、まったく同じデータが取得されますが、2170ではなく310ミリ秒で完了します。さらに、SQL Serverは1456ではなく911の読み取りのみを実行します。 UDF

「より速く実行」設定を有効にする：ダーティリーディングの使用

過去に戻って、286個のプロセッサを搭載した古いコンピューターでは、いくつかの理由でフロントパネルにターボボタンがあったことを思い出すことができます。 誤って「圧迫」した場合、コンピュータはすぐに異常なほど遅くなり始めました。 したがって、最大帯域幅を確保するには、常にいくつかの機能をオンにする必要があることに気付きました。 同様に、多くの人が分離レベルREAD_UNCOMMITTEDとNO_LOCKヒントをSQL Serverのターボボタンとして見ています。 それらを使用する場合は、必ず確認してください-ほとんどすべての要求とシステム全体が高速になります。 これは、読み取り中にロックが課されたりチェックされたりしないという事実によるものです。 より少ないロック-より速い結果。 しかし...

クエリでREAD_UNCOMMITTEDまたはNO_LOCKを使用すると、ダーティリードが発生します。 これは、更新操作がまだ完了していない場合、「猫」ではなく「犬」を読むことができることを意味することを誰もが理解しています。 ただし、これに加えて、クエリの実行中にデータページが移動する可能性があり、これを回避するためにロックをかけることはないため、実際の行よりも多い行または少ない行を取得できます。 私はあなたのことは知りませんが、私が働いていたほとんどの企業では、ほとんどのシステムでのクエリのほとんどが完全なデータを返すと予想していました。 同じデータセットで実行された同じパラメータを使用した同じクエリでは、同じ結果が得られます。 NO_LOCKを使用する場合ではありません。 これを確認するには、 この投稿を読むことをお勧めします。

リクエストでのヒントの不当な使用

ヒントを使用することについて、人々はあまりにも急いで決定します。 最も一般的な状況は、ヒントがリクエストの1つで、非常にまれな問題の解決に役立つ場合です。 しかし、人々がこのリクエストで大幅なパフォーマンスの向上を見ると...彼らはすぐにそれをどこでも一般的にポップし始めます。

たとえば、多くの人は、LOOP JOINがテーブルを結合する最良の方法だと考えています。 彼らはこの結論に達します。なぜなら、それはほとんどの場合、小さくて迅速なクエリに見られるからです。 したがって、彼らはSQL ServerにLOOP JOINの使用を強制することにしました。 これはまったく複雑ではありません。

 SELECT s.[Name] AS StoreName, p.LastName + ', ' + p.FirstName FROM Sales.Store AS s JOIN sales.SalesPerson AS sp ON s.SalesPersonID = sp.BusinessEntityID JOIN HumanResources.Employee AS e ON sp.BusinessEntityID = e.BusinessEntityID JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID OPTION (LOOP JOIN); 

この要求は101ミリ秒で実行され、4115の読み取り操作を実行します。 一般に、悪くはありませんが、このヒントを削除すると、同じリクエストが90ミリ秒で実行され、2370の読み取り値のみが生成されます。 システムの負荷が高いほど、ヒントを使用しないリクエストの有効性がより明白になります。

別の例を示します。 多くの場合、テーブルにインデックスを作成し、問題の解決を期待します。 そこで、リクエストがあります：

 SELECT * FROM Purchasing.PurchaseOrderHeader AS poh WHERE poh.PurchaseOrderID * 2 = 3400; 

ここでも問題は、列変換を行うときに、単一のインデックスが適切に使用されないことです。 クラスタ化インデックススキャンが進行中のため、パフォーマンスが低下します。 そのため、人々は自分のインデックスが使用されていないことに気付いたとき、次のことを行います。

 SELECT * FROM Purchasing.PurchaseOrderHeader AS poh WITH (INDEX (PK_PurchaseOrderHeader_PurchaseOrderID)) WHERE poh.PurchaseOrderID * 2 = 3400; 

そして今、彼らはクラスター化されたものではなく、選択したインデックスのスキャンを取得するので、インデックスは「使用」されていますよね？ しかし、クエリのパフォーマンスは変化しています-現在、11回の読み取り操作の代わりに44回実行されています（どちらも約0ミリ秒の実行時間を持っています。非常に高速なディスクがあるためです）。 「使用済み」は使用されていますが、意図したとおりではありません。 この問題の解決策は、次のようにリクエストを書き換えることです。

 SELECT * FROM Purchasing.PurchaseOrderHeader poh WHERE PurchaseOrderID = 3400 / 2; 

インデックス検索が使用されているため、読み取り操作の数は2つに減りました。インデックスは正しく使用されています。

他のすべての可能なオプションがテストされ、肯定的な結果が得られていない場合、クエリのヒントは常に最後に適用する必要があります。

クエリ実行結果の行ごとの処理の使用（「行による行の処理」処理）

行単位の処理は、セットの操作ではなく、WHILEサイクルのカーソルまたは操作を使用して実行されます。 使用すると、パフォーマンスは非常に低くなります。 カーソルは一般的に2つの理由で使用されます。 1つ目はコードで行ごとの処理を使用することに慣れている開発者で、2つ目はOracleから来た開発者で、カーソルは良いことだと考えています。 理由が何であれ、カーソルは芽の生産性を低下させます。

失敗したカーソルの典型的な例を次に示します。 特定の基準で選択された製品の色を更新する必要があります。 発明されたものではありません-かつて最適化する必要があったコードに基づいています。

 BEGIN TRANSACTION DECLARE @Name NVARCHAR(50) , @Color NVARCHAR(15) , @Weight DECIMAL(8, 2) DECLARE BigUpdate CURSOR FOR SELECT p.[Name] ,p.Color ,p.[Weight] FROM Production.Product AS p ; OPEN BigUpdate ; FETCH NEXT FROM BigUpdate INTO @Name, @Color, @Weight ; WHILE @@FETCH_STATUS = 0 BEGIN IF @Weight < 3 BEGIN UPDATE Production.Product SET Color = 'Blue' WHERE CURRENT OF BigUpdate END FETCH NEXT FROM BigUpdate INTO @Name, @Color, @Weight ; END CLOSE BigUpdate ; DEALLOCATE BigUpdate ; SELECT * FROM Production.Product AS p WHERE Color = 'Blue' ; ROLLBACK TRANSACTION 

各反復で、2つの読み取り操作を実行します。基準を満たす製品の数は数百です。 私のマシンでは、負荷がかかりません。実行時間は1秒以上です。 特にこのリクエストの書き換えは非常に簡単なので、これはまったく受け入れられません。

 BEGIN TRANSACTION UPDATE Production.Product SET Color = 'BLUE' WHERE [Weight] < 3 ; ROLLBACK TRANSACTION 

現在、15回の読み取り操作のみが実行され、実行時間は1ミリ秒のみです。 笑わないでください。 人々はそのようなコードを書くことが多く、さらに悪いことです。 カーソルは避けなければならず、カーソルなしではできない場合にのみ使用する必要があります。たとえば、さまざまなテーブルまたはデータベースを「実行」する必要があるメンテナンスタスクなどです。

ネストされたビューの不当な使用

ビューに接続する他のビューにリンクするビューに接続するビューにリンクするビュー...ビューは単なるクエリです。 しかし、テーブルとして扱うことができるため、人々はそれらをテーブルとして考え始めることができます。 しかし、無駄に。 あるビューを別のビューに接続したり、3番目のビューを参照したりすると、どうなりますか？ リクエストを実行するための非常に複雑な計画を作成しているだけです。 オプティマイザーはそれを単純化しようとします。 彼はすべてのテーブルを使用しない計画を試みますが、計画の選択にかかる時間は限られており、計画が複雑になればなるほど、かなり単純な実装計画になりにくくなります。 そして、パフォーマンスの問題はほとんど避けられません。

たとえば、ビューを定義する単純なクエリのシーケンスを次に示します。

 CREATE VIEW dbo.SalesInfoView AS SELECT soh.SalesOrderID, soh.OrderDate, soh.SalesPersonID, soh.PurchaseOrderNumber, soh.AccountNumber, a.City AS ShippingCity FROM Sales.SalesOrderHeader AS soh JOIN Person.Address AS a ON soh.ShipToAddressID = a.AddressID ; CREATE VIEW dbo.SalesDetailsView AS SELECT sod.SalesOrderID, sod.SalesOrderDetailID, sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderDetail AS sod ; CREATE VIEW dbo.CombinedSalesInfoView AS SELECT si.SalesPersonID, si.ShippingCity, si.OrderDate, si.PurchaseOrderNumber, si.AccountNumber, sd.OrderQty, sd.UnitPrice FROM dbo.SalesInfoView AS si JOIN dbo.SalesDetailsView AS sd ON si.SalesOrderID = sd.SalesOrderID ; 

そして、ここでテキストの作者はリクエストを示すのを忘れましたが、コメントでそれを与えます（翻訳者のメモ）：

 SELECT csi.OrderDate FROM dbo. CominedSalesInfoView csi WHERE csi.SalesPersonID = 277 

その結果、リクエストは155ミリ秒実行され、965の読み取り操作を使用します。 彼の実行計画は次のとおりです。



特に7000行を取得しているので、見栄えが良いので、すべて正常に動作しているようです。 しかし、そのようなリクエストを実行しようとするとどうなりますか：

 SELECT soh.OrderDate FROM Sales.SalesOrderHeader AS soh WHERE soh.SalesPersonID = 277 ; 

そして今、要求は3ミリ秒で完了し、685の読み取り操作を使用します-それはまったく異なります。 そして、彼の実行計画は次のとおりです。



ご覧のとおり、オプティマイザーは、クエリの単純化プロセスの一部として、すべての余分なテーブルを破棄することはできません。 したがって、フォアグラウンドには、インデックススキャンとデータを一緒に収集するハッシュマッチという2つの追加操作があります。 ビューを使用せずにこのクエリを記述することにより、SQL Serverを不要な作業から節約できます。 また、この例は非常に単純であり、実際のクエリのほとんどははるかに複雑であり、パフォーマンスの問題がはるかに大きくなることに注意してください。

この記事へのコメントには、少し論争があります。その本質は、Grant（記事の著者）が標準のAdventureWorksデータベースではなく、類似のデータベースで要求を満たしたようですが、実装計画が最適ではないためです。 「最後のセクションのクエリは、実験を自分で行って見ることができるものとは異なります。 ご注意 翻訳者。
どこかで私があまりにも縛られていて（できれば）テキストが理解しにくい場合、または何でも最高の言葉遣いを提供できる場合は、すべてのコメントを喜んで聞きます。