Cześć,
Chciałbym was zapytać czemu zapytania składowane w systemie zarządzania bazą danych są wydajniejsze od zapytań zagnieżdżonych w kodzie aplikacji?
Bardzo mnie interesuje ta kwestia.
Z góry dzięki

Bo w wielu systemach ich wyniki są keszowane.

A poza tym, to nie zawsze prawda... A raczej - nie ma na tak postawione pytanie odpowiedzi zero-jedynkowej.
@bananananafu Skąd to twierdzenie, jakiś konkretny przykład?
Jeśli porównujesz wydajność select, to oczywiście zwróciłeś uwagę na czas wysyłki danych do klienta i czas obróbki tych danych przez klienta? Jak spartolisz coś w aplikacji, to baz danych szybciej zwróci dane, niż aplikacja jest w stanie je odebrać.

Gdzieś tak kiedyś przeczytałem i teraz zacząłem potrzebować odpowiedzi na to pytanie ;p
Wyczytałem, że lepiej, zeby zapytania mielił silnik bazy danych, bo jest bardziej wydajny, ale to są tylko i wyłącznie rzeczy które kiedyś przeczytałem, chyba na tym forum

Zastanów się... Jeśli zapytanie wysyłasz z aplikacji do bazy danych, to baz danych go nie "mieli"?
A może chodzi Ci o operacje sortowania/filtrowania wykonywane w aplikacji vs baza danych?
Tak czy inaczej, dalej podtrzymuje to co napisałem wcześniej; to zależy...

Gdyby mnie to nie zastanawiało, to by nie było tego pytania. Naukę o bazach danych zacząłem niedawno, dlatego wolałem zapytać o zdanie tych, którzy wiedzą ode mnie więcej. Dzięki za wszystkie odpowiedzi:)

Padła już tu częściowo odpowiedź na to pytanie:

Marcin.Miga napisał(a):

Bo w wielu systemach ich wyniki są keszowane.

Ale nie tyle są keszowane wyniki co plany zapytań w wydajniejszy sposób niż zapytania Ad Hoc (procedury są kompilowane). Dane keszowane są i dla Ad Hoc i dla procedur w tzw buffer cachu jeśli mówimy o SQL Server.
Po drugie kwestia możliwości optymalizacji zapytań z aplikacji. Tu też często są ograniczone możliwości optymalizacji jakiś frameworków.
Być może utrzymanie, łatwiej jest poprawić procedurę w SQL niż wydawać nową wersję aplikacji, ale to już są zalety w kwestii organizacji pracy. Tak samo dla administratora SQL łatwiej analizować problemy wydajnościowe.
No to powiedzmy takie podstawowe zalety.

firefox napisał(a):

Padła już tu częściowo odpowiedź na to pytanie:

Marcin.Miga napisał(a):

Bo w wielu systemach ich wyniki są keszowane.

Ale nie tyle są keszowane wyniki co plany zapytań w wydajniejszy sposób niż zapytania Ad Hoc (procedury są kompilowane). Dane keszowane są i dla Ad Hoc i dla procedur w tzw buffer cachu jeśli mówimy o SQL Server.

Jeśli już mówimy o SQL Server, to dla zapytań sparametryzowanych plan zapytań jest kompilowany i keszowany w dokładnie ten sam sposób co do procedury składowanej. Rożnica jest taka, że dla procedury robi się to raz podczas "kompilacji", a dla zapytań ad-hoc podczas "preparacji".
Dopóki takie spreparowane zapytanie istnieje w danej sesji, doputy plan jest w keszu.
Szczerze? Tam gdzie nie ma absolutnej potrzeby (czyli np. skomplikowane przetwarzanie jakiś danych), nie używam procedur. Każde zapytanie z aplikacji jest parametryzowane, żadnego sklejania stringów i hardcodowania wartości parametrów. Takiego zapytania SQL Server nie jest w stanie optymalizować i trzymać dla nie planu zapytania w keszu. Podobnie jest i w innych bazach danych...

firefox napisał(a):

Po drugie kwestia możliwości optymalizacji zapytań z aplikacji. Tu też często są ograniczone możliwości optymalizacji jakiś frameworków.
Być może utrzymanie, łatwiej jest poprawić procedurę w SQL niż wydawać nową wersję aplikacji, ale to już są zalety w kwestii organizacji pracy. Tak samo dla administratora SQL łatwiej analizować problemy wydajnościowe.
No to powiedzmy takie podstawowe zalety.

To jest już ograniczenie danej technologii, a nie żadna przewaga SP.

Niechęć do stosowania zapytań (widoków) i procedur przechowywanych wynika pewnie z braku doświadczenia w pisaniu kodu SQL. Bez rozpisywania się już klika razy sam byłem zmuszony przenieść zapytania realizowane bezpośrednio w kodzie programu do bazy SQL właśnie ze względu na wydajność. Jedyną poważną wadą jest konieczność aktualizacji bazy (wykonywanie jakiś skryptów SQL czy ręcznie czy też automatycznie) przy okazji aktualizacji samej aplikacji.

cw1 napisał(a):

Niechęć do stosowania zapytań (widoków) i procedur przechowywanych wynika pewnie z braku doświadczenia w pisaniu kodu SQL.

Moim zdaniem jest dokładnie odwrotnie. SP (Stored Procedure - procedura składowana) zapewnia jednolity dostęp do danych niezależnie od aplikacji. I to jest zaleta, zwłaszcza dla kogoś kto tak naprawdę nie wie i nie rozumie co się dzieje w serwerze.
Miałem już do czynienia z systemem, który posiadał ponad 12 tyś procedur. Na wszystko. Utrzymanie tego czegoś to była dopiero "twórcza" praca...
Widziałem też inny, który dla zwykłej prostej operacji wyświetlenia listy dokumentów robił tak:

1. Odpalanie jednej procedury, która zwracała ID rekordów do listy
2. Odpalanie procedury ładowania wiersza, dla każdego wiersza z listy z pkt. 1 (sic!)
   Pewnie autor tego cuda tez przeczytał, że procedury są szybsze...

cw1 napisał(a):

Bez rozpisywania się już klika razy sam byłem zmuszony przenieść zapytania realizowane bezpośrednio w kodzie programu do bazy SQL właśnie ze względu na wydajność. Jedyną poważną wadą jest konieczność aktualizacji bazy (wykonywanie jakiś skryptów SQL czy ręcznie czy też automatycznie) przy okazji aktualizacji samej aplikacji.

Tu już nie wytrzymam... To bzdura. Nie ma prostej odpowiedzi, że to samo będzie działało szybciej tylko dlatego że jest w SP. Wiele zależy od tego, jak to było napisane.
Jeśli ktoś w aplikacji pobiera dane, a potem dla każdego wiersza wykonuje zapytanie, które coś tam dobiera z bazy, zamiast to przepisać na złączenia to sorry. Będzie to działało jak kupa. I wtedy faktycznie "przepisałem to na SP i teraz działa 100x szybciej" ma sens. Tylko, to nie do końca prawda...

Aktualizacje bazy danych to jest horror przy często zmieniającym się kodzie, gdzie tej logiki w bazie jest za dużo.
Wielokrotnie obserwuje duże systemy napisane wg "cała logika w bazie", że programiści producenta sami nie widzą co dokładnie się dzieje. Wszystko przez poplątany i zakręcony kod w serwerze, gdzie procedur jest za dużo, używane są wszędzie, zagnieżdża się wywołanie jednej SP w innej, a ta pierwsze wywołana jest przez ciąg triggerów...

I żeby była jasność. Nie jestem przeciwnikiem SP. Ale twierdzę, że trzeba podejść do tematu na zimno i pełnym zrozumieniem zasad. A tu mamy sytuację, gdzie "młody" oczekuje odpowiedzi zero-jedynkowej - co jest lepsze. Nie istnieje jednoznaczna odpowiedź na takie pytanie bez żadnego kontekstu!
A jeśli ktoś twierdzi, że SP są rozwiązaniem wszystkich (powiedzmy - większości) problemów z wydajnością itp. - to po prostu nie do końca ma pojęcie o czym mówi.

"byłem zmuszony przenieść zapytania realizowane bezpośrednio w kodzie programu do bazy SQL"

W wiekszosci w kodzie mozesz zrobic to samo to w bazie danych tylko trzeba znac interfejs (i zeby ten interfejs nie byl skopany). Takze bardziej obstawialbym ze Twoja znajomosc programowania i interfejsu jakiego uzywaliscie jest slaba niz to ze w bazie dziala szybciej niz z kodu

Fajna dyskusja się wywiązała, więc dorzucę swoje trzy grosze w temacie.
Generalnie chcemy mieć kod tani w utrzymaniu, więc przejrzysty, przykryty testami, łatwo poddający się automatycznemu refactoringowi, SOLID, DRY, KISS i tak dalej. Dlatego moim prywatnym zdaniem chciałbym mieć ORM zapewniający automatyczne mapowanie struktury bazy danych na klasy i powiązania pomiędzy klasami. Jeśli zostanie dołożone/usunięte/wyedytowane pole tabeli, pojawi się sp albo coś zmieni się w view - automat to wszystko ogarnie i przeważnie w przypadku niekompatybilnych zmian kod wywali się już na etapie kompilacji. Jeśli nie, to z dużym prawdopodobieństwem zrobią to testy. Logika biznesowa jest w jednym miejscu, zorganizowana w klasy (oby nieduże), nie trzeba robić protez typu wysyłanie maila czy komunikacja z webserwisem z poziomu sql. Do tego miejsca wygląda to tak, jak bym chciał, żeby to było.
Czasem jednak potrzebujemy zajebistej wydajności. Mamy np. kilkaset i więcej requestów na sekundę. Gdzie się da można aplikację przykryć cachem, CDN, Redisem itp, jednak istnieją sytuacje, kiedy te kilka tysięcy zapytań na sekundę musi trafić prosto do bazy. Można iść w farmę sql, można też wyciskać z bazy wszystko co się da, czasem jedno i drugie. Skoro zachodzi taka potrzeba, to optymalizuje się bazę danych i komunikację z nią, denormalizuje dane, przenosi część logiki do procedur składowanych, godzinami ślęczy się nad planami zapytań.

I tu lekcja, którą wyniosłem z ostatniej pracy, gdzie spier**one było wszystko - od architektury systemu, przez bazę danych i kod C#, do zarządzania wytwarzaniem kodu - lekcja jak się nie robi się oprogramowania. Priorytetem powinno być łatwe utrzymanie kodu, dopiero potem wydajność. Zastałem system, który został napisany w całości z myślą o wydajności - same sp, systemy różnych firm gadały ze sobą bezpośrednio przez bazę danych, po trzy osobne bazy danych dla każdego z klientów (a miało być ich kilkuset), jednocześnie brak dokumentacji, brak specyfikacji (!), nawet brak komentarzy przy commitach. OK, można by powiedzieć, że jest słabo, ale przynajmniej mamy szybki system (i tak faktycznie było), ale... rdzeń systemu, o którym mowa, został przykryty cache'ami w zewnętrznych aplikacjach, aplikacje przykryte blobami i CDN i raptem okazało się, że do rdzenia trafia raptem kilka RPS. Zostaliśmy z nieczytelnym, nieutrzymywalnym, nierozwojowym systemem, gdzie człowiek który napisał 95% tego gówna załamał się w końcu i odszedł z firmy zabierając prawie całą wiedzę na temat core'a. Ok, wynika to w głównej mierze z braku dokumentacji, testów i bardzo słabego kodu, ale te dwie rzeczy wzięły się z braku czasu na zadbanie o te rzeczy, a to dlatego, że tzw. przedwczesna optymalizacja (premature optimization) zjadła czas na inne rzeczy. Wniosek z tego taki, że trzeba bardzo BARDZO dobrze zastanowić się nad architekturą aplikacji, dobrze przemyśleć co będzie podlegało dużym obciążeniom i nie da się zbuforować, a dopiero potem siadać do optymalizacji. Zresztą jak wiadomo optymalizuje się tylko wąskie gardła, a nie cały kod (vide _13th_Dragon wciskający wszędzie preinkrementację).

Oczywiście można pisać cały system po stronie bazy danych (Oracle), można też bazę danych dotykać tylko długim kijkiem (EF), można iść w same SP, ale najlepiej jest po prostu bardzo dobrze przemyśleć całą architekturę w gronie kumatych i technicznych ludzi.

Do autora wątku - najpierw łatwe utrzymanie kodu, dopiero potem likwidacja wąskich gardeł. Z tego powodu SP, view, triggery tylko w uzasadnionych przypadkach.

Z wszystkimi opiniami nie sposób się nie zgodzić. Nigdy nie ma odpowiedzi w 100% tak lub nie szczególnie jak mówimy o technicznych tematach. Zgadzam się, że wszystko zależy od wielu czynników. Myślę, że wszyscy się zgodziliśmy, że SP są z reguły wydajniejsze. Takie zresztą było pytanie, więc chyba wszyscy zgodnie odpowiedzieli. Natomiast jasne, są sytuacje w których trzymanie kodu poza SQL będzie po prostu rozsądniejsze. Procedury można tak samo skopać i będą niewydajne tak jak i kod C#, że zamiast pobrać wszystkie dane i wrzucić do jakiegoś obiektu to pobierany jest rekord po rekordzie. Zamiast pobrać wymagany set danych to pobieramy całe tabele itp itd. Nie mówiąc o zawiłych koszmarkach generowanych przez framework.
Zrypać zawsze można czy to w SQL czy w aplikacji.
Zawsze jest potrzebny rozsądek.

firefox napisał(a):

Z wszystkimi opiniami nie sposób się nie zgodzić. Nigdy nie ma odpowiedzi w 100% tak lub nie szczególnie jak mówimy o technicznych tematach. Zgadzam się, że wszystko zależy od wielu czynników. Myślę, że wszyscy się zgodziliśmy, że SP są z reguły wydajniejsze.

Nie, ja się z tym nie zgadzam. W typowych zastosowaniach dla systemów OLTP, czyli implementacja CRUD przy pomocy SP, to niepotrzebny kod.
Założę się, że napiszę kod w aplikacji, który będzie zdecydowanie szybciej robił UPDATE na tabeli ze 100 polami, niż jakakolwiek procedura składowana.
Dlaczego? Ano dlatego, że:

1. Praktycznie nigdy nie ma potrzeby aktualizować wszystkich pól, często aktualizuje się tylko kilka z nich.
2. Do SP musisz przekazać wartości wszystkich parametrów i aktualizować jak leci. Słabo.
3. Mój DAC potrafi aktualizować tylko te dane, które się zmieniły. Dodatkowy preparuje zapytania i może je keszować, przez co utrzymuje skompilowany plan dla takich zapytań. W efekcie działa to bardzo wydajnie a jednocześnie jest efektywniejsze od "sztywnej" SP.
4. Przez to, że aktualizuje tylko te pole, które zostały zmienione - update potrafi być wielokrotnie szybszy. Zwłaszcza jak ktoś ma fantazję i aktualizuje dane przez widok w bazie i to najlepiej niezmaterializowany (czyli wyzwalacze aktualizujące do widoku).

firefox napisał(a):

Takie zresztą było pytanie, więc chyba wszyscy zgodnie odpowiedzieli.

Też się nie zgadzam, ponieważ pytanie był tendencyjne. OP pytał nie o to co jest szybsze, tylko dlaczego SP jest szybsza. A to drobna różnica, nieprawdaż?
A ja twierdzę, że to bez znaczenia a ewentualne różnice są pomijalne. Pod warunkiem, że zapytania ad-hoc są spreparowane.
Natomiast jestem przeciwnikiem pisania SP do wszystkiego, a zwłaszcza do realizacji operacji CRUD.
Oczywiście używam SP, ale tylko tam gdzie to ma sens - a więc tam, gdzie to jest niezbędne. I chcę ich mieć relatywnie niewiele w bazie danych.

firefox napisał(a):

Natomiast jasne, są sytuacje w których trzymanie kodu poza SQL będzie po prostu rozsądniejsze. Procedury można tak samo skopać i będą niewydajne tak jak i kod C#, że zamiast pobrać wszystkie dane i wrzucić do jakiegoś obiektu to pobierany jest rekord po rekordzie. Zamiast pobrać wymagany set danych to pobieramy całe tabele itp itd. Nie mówiąc o zawiłych koszmarkach generowanych przez framework.
Zrypać zawsze można czy to w SQL czy w aplikacji.
Zawsze jest potrzebny rozsądek.

I z tym się zgadzam :)

wloochacz napisał(a):

Praktycznie nigdy nie ma potrzeby aktualizować wszystkich pól, często aktualizuje się tylko kilka z nich.

Do SP musisz przekazać wartości wszystkich parametrów i aktualizować jak leci. Słabo.

Owszem, tylko ta różnica jest pomijalna dla pojedynczego update. To tylko wysłanie kilku(set) bajtów więcej. Niektóre ORMy tak robią, i nie ma tragedii.

Mój DAC potrafi aktualizować tylko te dane, które się zmieniły. Dodatkowy preparuje zapytania i może je keszować, przez co utrzymuje skompilowany plan dla takich zapytań. W efekcie działa to bardzo wydajnie a jednocześnie jest efektywniejsze od "sztywnej" SP.

DAC to przetwornik analogowo-cyfrowy. Zakładam, że nie o to Ci chodzi. ;)
Mógłbyś podać nazwę konkretnej technologii, która to zapewnia? Czy to Twój własny twór?

Przez to, że aktualizuje tylko te pole, które zostały zmienione - update potrafi być wielokrotnie szybszy.

Testowałeś to jakoś?

wloochacz napisał(a):

1. Praktycznie nigdy nie ma potrzeby aktualizować wszystkich pól, często aktualizuje się tylko kilka z nich.
2. Do SP musisz przekazać wartości wszystkich parametrów i aktualizować jak leci. Słabo.
3. Mój DAC potrafi aktualizować tylko te dane, które się zmieniły. Dodatkowy preparuje zapytania i może je keszować, przez co utrzymuje skompilowany plan dla takich zapytań. W efekcie działa to bardzo wydajnie a jednocześnie jest efektywniejsze od "sztywnej" SP.
4. Przez to, że aktualizuje tylko te pole, które zostały zmienione - update potrafi być wielokrotnie szybszy. Zwłaszcza jak ktoś ma fantazję i aktualizuje dane przez widok w bazie i to najlepiej niezmaterializowany (czyli wyzwalacze aktualizujące do widoku).

A policzyłeś czas od spreparowania/wygenerowania do wykonania? Czy liczysz tylko czas od wysłania już wygenerowanego zapytania po stronie aplikacji do jego wykonania? Policz pełny czas od request'a w aplikacji. Po drugie właśnie te parametry są kluczem bo dla dobrej procedury są budowane plany zapytań sparametryzowane.

Dodatkowo zapominamy o kwestiach bezpieczeństwa. Tak samo zminimalizowanie obciążenia sieci bo zamiast często gęsto wysyłać kilku KB kod wysyłamy tylko exec :)
Uważam temat za zamknięty, bo możemy na ten temat rozmawiać do rana i SQL'owcy powiedzą, że najlepsze są procedury a .NET'owcy, że ich zabawki :) Najczęściej praktyka mi mówi, że .NET mają takie zdanie z braku zaawansowanej wiedzy o T-SQL a SQL'owcy braku wiedzy o .NET. A może o ich doświadczeniu z developerami co bezgranicznie ufają frameworkom i bez limitu robią to co napisałem wyżej:)
Jak dobrze poszukamy w necie to są statystyki przy jakiś założeniach. Niestety, ale najlepiej wypadają te, które wywołują procedury składowane (zapewne nie w każdym przypadku - nie jest to żelazna zasada).
Raz jeszcze na koniec powtarzam, wszędzie należy zachować umiar, rozsądek a dobra architektura/model danych sam rozwiąże w praktyce naszą rozmowę :)

PS. Tabela, która posiada 100 kolumn jest chyba potworkiem a nie tabelą. Natomiast widziałem tabele zbudowane z 365 kolumn :) Niestety nadal nie znam uzasadnienia budowy takiej tabeli :)

somekind napisał(a):

wloochacz napisał(a):

Praktycznie nigdy nie ma potrzeby aktualizować wszystkich pól, często aktualizuje się tylko kilka z nich.

Do SP musisz przekazać wartości wszystkich parametrów i aktualizować jak leci. Słabo.

Owszem, tylko ta różnica jest pomijalna dla pojedynczego update. To tylko wysłanie kilku(set) bajtów więcej. Niektóre ORMy tak robią, i nie ma tragedii.

Wiem, że tak robią i czasem jest to poważny problem. Pół biedy, jak można to optymalizować, ale nic mnie tak nie wkurza jak naginanie logiki aplikacji do ograniczeń technologii vel kaskadowa aktualizacja (a raczej jej brak w pewnych przypadkach) w MS SQL.

somekind napisał(a):

Mój DAC potrafi aktualizować tylko te dane, które się zmieniły. Dodatkowy preparuje zapytania i może je keszować, przez co utrzymuje skompilowany plan dla takich zapytań. W efekcie działa to bardzo wydajnie a jednocześnie jest efektywniejsze od "sztywnej" SP.

DAC to przetwornik analogowo-cyfrowy. Zakładam, że nie o to Ci chodzi. ;)
Mógłbyś podać nazwę konkretnej technologii, która to zapewnia? Czy to Twój własny twór?

DAC - Data Access Components. Ale racja, powinienem napisać DAL...
Głównie pisze w Delphi i przede wszystkim korzystam z FireDAC + własne rozszerzenia.

somekind napisał(a):

Przez to, że aktualizuje tylko te pole, które zostały zmienione - update potrafi być wielokrotnie szybszy.

Testowałeś to jakoś?

Nie, nie testowałem - było gorzej, albo i lepiej jak kto woli...
Uważałem podobnie jak Ty teraz, do momentu aż klienci nie zaczęli kwękać, że coś wolno działa. Po krótkiej analizie i kilku testach wyszło na to co powiedziałem. Może nie jest to typowa prosta tabelka, jest to cokolwiek bardziej skomplikowane, ale co do zasady zgadza się w 100% z tym co napisałem wcześniej.

firefox napisał(a):

wloochacz napisał(a):

1. Praktycznie nigdy nie ma potrzeby aktualizować wszystkich pól, często aktualizuje się tylko kilka z nich.
2. Do SP musisz przekazać wartości wszystkich parametrów i aktualizować jak leci. Słabo.
3. Mój DAC potrafi aktualizować tylko te dane, które się zmieniły. Dodatkowy preparuje zapytania i może je keszować, przez co utrzymuje skompilowany plan dla takich zapytań. W efekcie działa to bardzo wydajnie a jednocześnie jest efektywniejsze od "sztywnej" SP.
4. Przez to, że aktualizuje tylko te pole, które zostały zmienione - update potrafi być wielokrotnie szybszy. Zwłaszcza jak ktoś ma fantazję i aktualizuje dane przez widok w bazie i to najlepiej niezmaterializowany (czyli wyzwalacze aktualizujące do widoku).

A policzyłeś czas od spreparowania/wygenerowania do wykonania? Czy liczysz tylko czas od wysłania już wygenerowanego zapytania po stronie aplikacji do jego wykonania? Policz pełny czas od request'a w aplikacji.

Oczywiście, że sprawdziłem. Zależy od zapytania, aczkolwiek bywa to kosztowne. Dlatego robię to raz i używam spreparowanego zapytania do wysyłania konkretnych komend SQL; a więc parametry są przygotowane a plan skompilowany.
Naprawdę uważasz, że nie wiem o czym piszę, prawda? Twoje prawo. Natomiast opisuję swoje doświadczenia i nie mam ochoty nikomu niczego udowadniać. Za długo w tym siedzę, żeby bawić się w takie przepychanki...

firefox napisał(a):

Po drugie właśnie te parametry są kluczem bo dla dobrej procedury są budowane plany zapytań sparametryzowane.

A dla zapytania ad-hoc nie są?
Pisze po raz 3 w tym wątku - jeśli zapytanie jest sparametryzowane, to jest traktowane identycznie jak SP. Nie używam innych ad-hoc query. Nie sklejam zapytania ze stringów...

firefox napisał(a):

Dodatkowo zapominamy o kwestiach bezpieczeństwa. Tak samo zminimalizowanie obciążenia sieci bo zamiast często gęsto wysyłać kilku KB kod wysyłamy tylko exec :)

O czym piszesz, bo nie rozumiem?

firefox napisał(a):

Uważam temat za zamknięty, bo możemy na ten temat rozmawiać do rana i SQL'owcy powiedzą, że najlepsze są procedury a .NET'owcy, że ich zabawki :) Najczęściej praktyka mi mówi, że .NET mają takie zdanie z braku zaawansowanej wiedzy o T-SQL a SQL'owcy braku wiedzy o .NET. A może o ich doświadczeniu z developerami co bezgranicznie ufają frameworkom i bez limitu robią to co napisałem wyżej:)
Jak dobrze poszukamy w necie to są statystyki przy jakiś założeniach. Niestety, ale najlepiej wypadają te, które wywołują procedury składowane (zapewne nie w każdym przypadku - nie jest to żelazna zasada).
Raz jeszcze na koniec powtarzam, wszędzie należy zachować umiar, rozsądek a dobra architektura/model danych sam rozwiąże w praktyce naszą rozmowę :)

PS. Tabela, która posiada 100 kolumn jest chyba potworkiem a nie tabelą. Natomiast widziałem tabele zbudowane z 365 kolumn :) Niestety nadal nie znam uzasadnienia budowy takiej tabeli :)

ORLY?
No popatrz... też mi się tak wydawało. Mam nieźle znormalizowaną bazę danych, z wyczuciem i bez przesady w gonieniu jakiejś tam postaci normalnej. I gdyby to była właśnie taka tabela, a nie złączenie 9 innych, to pewnie nie byłoby problemu. A że byłem leniwy, to zrobiłem z tego widok + SP aktualizujące.
Masakra wydajnościowa... Ale ok, nieważne. Ja mam inne wymagania i oczekiwania niż standardowa aplikacja OLTP.
A teksty o potworkach możesz sobie darować - mam taki a nie inny obiekt biznesowy, który ma tyle i nie mniej atrybutów. Wszystkie są istotne. I tak jest to potworek, ale raczej z gatunku wyzwań a nie złego projektu.

Kolego spokojnie, ja nie oceniam Twojej bazy i nie mówię, że Ty tworzysz potworki, ale widziałem zapytania SELECT wygenerowane przez framework, które nie raz musiałem optymalizować. 100x szybciej i prościej dało radę napisać to zapytanie w T-SQL.
Dlatego też napisałem, zawsze jest potrzebne wyczucie, to co sprawdzi się w jednym nie musi w drugim. Tak samo umiejętność jednego developera nie wskazuje umiejętności developera drugiego. Być może moje doświadczenie jest negatywne, ale to nie znaczy, że każdy developer piszę tak samo.
To samo odnosi się do procedur składowanych, widziałem takie, że miały zagnieżdżone kursory gdzie pojęcie wydajność autorowi nie była znana.

Raz jeszcze powtarzam - rozsądek.
Żeby nie było tak źle jak jest to są testy pokazujące przewagę własnego kodu .NET nad wbudowanymi funkcjami obliczeniowymi. Zatem to ponownie pokazuje, że nie ma jednego idealnego wyjścia, wszystko zależy od potrzeb i modelu danych.

PS. Co do niezrozumienia tematu bezpieczeństwa wczytaj się w treść raz jeszcze i pomyśl. Myślę, że dasz radę dojść do sensu tej wypowiedzi.

To, że sql wygenerowany przez ORM jest długi i w dodatku wygląda nieczytelnie nie oznacza, że będzie wykonywać się wolniej niż dopieszczony, śliczny sql napisany z palca.

Panowie, nie róbcie jaj - nie o ilość linii chodzi o ilość JOIN czy ilość kolumn zwracanych i nigdy nie wykorzystywanych.

firefox napisał(a):

Kolego spokojnie, ja nie oceniam Twojej bazy i nie mówię, że Ty tworzysz potworki, ale widziałem zapytania SELECT wygenerowane przez framework, które nie raz musiałem optymalizować. 100x szybciej i prościej dało radę napisać to zapytanie w T-SQL.
Dlatego też napisałem, zawsze jest potrzebne wyczucie, to co sprawdzi się w jednym nie musi w drugim. Tak samo umiejętność jednego developera nie wskazuje umiejętności developera drugiego. Być może moje doświadczenie jest negatywne, ale to nie znaczy, że każdy developer piszę tak samo.
To samo odnosi się do procedur składowanych, widziałem takie, że miały zagnieżdżone kursory gdzie pojęcie wydajność autorowi nie była znana.

Oho - to kolejny mit; nie używaj kursorów bo są wolne.
Są, ale nie wszystkie; zdarza mi się użyć kursorów, ze względu na pewne fiku-miku i nie jest to problem wydajnościowy.
Natomiast jest wiele typów kursorów, trzeba wiedzieć co to znaczy. Ja np. nigdy nie użyłem innego kursora jak fast_forward.

firefox napisał(a):

Raz jeszcze powtarzam - rozsądek.
Żeby nie było tak źle jak jest to są testy pokazujące przewagę własnego kodu .NET nad wbudowanymi funkcjami obliczeniowymi. Zatem to ponownie pokazuje, że nie ma jednego idealnego wyjścia, wszystko zależy od potrzeb i modelu danych.

To prawda i też byłem zdziwiony, że działa to wydajnie. Dobrym przykładem jest np. typ HierarchyID, który jest typem CLR - nie natywnym silnika. I działa to całkiem gracko.

firefox napisał(a):

PS. Co do niezrozumienia tematu bezpieczeństwa wczytaj się w treść raz jeszcze i pomyśl. Myślę, że dasz radę dojść do sensu tej wypowiedzi.

Pod warunkiem, że ma ona sens...
Bo jak rozumiem, SP są bezpieczniejsze bo... co?
SQLInjection? Nie wchodzi w grę, ponieważ używamy sparametryzowanych zapytań. Identycznie jak w przypadku SP.
Uprawnienia w bazie danych? Nie używam ich; z baza komunikuje się aplikacja - nie użytkownik wprost. Aplikacja posiada uprawniania, nie baza danych. Aplikacja działa z baza na zasadzie Application Role.

bananananafu napisał(a):

Cześć,
Chciałbym was zapytać czemu zapytania składowane w systemie zarządzania bazą danych są wydajniejsze od zapytań zagnieżdżonych w kodzie aplikacji?
Bardzo mnie interesuje ta kwestia.
Z góry dzięki

Nie "czemu", tylko "kiedy?".
Będą wydajniejsze jeśli możesz efektywnie (najlepiej nie-kursorowo) obrobić zbiór po stronie serwera zamiast go wyciągać na klienta.

Kiedy będą wolniejsze?

1. Np. gdy to samo zapytanie stosujesz do małego podzbioru i do całej tabeli. Wtedy SP może się zoptymalizować na jeden typ wyniku i przy drugim będzie już wolna. A dynamiczny SQL zawsze się dostosuje od nowa.
2. Kiedy masz dużo zależności między rekordami (grafy, drzewa, ostatni aktywny rekord z grupy itp)
3. Kiedy nie masz potrzebnej (i efektywnej) funkcji wbudowanej i musisz się posiłkować jakimiś wydumanymi obliczeniami i/lub grupowaniami

vpiotr napisał(a):

Kiedy będą wolniejsze?

1. Np. gdy to samo zapytanie stosujesz do małego podzbioru i do całej tabeli. Wtedy SP może się zoptymalizować na jeden typ wyniku i przy drugim będzie już wolna. A dynamiczny SQL zawsze się dostosuje od nowa.
2. Kiedy masz dużo zależności między rekordami (grafy, drzewa, ostatni aktywny rekord z grupy itp)
3. Kiedy nie masz potrzebnej (i efektywnej) funkcji wbudowanej i musisz się posiłkować jakimiś wydumanymi obliczeniami i/lub grupowaniami

Ad 1) Co to znaczy "SP może zoptymalizować się na jeden typ wyniku"? Dane zwracane przez sp nie są buforowane, buforowany jest plan zapytania, kluczem do buforu jest literalnie sql stanowiący to zapytanie. Buforowany jest plan pierwszego wykonania zapytania - czy o to Ci chodzi? Przecież dokładnie ten sam problem będzie występować dla zwykłego zapytania (oczywiście via prepared statement).
Ad 2 i 3) To samo będzie dla zwykłego zapytania.

SP w porównaniu do zwykłego zapytania rzadko bywa wolniejsza, w sumie poza technicznymi kruczkami (np. takim jak opisano w http://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure - BTW bardzo ciekawy wątek) to nie mogę wymyślić takiego racjonalnego przypadku.

Raczej nie musi być wielkich różnic w szybkości wykonania pojedynczego select/update/insert/delete w ramach SP i z serwera aplikacyjnego, a nawet dla wielu takich samych modyfikacji w różnymi parametrami (bo np. w JDBC można zastosować "batching").
Zysk dla SP może być taki, że w SP można czasem zdecydować, czy coś zrobić na bazie i jak, bez potrzeby ruchu sieciowego tam i z powrotem między bazą a serwerem aplikacyjnym tylko w celu podjęcia tej decyzji.

A co do jakiegoś kompilowania planów wykonania w SP, to nie wiem, o jakie bazy chodzi, ale raczej o jakieś niedorobione jak na dzisiejsze rozwiązania. Przecież optymalny plan zależy od wartości parametrów zapytania (owszem bywał z tym problem w Oracle'u chyba 9, ale to przecież grube lata temu), bo mogą być różne rozkłady wartości w kolumnach z kryteriami, mogą zostać policzone nowe statystyki, ktoś może dodać indeks itd.

firefox napisał(a):

Uważam temat za zamknięty, bo możemy na ten temat rozmawiać do rana i SQL'owcy powiedzą, że najlepsze są procedury a .NET'owcy, że ich zabawki :)

Nie sądzę, aby jakikolwiek dobry programista tak powiedział.

Jak dobrze poszukamy w necie to są statystyki przy jakiś założeniach. Niestety, ale najlepiej wypadają te, które wywołują procedury składowane (zapewne nie w każdym przypadku - nie jest to żelazna zasada).

Nawet jeśli w jakichś tam sztucznych testach SP wypadają jako zawsze szybsze, to na pewno nie są szybsze w tworzeniu i utrzymywaniu w rzeczywistym świecie. SP ma sens do operacji wsadowych, raportów, tego typu hurtowego przetwarzania danych. A do CRUDa są ORMy. Milisekundy tracone na wykonaniu zapytania są równoważone przez miesiące krótszej pracy nad aplikacją.

wloochacz napisał(a):

Wiem, że tak robią i czasem jest to poważny problem.

Wierzę Ci, chociaż sam nie spotkałem się z tym, żeby update pojedynczego rekordu spowalniał aplikację, albo w ogóle powodował jakieś opóźnienie zauważalne dla użytkownika.

Pół biedy, jak można to optymalizować, ale nic mnie tak nie wkurza jak naginanie logiki aplikacji do ograniczeń technologii

Z punktu widzenia logiki aplikacji nie ma znaczenia, czy updatowane są wyłącznie zmienione pola czy wszystkie. To jedynie szczegół implementacji infrastruktury. Owszem, nie jest to optymalne działanie, może się nie podobać, ale jeśli mamy z tego jakiś zysk, to w czym to może przeszkadzać?

wloochacz napisał(a):

Pod warunkiem, że ma ona sens...
Bo jak rozumiem, SP są bezpieczniejsze bo... co?
SQLInjection? Nie wchodzi w grę, ponieważ używamy sparametryzowanych zapytań. Identycznie jak w przypadku SP.
Uprawnienia w bazie danych? Nie używam ich; z baza komunikuje się aplikacja - nie użytkownik wprost. Aplikacja posiada uprawniania, nie baza danych. Aplikacja działa z baza na zasadzie Application Role.

Oczywiście, że ma :) Inaczej bym nie pisał. Tak tylko bez żadnego wysiłku intelektualnego odpisze:

1. Mając procedurę nadajesz uprawnienia tylko do procedury i jej wykonania. Bez dostępu do widoku/tabeli. Czyli użytkownik nie dostanie się do danych tabeli, może poruszać się w zakresie możliwości procedury.

2. Czy wysyłając z aplikacji zapytanie nie wysyłasz jawnie całego SELECT/UPDATE/INSERT/DELETE? Jak kolega zauważył kilka linijek kodu EF zamienia na kilkanaście albo kilkadziesiąt Podczas gdy procedura wywoływana jest za pomocą tylko EXECa - to też sama w sobie jest zaleta. To powoduje, że trochę jest to niebezpieczne w przypadku nasłuchiwania sieci. bo nikt nie zobaczy i nie pozna w ten sposób struktury bazy. Nie widzisz nazw tabel czy widoków, kolumn czy kalkulacji a nawet RELACJI. Widzisz tylko exec z parametrami.

3. Po trzecie choć w dzisiejszych czasach jest to mniej bolesne bo infrastruktura z reguły jest na wysokim poziomie ale w przypadku kilku set użytkowników i ilości danych jakie wędrują po sieci w odpowiedzi na zapytanie i inne procesy około SQL dokładanie dużego kodu zapytania (który jest w postaci jawnej patrz punkt 1) po sieci jest jego dodatkowym obciążeniem zamiast wysłania tylko exec.

Tak sobie myślę, że nie rozpatrujemy innej strony medalu, użytkowej. Patrzymy przez pryzmat developera. A co z adminami SQL? Teoretycznie DEV nie powinien mieć dostępu do serwera produkcyjnego, w jaki sposób zatem administrator będzie optymalizował środowisko? Chyba zgodnie z ogólnymi zasadami zgodzicie się, że to rola administratora? - oczywiście w praktyce różnie z tym bywa i czasem DEV i ADM są współdzielone - tak, można korzystać z widoków/XE, można chodzić do DEV, ale to wydłuża proces analizy.

Mam jeszcze takie zapytanie bo nie jestem specjalistą od .NET, ale czy EF daje możliwość korzystania np z HINT'ów, robienia rekurencji (bez posiłkowania się widokami) - oczywiście bez sklejania strinq'ów?

Na koniec raz jeszcze powtarzam. Nie ma złotego i idealnego rozwiązania. Wszystko zależy od sposobu wykorzystania, jakości, modelu danych, wymagań biznesowych.
Tak jak napisałem wcześniej są sytuacje w których ORM sprawdzi się po prostu lepiej, są sytuacje, w których procedura poradzi sobie lepiej. Zatem każdy przypadek powinien być rozpatrywany indywidualnie a nie ogólnie tak jak my próbujemy to zrobić w tym miejscu.
To jak jakby dyskutować, czy wyższy człowiek ma lepiej w życiu czy niższy. Każda baza jest inna, każdy ORM i proc można spaprać tak, że wyjdzie z tego chała :)

firefox napisał(a):

1. Czy wysyłając z aplikacji zapytanie nie wysyłasz jawnie całego SELECT/UPDATE/INSERT/DELETE? Jak kolega zauważył kilka linijek kodu EF zamienia na kilkanaście albo kilkadziesiąt Podczas gdy procedura wywoływana jest za pomocą tylko EXECa - to też sama w sobie jest zaleta. To powoduje, że trochę jest to niebezpieczne w przypadku nasłuchiwania sieci. bo nikt nie zobaczy i nie pozna w ten sposób struktury bazy.

Z drugiej strony, kod wygenerowany przez Entity Framework jest tak zobfuscowany, że nikt nie da rady się z niego niczego o bazie dowiedzieć. Gorzej z prawdziwymi ORMami.
I kto niby miałby podsłuchiwać te zapytania? Przecież one nie idą przez internet, lecz przez jakąś wewnętrzną sieć, do której nie ma dostępu nikt poza adminami.

Mam jeszcze takie zapytanie bo nie jestem specjalistą od .NET, ale czy EF daje możliwość korzystania np z HINT'ów, robienia rekurencji (bez posiłkowania się widokami) - oczywiście bez sklejania strinq'ów?

Można manipulować treścią wygenerowanego zapytania wykorzystując interceptory i dodać hinty.