Jakie są najczęstsze anty-wzorce SQL? [zamknięte]

Oto moje top 3.

Numer 1. Brak określenia listy pól. (Edit: aby zapobiec zamieszaniu: jest to reguła kodu produkcyjnego. Nie dotyczy to jednorazowych skryptów analitycznych - chyba, że jestem autorem.)

SELECT *
Insert Into blah SELECT *

Powinno być

SELECT fieldlist
Insert Into blah (fieldlist) SELECT fieldlist

Numer 2. Używając kursora i pętli while, gdy zrobi to pętla while ze zmienną pętli.

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

Numer 3. DateLogic poprzez typy łańcuchowe.

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

Powinno być

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

Widziałem ostatnio skok " jednego pytanie jest lepsze niż dwa, amiright?"

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

To zapytanie wymaga dwóch lub trzech różnych planów wykonania w zależności od wartości parametrów. Tylko jeden plan wykonania jest generowany i zatrzymany w pamięci podręcznej dla tego tekstu sql. Plan ten będzie używany niezależnie od wartości parametrów. Powoduje to sporadyczne słabe wyniki. Znacznie lepiej jest napisać dwa zapytania(jedno zapytanie na plan wykonania).

• Czytelne dla człowieka pola haseł , egad. To oczywiste.

• Używanie Jak wobec indeksowanych felietony, a mnie prawie kusiło po prostu powiedz jak w ogóle.

• Recykling wartości PK wygenerowanych przez SQL.

• Niespodzianka nikt nie wspomniał o god-table yet. Nic nie mówi "organiczne" jak 100 kolumn bitu flagi, Duże ciągi i liczby całkowite.

• Potem jest " tęsknię .ini files" pattern: storage CSVs, pipe rozdzielone ciągi lub inny parse wymagane dane w dużych polach tekstowych.

• Oraz dla MS SQL server zastosowanie Kursory w ogóle . Jest lepszy sposób wykonania dowolnego zadania kursora.

edytowane, bo jest ich tak wiele!

Nie trzeba kopać głęboko: nie używać prepared statements.

Używanie bezsensownych aliasów tabel:

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

Sprawia, że czytanie dużej instrukcji SQL jest o wiele trudniejsze niż musi być

var query = "select COUNT(*) from Users where UserName = '" 
            + tbUser.Text 
            + "' and Password = '" 
            + tbPassword.Text +"'";

1. ślepo ufając wejściu użytkownika
2. nie używa parametryzowanych zapytań
3. hasła Cleartext

Moje pluskwy to tabele dostępu do kolumn 450, które zostały połączone przez 8-letniego syna najlepszego przyjaciela dyrektora zarządzającego i podejrzaną tabelę wyszukiwania, która istnieje tylko dlatego, że ktoś nie wie, jak prawidłowo znormalizować strukturę danych.

Zazwyczaj ta tabela wyszukiwania wygląda tak:

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

Straciłem rachubę liczby klientów, których widziałem, którzy mają systemy, które polegają na takich obrzydliwościach.

Te, których najbardziej Nie lubię to

1. Używanie spacji podczas tworzenia tabel, kół zębatych itp. Jestem w porządku z CamelCase lub under_scores i liczby pojedynczej lub mnogiej i wielkich lub małych liter, ale odwołanie się do tabeli lub kolumny[ ze spacjami], zwłaszcza jeśli [jest dziwnie spacja] (tak, wpadłem na to) naprawdę irytuje mnie.

2. Dane Denormalizowane. Tabela nie musi być idealnie znormalizowana, ale kiedy natknę się na tabelę pracowników, która zawiera informacje o ich aktualny wynik oceny lub ich podstawowe cokolwiek, to mówi mi, że prawdopodobnie będę musiał zrobić oddzielną tabelę w pewnym momencie, a następnie spróbować utrzymać je zsynchronizowane. Najpierw znormalizuję dane, a potem, jeśli zobaczę miejsce, w którym denormalizacja pomaga, rozważę to.

3. Nadużywanie widoków lub kursorów. Widoki mają swój cel, ale gdy każda tabela jest zawinięta w widok, jest to zbyt wiele. Kilka razy musiałem używać kursorów, ale generalnie można użyć innych mechanizmów za to.

4. Dostęp. Czy program może być anty-wzorcem? Mamy SQL Server w mojej pracy, ale wiele osób korzysta z dostępu ze względu na jego dostępność, "łatwość obsługi" i "przyjazność" dla nietechnicznych użytkowników. Jest tu zbyt wiele do zrobienia, ale jeśli byłeś w podobnym środowisku, wiesz.

Nadużywanie tymczasowych tabel i kursorów.

Użyj SP jako prefiksu nazwy procedury sklepu, ponieważ najpierw będzie wyszukiwać w lokalizacji procedur systemowych, a nie niestandardowych.

Do przechowywania wartości czasu należy używać tylko strefy czasowej UTC. Nie należy stosować czasu lokalnego.

Używanie @ @ IDENTITY zamiast SCOPE_IDENTITY ()

Cytat z ta ODPOWIEDŹ :

• @ @ IDENTITY zwraca ostatnią wartość tożsamości wygenerowaną dla dowolnej tabeli w bieżącej sesji, we wszystkich zakresach. Musisz być ostrożna, bo to jest poza zasięgiem. Możesz uzyskać wartość z wyzwalacza, zamiast bieżącego wyciągu.
• SCOPE_IDENTITY zwraca ostatnią wartość tożsamości wygenerowaną dla dowolnej tabeli w bieżącej sesji i obecny zakres. Ogólnie to, czego chcesz użyć.
• IDENT_CURRENT zwraca ostatnią wartość tożsamości wygenerowaną dla określonej tabeli w dowolnej sesji i dowolnym zakresie. Pozwala to określić, z której tabeli chcesz uzyskać wartość, na wypadek, gdyby powyższe dwie nie były dokładnie tym, czego potrzebujesz(bardzo rzadko). Możesz tego użyć, jeśli chcesz uzyskać bieżącą wartość tożsamości dla tabeli, do której nie wstawiono rekordu.

Ponowne użycie "martwego" pola do czegoś, do czego nie było przeznaczone (np. przechowywanie danych użytkownika w polu "Faks") - bardzo kuszące jako szybka poprawka!

select some_column, ...
from some_table
group by some_column

I zakładając, że wynik będzie posortowany według some_column. Widziałem to trochę z Sybase gdzie założenie trzyma (na razie).

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

• Składnia FROM TableA, TableB WHERE dla łączników zamiast FROM TableA INNER JOIN TableB ON

• Założenie, że zapytanie zostanie zwrócone w określony sposób, bez umieszczania klauzuli ORDER BY, tylko dlatego, że tak było podczas testowania w narzędziu zapytań.

Muszę umieścić tutaj swój aktualny ulubiony, aby lista była kompletna. Mój ulubiony antypattern to nie testowanie Twoich zapytań.

Dotyczy to sytuacji, gdy:

1. Twoje zapytanie obejmuje więcej niż jedną tabelę.
2. myślisz, że masz optymalny projekt dla zapytania, ale nie trudź się testować swoich założeń.
3. akceptujesz pierwsze zapytanie, które działa, nie mając pojęcia, czy jest nawet bliskie optymalizacji.

I wszelkie testy prowadzone przeciwko nietypowe lub niewystarczające dane się nie liczą. Jeśli jest to procedura składowana, umieść instrukcję testową w komentarzu i zapisz ją wraz z wynikami. W przeciwnym razie umieść go w komentarzu w kodzie z wynikami.

Nauka SQL w ciągu pierwszych sześciu miesięcy kariery i nigdy nie uczenie się niczego innego przez następne 10 lat. W szczególności nie uczenie się lub efektywne korzystanie z funkcji okienkowych / analitycznych SQL. W szczególności użycie over () i partition by.

Funkcje okien, jak agregat funkcji, wykonać agregację na zdefiniowany zbiór (Grupa) wierszy, ale zamiast zwracać jedną wartość na Grupa, funkcje okna mogą zwracać wiele wartości dla każdego Grupa.

Pogląd przeciwny: nadmierna obsesja normalizacji.

Większość systemów SQL / RBDBs oferuje wiele funkcji (transakcje, replikacja), które są bardzo przydatne, nawet przy nieznormalizowanych danych. Miejsce na dysku jest tanie, a czasami może być prostsze (łatwiejszy kod, szybszy czas rozwoju) manipulowanie / filtrowanie / wyszukiwanie pobranych danych, niż zapisywanie schematu 1NF i radzenie sobie ze wszystkimi kłopotami w nim zawartymi (złożone połączenia, paskudne podwyzki, itp.).

Znalazłem nadmiernie znormalizowane systemy są często przedwczesną optymalizacją, zwłaszcza na wczesnych etapach rozwoju.

(więcej na ten temat... http://writeonly.wordpress.com/2008/12/05/simple-object-db-using-json-and-python-sqlite/)

Tymczasowe nadużywanie stołu.

Konkretnie tego typu rzeczy:

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)

Nie buduj tymczasowej tabeli z zapytania, tylko usuwaj wiersze, których nie potrzebujesz.

I tak, widziałem strony kodu w tej formie w DBs produkcji.

Właśnie złożyłem ten jeden razem, na podstawie niektórych odpowiedzi SQL tutaj NA SO.

To poważny antypattern myśleć, że wyzwalacze są do baz danych, tak jak programy obsługi zdarzeń są do OOP. Istnieje takie przekonanie, że każda stara logika może być umieszczona w wyzwalaczach, które zostaną wyłączone, gdy transakcja (zdarzenie) dzieje się na stole.

Nieprawda. Jedną z dużych różnic jest to, że wyzwalacze są synchroniczne - z pewną odwagą, ponieważ są synchroniczne na ustawionej operacji, a nie na wierszu operacja. Po stronie OOP jest dokładnie odwrotnie-zdarzenia są efektywnym sposobem implementacji transakcji asynchronicznych.

1) Nie wiem, czy to "oficjalny" anty-wzorzec, ale nie lubię i staram się unikać liter ciągów jako magicznych wartości w kolumnie bazy danych.

Przykład z tabeli 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",

(po prostu zauważam inną obudowę, inną rzecz, której należy unikać)

Projektuję takie przypadki jak wyszukiwanie int do tabel ImageMediaType i ImageMajorMime z kluczami podstawowymi int.

2) Konwersja date / string, która opiera się na określonych ustawieniach NLS

CONVERT(NVARCHAR, GETDATE())

Bez formatu identyfikator

Identyczne zapytania podrzędne w zapytaniu.

• Zmieniony widok - widok, który jest zmieniany zbyt często i bez powiadomienia lub powodu. Zmiana zostanie albo zauważona w najbardziej nieodpowiednim czasie, albo co gorsza będzie zła i nigdy nie zauważona. Może Twoja aplikacja się zepsuje, bo ktoś wymyślił lepszą nazwę dla tej kolumny. Co do zasady poglądy powinny rozszerzać użyteczność tabel bazowych przy zachowaniu umowy z konsumentami. Napraw problemy, ale nie dodawaj funkcji ani co gorsza zmieniaj zachowanie, aby utworzyć nowy widok. Na ograniczaj nie dziel się poglądami z innymi projektami i używaj CTEs , gdy pozwalają na to Platformy. Jeśli twój sklep ma DBA, prawdopodobnie nie możesz zmienić widoków, ale wszystkie Twoje widoki będą nieaktualne i lub bezużyteczne w tym przypadku.

• The !Paramed - czy zapytanie może mieć więcej niż jeden cel? Prawdopodobnie, ale następna osoba, która to przeczyta, nie dowie się aż do głębokiej medytacji. Nawet jeśli nie potrzebujesz ich teraz, są szanse, że będziesz, nawet jeśli jest to "tylko" do debugowania. Dodawanie parametrów obniża konserwację czas i utrzymać rzeczy suche. Jeśli masz klauzulę where, powinieneś mieć parametry.

• The case for no CASE -

  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  
  

Procedury lub funkcje przechowywane bez komentarzy...

Umieszczanie rzeczy w tabelach tymczasowych, szczególnie ludzie, którzy przechodzą z SQL Server NA Oracle, mają zwyczaj nadużywania tabel tymczasowych. Wystarczy użyć zagnieżdżonych instrukcji select.

Dwa, które znajduję najbardziej i mogą mieć znaczny koszt pod względem wydajności, to:

• Używanie kursorów zamiast zestawu ekspresja. Wydaje mi się, że to zdarza się często, gdy programista myśli proceduralnie.

• Za pomocą skorelowanych zapytań podrzędnych, gdy join to a derived table can do praca.

Programiści, którzy piszą zapytania bez dobrego pojęcia o tym, co sprawia, że aplikacje SQL (zarówno pojedyncze zapytania, jak i systemy dla wielu użytkowników) są szybkie lub wolne. Obejmuje to ignorancję na temat:

• fizyczne strategie minimalizacji We/Wy, biorąc pod uwagę, że wąskim gardłem większości zapytań jest we / wy, a nie PROCESOR
• Wpływ różnych rodzajów fizycznego dostępu do pamięci masowej (np. wiele sekwencyjnych we/wy będzie szybszych niż wiele małych losowych We / Wy, chociaż mniej, jeśli fizyczna pamięć masowa jest SSD!)
• Jak ręcznie dostroić zapytanie, jeśli DBMS produkuje zły plan zapytań
Jak zdiagnozować słabą wydajność bazy danych, jak "debugować" powolne zapytanie i jak odczytać Plan zapytań (lub wyjaśnić, w zależności od wybranego DBMS)
• strategie blokowania w celu optymalizacji przepustowości i uniknięcia blokad w aplikacjach dla wielu użytkowników
• znaczenie dozowania i innych sztuczek do obsługi przetwarzania zbiorów danych
• KONSTRUKCJA tabeli i indeksu w celu najlepszego zrównoważenia przestrzeni i wydajności (np. pokrycie indeksów, utrzymywanie indeksów w miarę możliwości na małym poziomie, redukcja typów danych do niezbędnego minimalnego rozmiaru itp.)

Używanie SQL jako pakietu ISAM (Indexed Sequential Access Method). W szczególności, zagnieżdżanie kursorów zamiast łączenia wyrażeń SQL w jedną, choć większą, instrukcję. Liczy się to również jako "nadużycie optymalizatora", ponieważ w rzeczywistości optymalizator nie może wiele zrobić. Dla maksymalnej nieefektywności można to łączyć z nie przygotowanymi wypowiedziami:

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

Poprawnym rozwiązaniem (prawie zawsze) jest połączenie dwóch poleceń SELECT w jedno:

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

The only zaletą wersji z podwójną pętlą jest to, że możesz łatwo zauważyć przerwy między wartościami w tabeli 1, ponieważ wewnętrzna pętla się kończy. Może to być czynnik w raportach przerw kontroli.

Również sortowanie w aplikacji jest zwykle Nie-Nie.

Używanie kluczy podstawowych jako zastępczych dla adresów rekordów oraz kluczy obcych jako zastępczych dla wskaźników osadzonych w rekordach.