Najlepszy sposób na sprawdzenie czy dany rekord istnieje w bazie.

Witam, zacznę od tego, że to nie jest zwykłe pytanie w stylu "jaki napisać kod SQL do sprawdzenia czy rekord istnieje w bazie danych", bo to akurat wiem, moje pytanie dotyczy tego która z podanych przeze mnie metod jest szybsza, poprawniejsza.

Mianowicie mam tabelę z osobami w której kluczem głównym jest numer dowodu osobistego.

CREATE TABLE IF NOT EXISTS visitors (doc_number VARCHAR(255) PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50));

Z racji tego, że kluczem głównym jest numer dowodu, jest od wpisywany ręcznie przez użytkownika, więc może się zdarzyć sytuacja, że podany numer już znajduje się w tabeli.

I tu dochodzimy do sedna. Czy lepiej jest zrobić to w ten sposób, że w momencie dodawania rekordu przez JDBC mam złapać wyjątek SQLException z errorCode = 19 <- w tym momencie wiem już, że taki rekord istnieje.

Czy lepiej napisać zapytanie w SQL w stylu:

SELECT doc_number From visitors WHERE doc_number = ?

i dopiero teraz sprawdzić, czy zapytanie zwróciło jakiś element?

Która z powyższych metod będzie efektywniejsza, lepsza? A może znacie jeszcze lepszy sposób na wykonanie tego zadania?

Ja robiłem to w ten sposób, że tworzyłem indeks wymuszający unikalność na danej kolumnie / grupie kolumn. Jeżeli operacja dodawania się nie udawała to sprawdzałem czy winne było naruszanie indeksu. Jeśli tak to rzucałem własnym wyjątkiem informującym o istnieniu indeksu.

Exception Driven Development to zło. Wyjątki nie służą do obsługi sterowania w aplikacji. Sytuacji gdy ręcznie zarządzamy PK i poraz wtóry chcemy go dodać nie jest wyjątkowa. Przynajmniej nie w prostym przepływie. Zatem dodatkowe zapytanie jest dobrym wyborem.
Cyrk zaczyna się gdy w jakiś "magiczny" sposób dwóch użytkowników chce dodać ten sam klucz i jeden z nich przegrywa wyścig:

A - sprawdza czy istnieje klucz - nie istnieje
B - sprawdza czy istnieje klucz - nie istnieje
A - dodaje - OK
B - dodaje - wyjątek.

Tu przydaje się mechanizm transakcji biznesowej pozwalający na blokowanie B już w pierwszym kroku. Sprawdzenie czy dany rekord istnieje powinno automatycznie zakładać lock na dany zestaw danych biznesowych tak by tylko jeden użytkownik mógł je obrabiać.

A ja się tu z @Koziołek zupełnie nie zgodzę.

1. Read before write - zło (wydajność).
2. Blokady na poziomie bazy - jeszcze gorsze zło (brak skalowania). Natrafienie na blokadę w RDBMSie z MVCC kończy się niemal zawsze ROLLBACKiem. Czyli i tak poleci wyjątek lub coś tego typu ;) Na ogół tego nie chcesz.
3. Blokady na poziomie biznesowym (długie transakcje) - prosta droga do katastrofy. Później są takie idiotyzmy, jak z edycją wniosków o granty w pewnym rządowym systemie informatycznym, że jak jedna osoba otworzy dokument i sobie pójdzie na fajkę, to inne są zablokowane i nie mogą pracować.

Sprawdzanie przed wyjątkiem ma sens tylko wtedy, jeśli absolutnie jesteś pewien, że tylko Ty jesteś jedynym użytkownikiem tej bazy.

Jeżeli nie potrzebujesz transakcji, IMHO optymistyczna próba dodania rekordu i łapanie wyjątku to najwydajniejsze i zarazem najprostsze rozwiązanie. Baza i tak sprawdza wewnętrznie, czy klucz istnieje w momencie dodania klucza. Po co robić to drugi raz z poziomu aplikacji, niepotrzebnie męcząc bazę zakładaniem dodatkowym sprawdzaniem i blokadami?

Jeszcze prostsze rozwiązanie - jako ID użyć UUID lub TimeUUID lub sekwencji i masz zagwarantowane, że klucze się nie powtórzą i nic nie trzeba sprawdzać ani nie ma problemu z wyjątkami :)

PS. Numer dowodu jako klucz główny to też bardzo, bardzo zły pomysł, ale to już tamat na osobnego posta.

@Koziołek:
Załóżmy, że mamy prosty CRUD do tabeli z kolumnami (a, b, c, d). Załóżmy, że (a) to techniczny, autoincrementujący się klucz. Kolumny (c,d) natomiast z przyczyn biznesowych muszą być unikalne, ale nie są na tyle intuicyjne, aby uczynić je primary key (poza tym klucze z większą ilością kolumn są mniej wygodne, a po typie BIGINTEGER szybciej się wyszukuje, a po to wszystkim chodzi). Jednak stosowanie ich jako klucza jest niewygodne. W takiej sytuacji na kolumny (c,d) zakładam indeks z dodatkową własnością UNIQUE.

Załóżmy, że mamy prostego CRUDa, w którym użytkownik dodaje sobie dane do wspomnianej tabeli. Bardzo rzadko, ale może się to zdarzyć następuje sytuacja, że narusza dodatkowy constraint wspólnej unikalności kolumn (c,d). Jak go o tym poinformować?

1 scenariusz:

• wykonuje SELECT, który sprawdzi czy spełnione są warunki do dodania rekordu. Jeżeli sytuacja nie pozwala, nie dodaje obiektu do bazy i wyświetlam odpowiedni komunikat (może dojść do wyścigu, o którym wspomniałeś).
  Największa wada: wykonuje ten SELECT zawsze. Jest to strata CPU, gdyż sytuacja jest wyjątkowa (nie występuje w 99% przypadków).
  2 scenariusz:
• dodaje obiekt:

try {
  em.persist(o);
  em.flush();
} catch (PersistanceException e) {
  if (e.getMessage().contains(UNIQUE_INDEX_NAME) {
	throw new MyUniqueIdx();
  }
}

W przypadku sytuacji wyjątkowej jaką jest naruszenie unikalnego indeksu wyświetlam komunikat błędu, a moja metoda informuje mnie, że występuję sytuacja wyjątkowa, którą mogę obsłużyć. W ten sposób mam banalny w utrzymaniu kod i co najważniejsze: czytelny.

Testowałem to jednym projekcie i rozwiązało mój problem z unikalnością pewnych kluczy.

To co piszecie Panowie jest bardzo ciekawe i na pewno przyda mi się w przyszłości, jednak nie zaznaczyłem w pierwszym poście, że aplikacja którą piszę będzie używana przez JEDNĄ osobę, więc większość problemów opisanych przez Was się raczej nie zdarzy. Pytanie zadałem gdyż w trakcie pisania wpadło mi do głowy i być może przyda się w późniejszych projektach. :)

@Krolik, jednak wolę zaryzykować dodatkowe zapytanie (wydajne bo po indeksie) niż użerać się z obsługą wyjątków. Generalnie nie podoba mi się sama koncepcja sterowania przepływem za pomocą wyjątków.
Kolejna sprawa to użycie sztucznego klucza głównego szczególnie gdy istnieje klucz naturalny. Wbrew pozorom nie będzie tu znaczących różnic w wydajności. Szczególnie jak weźmiemy pod uwagę, że klucz główny posiada indeks (kwestia użycia odpowiedniego indeksu). W dodatku taki klucz naturalny ma tą zaletę, że niesie w sobie już pewną wartość biznesową.

Co do transakcji po stronie aplikacji, to nie są one złe. Przykład, który podałeś dotyczy nieprawidłowej ich implementacji.

@NiktWażny oraz kod sterowany wyjątkami. Rzecz w tym, że kod choć wydaje się prost to może doprowadzić do kilku problemów z wydajnością. Obsługa wyjątku jest zawsze kosztowna. Jeżeli używasz ORMa, to jest szansa, że dane których szukasz znajdują się już cachu zatem ich odczyt jest dość tani. W dodatku jeżeli założyłeś unikalny indeks to jego przeszukanie jest znacznie tańsze niż obsługa wyjątków.

Zresztą ciekawe porównanie > http://www.depesz.com/2012/06/07/123-vs-depesz-what-is-faster/

A ile w tej bazie będzie docelowo rekordów ? Może nie ma o co kruszyć kopi bo tych kilku milisekund użytkownik i tak nie zauważy. Ogólnie jak Koziołek uważam, że z wyjątków należy korzystać w wyjątkowych sytuacjach

Może troszkę inny, ale podobny przykład:

Masz kod serwera, który pracuje z wysokimi uprawnieniami i ma udostępnić użytkownikowi dane z pliku. Ale narzucono zasadę, że mają być dostępne tylko pliki, których użytkownik jest właścicielem (lub ma jakieś tam uprawnienia). Zakładając, że serwer zna użytkownika (wymagana autentykacja), jak napiszesz kod do sprawdzania, czy użytkownik ma uprawnienia do pliku, czy nie?

''
odczytaj właściciela pliku (np. filestat)
if (właściciel pliku == zalogowany użytkownik)
wyślij plik użytkownikowi
else
wyświetl błąd access denied
''
Zagadka: gdzie w powyższym pseudokodzie kodzie jest dziura dot. bezpieczeństwa?

IMHO wyjątki tudzież sprawdzanie poprawności operacji poprzez próbę wykonania potencjalnie błędnej operacji, a nie zawczasu, ma czasem bardzo głęboki sens i pozwala uniknąć dość subtelnych błędów.

Chodzi o to, że właściciel pliku może się zmienić zanim wykona się warunek w if?