最も一般的なSQLのアンチパターンは何ですか?


リレーショナルデータベースを扱う私たち全員が、SQLが異なるということを学んだり(または学習している) 望ましい結果を引き出し、効率的に実行するには、不慣れなパラダイムを学習することによって部分的に特徴づけられる退屈なプロセスが必要であり、最もよく知られているプログラミングパターンのいくつかがここでは機能しないことがわかります。 あなたが見た(または自分がコミットした)一般的な反パターンは何ですか?

Answers


私は一貫して、データアクセス層にUIロジックを混在させるほとんどのプログラマの傾向に失望しています。

SELECT
    FirstName + ' ' + LastName as "Full Name",
    case UserRole
        when 2 then "Admin"
        when 1 then "Moderator"
        else "User"
    end as "User's Role",
    case SignedIn
        when 0 then "Logged in"
        else "Logged out"
    end as "User signed in?",
    Convert(varchar(100), LastSignOn, 101) as "Last Sign On",
    DateDiff('d', LastSignOn, getDate()) as "Days since last sign on",
    AddrLine1 + ' ' + AddrLine2 + ' ' + AddrLine3 + ' ' +
        City + ', ' + State + ' ' + Zip as "Address",
    'XXX-XX-' + Substring(
        Convert(varchar(9), SSN), 6, 4) as "Social Security #"
FROM Users

通常、プログラマはデータセットをグリッドに直接バインドする予定であり、SQL Serverの形式はクライアント側の形式よりもサーバー側の方が便利なので、これを実行します。

上記のようなクエリは、データレイヤをUIレイヤに密接に結合するため、非常に脆弱です。 さらに、このプログラミング方式は、ストアドプロシージャが再利用されることを完全に防ぎます。

ここに私のトップ3です。

フィールドリストの指定に失敗しました。 (編集:混乱を避けるため、これはプロダクションコードのルールです。私が作者でない限り、これは単発の解析スクリプトには当てはまりません)。

SELECT *
Insert Into blah SELECT *

すべきである

SELECT fieldlist
Insert Into blah (fieldlist) SELECT fieldlist

カーソルとwhileループを使用すると、ループ変数を持つwhileループが実行されます。

DECLARE @LoopVar int

SET @LoopVar = (SELECT MIN(TheKey) FROM TheTable)
WHILE @LoopVar is not null
BEGIN
  -- Do Stuff with current value of @LoopVar
  ...
  --Ok, done, now get the next value
  SET @LoopVar = (SELECT MIN(TheKey) FROM TheTable
    WHERE @LoopVar < TheKey)
END

3.文字列型によるDateLogic。

--Trim the time
Convert(Convert(theDate, varchar(10), 121), datetime)

すべきである

--Trim the time
DateAdd(dd, DateDiff(dd, 0, theDate), 0)

私は最近、「1つのクエリが2つより優れています」という最近のスパイクを見てきました。

SELECT *
FROM blah
WHERE (blah.Name = @name OR @name is null)
  AND (blah.Purpose = @Purpose OR @Purpose is null)

このクエリには、パラメータの値に応じて2つまたは3つの異なる実行計画が必要です。 1つの実行計画だけが生成され、このSQLテキストのキャッシュにスタックされます。 その計画は、パラメータの値に関係なく使用されます。 断続的に性能が低下します。 2つのクエリ(意図した実行計画ごとに1つのクエリ)を書く方がはるかに優れています。

  • 人間が読めるパスワードフィールド 、egad。 自己説明的。

  • インデックス付きのに対してLIKEを使用すると、一般的にLIKEというだけのことが嫌です。

  • SQL生成のPK値をリサイクルする。

  • 驚きは誰もまだ神のテーブルを言及なかった。 100列のビットフラグ、大きな文字列、整数などの「有機的」とは何も言いません。

  • 次に、 " i.ini ファイルがありません"というパターンがあります:大規模なテキストフィールドにCSV、パイプ区切り文字列、またはその他の解析に必要なデータを格納します。

  • そして、MS SQLサーバーでは、カーソルの使用はまったくありません。 任意のカーソルタスクを実行するためのより良い方法があります。

そんなにたくさんあるので編集!

それを深く掘り下げる必要はありません:準備されたステートメントを使用しないでください。

無意味なテーブルエイリアスを使う:

from employee t1,
department t2,
job t3,
...

大きなSQL文を必要以上に難読化する

私のbugbearsは、誰かが正しくデータ構造を正規化する方法を知らないために存在するマネージングディレクターの親友のdog groomerとdodgyルックアップテーブルの8歳の息子によってまとめられた450列のアクセステーブルです。

通常、このルックアップテーブルは次のようになります。

ID INT,
Name NVARCHAR(132),
IntValue1 INT,
IntValue2 INT,
CharValue1 NVARCHAR(255),
CharValue2 NVARCHAR(255),
Date1 DATETIME,
Date2 DATETIME

私はこのような嫌悪に頼るシステムを持っているクライアントの数を失ってしまった。

私が一番嫌いなものは

  1. 私はCamelCaseやunder_scores、単数形や複数形、大文字または小文字は問題ありませんが、特に[間違っている場合](はい、私はこれに踏み込んだ)本当に私を苛立たせる。

  2. 非正規化データ。 テーブルを完全に正規化する必要はありませんが、現在の評価スコアまたは主要なものについての情報を持つ従業員のテーブルを実行すると、ある時点で別のテーブルを作成する必要があります。同期させてください。 私はまずデータを正規化し、非正規化が助けになる場所を見たらそれを検討します。

  3. ビューまたはカーソルの過度使用。 ビューには目的がありますが、各テーブルがビューにラップされていると、ビューが大きすぎます。 私はカーソルを数回使用しなければなりませんでしたが、一般的にこれには他のメカニズムを使用することができます。

  4. アクセス。 プログラムが反パターンになることはありますか? 私の仕事ではSQL Serverを使用していますが、技術的でないユーザーには使いやすさと使いやすさという理由で多くの人がアクセスしています。 ここに入るにはあまりにも多くのことがありますが、あなたが同じような環境にいるのなら、あなたは知っています。

一時テーブルとカーソルの過度使用。

ストアプロシージャ名のプレフィックスとしてSPを使用します。これは、カスタムプロシージャ名ではなくシステムプロシージャの場所で最初に検索するためです。

時間値を格納するには、UTCタイムゾーンのみを使用する必要があります。 現地時間は使用しないでください。

select some_column, ...
from some_table
group by some_column

結果がsome_columnでソートされると仮定します。 私はこれを仮定していますが、今のところSybaseで少し見てきました。

SCOPE_IDENTITY()の代わりに@@ IDENTITYを使用する

この回答から引用:

  • @@ IDENTITYは、現在のセッション内のテーブルに対して生成された最後のID値をすべてのスコープにわたって返します。 それはスコープを越えているので、ここで注意する必要があります。 現在のステートメントではなく、トリガーから値を取得できます。
  • SCOPE_IDENTITYは、現在のセッション内の任意のテーブルおよび現在のスコープに対して生成された最後のID値を返します。 一般的にあなたが使いたいものです。
  • IDENT_CURRENTは、任意のセッションおよびスコープ内の特定の表に対して生成された最後のID値を戻します。 これにより、上記の2つのテーブルが必要なものではない場合(非常にまれ)に、値を使用するテーブルを指定できます。 これは、レコードを挿入していないテーブルの現在のIDENTITY値を取得する場合に使用します。

(例えば、 'Fax'フィールドにユーザーデータを保存するなど)意図していなかったものに「死んだ」フィールドを再使用する - 非常に速い修正として魅力的です!

SELECT FirstName + ' ' + LastName as "Full Name", case UserRole when 2 then "Admin" when 1 then "Moderator" else "User" end as "User's Role", case SignedIn when 0 then "Logged in" else "Logged out" end as "User signed in?", Convert(varchar(100), LastSignOn, 101) as "Last Sign On", DateDiff('d', LastSignOn, getDate()) as "Days since last sign on", AddrLine1 + ' ' + AddrLine2 + ' ' + AddrLine3 + ' ' + City + ', ' + State + ' ' + Zip as "Address", 'XXX-XX-' + Substring(Convert(varchar(9), SSN), 6, 4) as "Social Security #" FROM Users

または、すべてを1つの行に詰め込む。

  • FROM TableA, TableB WHERE構文ではなくJOINSのFROM TableA INNER JOIN TableB ON

  • クエリがORDER BY句を入れずに特定の方法でソートされたと仮定すると、それはクエリツールでのテスト中に表示されたのと同じだからです。

リストを完成させるために、自分の現在のお気に入りをここに置く必要があります。 私の好きな反パターンはクエリをテストしていません

これは、

  1. クエリに複数のテーブルが含まれています。
  2. あなたはあなたが最適な設計のクエリを持っていると思っていますが、あなたの前提をテストするのは面倒ではありません。
  3. 最初のクエリは受け入れられますが、最適化されているかどうかはわかりません。

また、非典型的または不十分なデータに対して実行されるテストではカウントされません。 ストアドプロシージャの場合は、テストステートメントをコメントに入れて、結果とともに保存します。 それ以外の場合は、コード内のコメントに結果を挿入します。

逆説的な見解:正常化への過剰な執着。

ほとんどのSQL / RBDBシステムは、非正規化されたデータであっても非常に便利な機能(トランザクション、レプリケーション)を1つ多く提供します。 ディスク容量は安く、フェッチされたデータを操作/フィルタリング/検索するためには、1NFスキーマを作成してその中のすべての面倒を処理する(複雑な結合、厄介な副選択、など)。

私は過度に正規化されたシステムが早期の最適化、特に初期の開発段階であることが判明しました。

(もっと考えてみてください ... http://writeonly.wordpress.com/2008/12/05/simple-object-db-using-json-and-python-sqlite/

彼らのキャリアの最初の6ヶ月でSQLを学び、今後10年間は​​何も学ぶことはありません。 特に、ウィンドウ処理/分析SQL機能の学習や効果的な使用は避けてください。 特にover()とby partitionの使用。

集計関数のようなウィンドウ関数は、定義された集合(グループ)の行に対して集計を実行しますが、グループごとに1つの値を返すのではなく、各グループに対して複数の値を返すことができます。

ウィンドウ関数の概要については、 O'Reilly SQL Cookbook付録Aを参照してください。

一時テーブル乱用

特にこの種のもの:

SELECT personid, firstname, lastname, age
INTO #tmpPeople
FROM People
WHERE lastname like 's%'

DELETE FROM #tmpPeople
WHERE firstname = 'John'

DELETE FROM #tmpPeople
WHERE firstname = 'Jon'

DELETE FROM #tmpPeople
WHERE age > 35

UPDATE People
SET firstname = 'Fred'
WHERE personid IN (SELECT personid from #tmpPeople)

クエリから一時テーブルを作成せず、必要のない行のみを削除してください。

そして、はい、私はこの形式のコードのページをプロダクションDBで見ました。

1)私はそれが "公式の"反パターンであるかどうかはわかりませんが、私は嫌いで、文字列リテラルをデータベース列の魔法の値として避けようとしています。

MediaWikiのテーブル 'image'の例

img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", 
    "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
img_major_mime ENUM("unknown", "application", "audio", "image", "text", 
    "video", "message", "model", "multipart") NOT NULL default "unknown",

(私はちょうど別のケーシング、回避する別のものに気づく)

私はint主キーを持つテーブルImageMediaTypeとImageMajorMimeへのintルックアップのようなケースを設計します。

2)特定のNLS設定に依存する日付/文字列変換

CONVERT(NVARCHAR, GETDATE())

フォーマット識別子なし

クエリ内の同じサブクエリ。

私はちょうどここにSQLの応答のいくつかに基づいて、この1つをまとめる。

イベントハンドラがOOPにあるように、データベースに対するトリガーと考えることは重大な反パターンです。 トランザクション(イベント)がテーブル上で発生すると、古いロジックをトリガに入れて起動できるという認識があります。

違います。 大きな相違点の1つは、トリガは、行操作ではなく、セット操作で同期するため、複数のトリガが同期していることです。 OOP側では、正反対のイベントは非同期トランザクションを効率的に実装する方法です。

  • 変更されたビュー - あまりに頻繁に変更され、通知または理由なしで変更されるビュー。 変更は、最も不適切な時間に気付かれるか、間違っていて、気づかれることはありません。 誰かがその列のより良い名前を考えたので、アプリケーションが壊れるかもしれません。 ルールビューは、消費者との契約を維持しながら基本テーブルの有用性を拡張する必要があります。 問題を修正しますが、新しいビューを作成するために、フィーチャを追加したり、変更の動作を悪化させたりしないでください。 他のプロジェクトとビューを共有しないように軽減するには、プラットフォームが許す限りCTEを使用します。 あなたのショップにDBAがある場合は、おそらくビューを変更することはできませんが、その場合はすべてのビューが古くなったり無駄になります。

  • !Paramed - クエリには複数の目的がありますか? 恐らくそれを読む次の人は深い瞑想まで知りません。 たとえそれが今必要でないとしても、デバッグするのに「ちょうど」あったとしても、あなたは意志です。 パラメータを追加するとメンテナンス時間が短縮され、物事をDRYに保ちます。 where句がある場合は、パラメータが必要です。

  • ケースなしのケース -

    SELECT  
    CASE @problem  
      WHEN 'Need to replace column A with this medium to large collection of strings hanging out in my code.'  
        THEN 'Create a table for lookup and add to your from clause.'  
      WHEN 'Scrubbing values in the result set based on some business rules.'  
        THEN 'Fix the data in the database'  
      WHEN 'Formating dates or numbers.'   
        THEN 'Apply formating in the presentation layer.'  
      WHEN 'Createing a cross tab'  
        THEN 'Good, but in reporting you should probably be using cross tab, matrix or pivot templates'   
    ELSE 'You probably found another case for no CASE but now I have to edit my code instead of enriching the data...' END  

任意のコメントなしでストアドプロシージャまたは関数...

テンポラリテーブル、特にSQL ServerからOracleに切り替える人は、一時テーブルを過度に使用する傾向があります。 ネストしたselect文を使用するだけです。

SQLアプリケーション(個々のクエリとマルチユーザーシステムの両方)を高速または低速にする要因について、良い考えを持たずにクエリを作成する開発者。 これには次のことに関する無知が含まれます:

  • ほとんどのクエリのボトルネックはI / OがCPUではないため、物理I / Oの最小化戦略
  • 異なる種類の物理ストレージアクセス(たとえば、シーケンシャルI / Oのロットが多数の小さなランダムI / Oよりも高速になりますが、物理ストレージがSSDの場合はそうではありません)
  • DBMSが貧弱なクエリプランを生成する場合、クエリを手作業でチューニングする方法
  • データベースパフォーマンスの低下を診断する方法、遅いクエリを「デバッグする」方法、クエリプランを読み込む方法(または選択したDBMSに応じてEXPLAIN)
  • スループットを最適化し、マルチユーザーアプリケーションのデッドロックを回避するための戦略をロックする
  • データセットの処理を処理するバッチ処理やその他のトリックの重要性
  • ベストバランスのスペースとパフォーマンスに、テーブルとインデックスの設計(例えば、小さなインデックスを保ち、インデックスをカバーすることができ、必要な最小サイズにデータの種類を減らす、など)

私はほとんど見つけ、およびパフォーマンスの面で大幅なコストを持つことができる2つがあります:

  • カーソルの代わりに、セットベースの表現を使用します。私は、プログラマがprocedurely考えているとき、この1つは頻繁に発生すると思います。

  • 派生テーブルに参加すると、相関サブクエリを使用すると、仕事をすることができます。

見せかけのISAM(索引順次アクセス方法)パッケージとしてSQLを使用しました。具体的には、ネスティングカーソルの代わりに、単一の、より大きないえ、ステートメントにSQL文を組み合わせます。実際には、オプティマイザが行うことができます多くはありませんので、これはまた、「オプティマイザの乱用」としてカウントされます。これが最大の非効率性のために非プリペアドステートメントと組み合わせることができます。

DECLARE c1 CURSOR FOR SELECT Col1, Col2, Col3 FROM Table1

FOREACH c1 INTO a.col1, a.col2, a.col3
    DECLARE c2 CURSOR FOR
        SELECT Item1, Item2, Item3
            FROM Table2
            WHERE Table2.Item1 = a.col2
    FOREACH c2 INTO b.item1, b.item2, b.item3
        ...process data from records a and b...
    END FOREACH
END FOREACH

正解は(ほとんど常に)1に2つのSELECT文を組み合わせることです。

DECLARE c1 CURSOR FOR
    SELECT Col1, Col2, Col3, Item1, Item2, Item3
        FROM Table1, Table2
        WHERE Table2.Item1 = Table1.Col2
        -- ORDER BY Table1.Col1, Table2.Item1

FOREACH c1 INTO a.col1, a.col2, a.col3, b.item1, b.item2, b.item3
    ...process data from records a and b...
END FOREACH

ダブルループバージョンへの唯一の利点は、内側のループが終了しているため、簡単に表1の値との間に休憩を見つけることができるということです。これは、コントロールブレークレポートで要因となることができます。

また、アプリケーションでソートすることは通常はありません-ではありません。

私はちょうどこのように、ビューの定義に出くわしました:

CREATE OR REPLACE FORCE VIEW PRICE (PART_NUMBER, PRICE_LIST, LIST_VERSION ...)
AS
  SELECT sp.MKT_PART_NUMBER,
    sp.PRICE_LIST,
    sp.LIST_VERSION,
    sp.MIN_PRICE,
    sp.UNIT_PRICE,
    sp.MAX_PRICE,
...

ビュー内の50かそこらの列があります。一部の開発者は、その1が対応するビュー内のどの列に把握できるようにするために、両方の場所に列オフセットをカウントする必要があり、列の別名を提供しないことにより、他の人を拷問小さな誇りを持っています。