Wywołanie Oracle SP z TableAdapter bardzo powolne




Mam kwerendy, która działa super szybko, gdy wykonywane w edytorze SQL (Oracle): 1ms.


To samo zapytanie (jak procedury przechowywanej) po wykonaniu przez zbioru danych o-TableAdapter trwa 2 sekund. Ja tylko zdobycie 20rows.


Ponieważ używam TableAdapter, zwracane wartości są przechowywane w ref kursora.


Gdybym był pobierania 2'000 wiersze mogę zrozumieć, że od pewnego czasu jest potrzebne do zbudowania zbioru danych, ale tylko 2 sekund do 20 wierszy wydaje się zbyt wiele dla mnie.


Nie ma lepszego sposobu, aby wykonać SP wyroczni czy jest to jedyny sposób? Co mogę spróbować zrobić, aby poprawić występy?


Dzięki za pomoc!







Wyszukiwanie w Google, wydaje się, że problem jest z refcursor. Inne osoby w obliczu tego samego problemu z wydajnością, ale żadne rozwiązanie nie jest przewidziane.
Ok. Znalazłem to w czym problem.


Na początku myślałem, że to problem z DataProvided, ale to nie było. Odkryłem ten sam problem w SQLServer 2000 ....


Wyszukiwanie w google dowiedziałem się czegoś o plan wykonania. Biorąc w ten sposób, że zapytania zwiększyło wydajność 50%.


Krótki życiorys tego problemu jest to, że podczas wykonywania SP kodem, DBMS ma pewne problemy z planu wykonania, a nie korzystać z indeksów ...


Lepszą odpowiedź jest w tym poście:
 [Parametr Wąchania (lub Fałszowanie) w SQL Server](https://stackoverflow.com/questions/211355/parameter-sniffing-or-spoofing-in-sql-server)


Mam nadzieję, że to Ci pomoże.
<p>oto wyniki zapytania tabeli w <strong>druga kolumna indeksu wielokolumnowego</strong>.<br> efekty są łatwe do odtworzenia dla każdego. po prostu spróbuj w domu.</p><p>testowałem z postgresql <strong>9.0.5</strong> na debianie, używając średniej wielkości tabeli bazy danych z 23322 wierszami. implementuje relację n: m między tabelami <code>adr</code> (adres) i <code>att</code> (atrybut), ale tutaj nie ma to znaczenia. uproszczony schemat:</p><pre><code>create table adratt ( adratt\_id serial primary key , adr\_id integer not null , att\_id integer not null , log\_up timestamp(0) not null default (now())::timestamp(0) , constraint adratt\_uni unique (adr\_id, att\_id) ); </code></pre><p>ograniczenie <code>unique</code> skutecznie implementuje unikalny indeks. dla pewności powtórzyłem test z prostym indeksem i uzyskałem identyczne wyniki, jak oczekiwano.</p><pre><code>create index adratt\_idx on adratt(adr\_id, att\_id) </code></pre><p>tabela jest skupiona w <code>adratt\_uni</code> indeks i przed testem uruchomiłem:</p><pre><code>cluster adratt; analyze adratt; </code></pre><p>skanowanie sekwencyjne w poszukiwaniu zapytań na <code>(adr\_id, att\_id)</code> są tak szybkie, jak to tylko możliwe. indeks wielokolumnowy będzie nadal używany jako warunek zapytania w samej drugiej kolumnie indeksu.</p><p>uruchomiłem zapytania kilka razy, aby zapełnić pamięć podręczną i wybrałem najlepszy z dziesięciu przebiegów, aby uzyskać porównywalne wyniki.</p><h2>1. zapytanie za pomocą obu kolumn</h2><pre><code>select * from adratt where att\_id = 90 and adr\_id = 10; adratt\_id | adr\_id | att\_id | log\_up +++ 123 | 10 | 90 | 2008-07-29 09:35:54 (1 row) </code></pre><p>wyjście <code>explain analyze</code>:</p><blockquote> <pre><code>index scan using adratt\_uni on adratt (cost=0.00..3.48 rows=1 width=20) (actual time=0.022..0.025 rows=1 loops=1) index cond: ((adr\_id = 10) and (att\_id = 90)) total runtime: 0.067 ms </code></pre></blockquote><h2>2. zapytanie za pomocą pierwszej kolumny</h2><pre><code>select * from adratt where adr\_id = 10 adratt\_id | adr\_id | att\_id | log\_up +++ 126 | 10 | 10 | 2008-07-29 09:35:54 125 | 10 | 13 | 2008-07-29 09:35:54 4711 | 10 | 21 | 2008-07-29 09:35:54 29322 | 10 | 22 | 2011-06-06 15:50:38 29321 | 10 | 30 | 2011-06-06 15:47:17 124 | 10 | 62 | 2008-07-29 09:35:54 21913 | 10 | 78 | 2008-07-29 09:35:54 123 | 10 | 90 | 2008-07-29 09:35:54 28352 | 10 | 106 | 2010-11-22 12:37:50 (9 rows) </code></pre><p>wyjście <code>explain analyze</code>:</p><blockquote> <pre><code>index scan using adratt\_uni on adratt (cost=0.00..8.23 rows=9 width=20) (actual time=0.007..0.023 rows=9 loops=1) index cond: (adr\_id = 10) total runtime: 0.058 ms </code></pre></blockquote><h2>3. zapytanie za pomocą drugiej kolumny</h2><pre><code>select * from adratt where att\_id = 90 adratt\_id | adr\_id | att\_id | log\_up +++ 123 | 10 | 90 | 2008-07-29 09:35:54 180 | 39 | 90 | 2008-08-29 15:46:07 ... (83 rows) </code></pre><p>wyjście <code>explain analyze</code>:</p><blockquote> <pre><code>index scan using adratt\_uni on adratt (cost=0.00..818.51 rows=83 width=20) (actual time=0.014..0.694 rows=83 loops=1) index cond: (att\_id = 90) total runtime: 0.849 ms </code></pre></blockquote><h2>4. wyłącz indeksowanie i skanowanie bitmaps</h2><pre><code>set enable\_indexscan = off; select * from adratt where att\_id = 90 </code></pre><p>wynik analizy explain analyze:</p><blockquote> <pre><code>bitmap heap scan on adratt (cost=779.94..854.74 rows=83 width=20) (actual time=0.558..0.743 rows=83 loops=1) recheck cond: (att\_id = 90) -&gt; bitmap index scan on adratt\_uni (cost=0.00..779.86 rows=83 width=0) (actual time=0.544..0.544 rows=83 loops=1) index cond: (att\_id = 90) total runtime: 0.894 ms </code></pre></blockquote><pre><code>set enable\_bitmapscan = off select * from adratt where att\_id = 90 </code></pre><p>wyjście <code>explain analyze</code>:</p><blockquote> <pre><code>seq scan on adratt (cost=0.00..1323.10 rows=83 width=20) (actual time=0.009..2.429 rows=83 loops=1) filter: (att\_id = 90) total runtime: 2.680 ms </code></pre></blockquote><h2>wniosek</h2><p>zgodnie z oczekiwaniami indeks wielokolumnowy służy do zapytania tylko w drugiej kolumnie.<br> zgodnie z oczekiwaniami jest mniej skuteczny, ale zapytanie jest nadal <strong>x szybsze</strong> niż bez indeksu.<br> po wyłączeniu skanowania indeksów planista zapytań wybiera skanowanie sterty map bitowych, które wykonuje prawie tak samo szybko. dopiero po wyłączeniu tego wraca do skanowania sekwencyjnego.</p> <h1><strong> nr. </strong></h1><p><strong> <code>truncate</code> i <code>drop</code> są prawie identyczne pod względem zachowania i prędkości, więc wykonanie <code>truncate</code> tuż przed <code>drop</code> jest po prostu niepotrzebne. </strong></p><hr><p><em>waga: napisałem tę odpowiedź z perspektywy sql server i założyłem, że będzie ona miała zastosowanie w równym stopniu do sybase. wygląda na to, że <a href="https://dba.stackexchange.com/q/186154/2660">nie jest tak do końca</a> .</em></p><p><em>waga: kiedy po raz pierwszy opublikowałem tę odpowiedź, było kilka innych wysoko ocenianych odpowiedzi - w tym również odpowiedź zaakceptowana - które złożyły kilka fałszywych twierdzeń, takich jak: <code>truncate</code> nie jest rejestrowany; <code>truncate</code> nie można przywrócić; <code>truncate</code> jest szybszy niż <code>drop</code>; itp.</em></p><p><em>teraz, gdy ten wątek został oczyszczony, następujące obalenia mogą wydawać się styczne do pierwotnego pytania. zostawiam je tutaj jako odniesienie dla innych, którzy chcą obalić te mity.</em></p><hr><p>istnieje kilka popularnych kłamstw - rozpowszechnionych nawet wśród doświadczonych dba - które mogły być tego powodem <code>truncate-then-drop</code> wzór. oni są:</p><ul> <li><strong> mit </strong>: <code>truncate</code> nie jest rejestrowany, dlatego nie można go przywrócić.</li> <li><strong> mit </strong>: <code>truncate</code> jest szybszy niż <code>drop</code>.</li> </ul><p>pozwól mi obalić te kłamstwa. piszę to obalenie z perspektywy sql server, ale wszystko, co tu powiem, powinno mieć również zastosowanie do sybase.</p><h1> <a href="http://msdn.microsoft.com/en-us/library/ms177570.aspx"> obciąć </a> <em>is</em> zalogowany, a <em>can</em> można przywrócić.</h1><ul> <li> <p><strong> <code>truncate</code> jest zarejestrowaną operacją, więc <a href="http://sqlblog.com/blogs/kalen\_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx">to <em>można</em> wycofać</a> . </strong> po prostu zawiń to w transakcji.</p> <pre><code>use [tempdb]; set nocount on; create table truncate\_demo ( whatever varchar(10) ); insert into truncate\_demo (whatever) values ('log this'); begin transaction; truncate table truncate\_demo; rollback transaction; select * from truncate\_demo; drop table truncate\_demo; </code></pre> <p>zauważ jednak, że <strong> jest to <a href="http://psoug.org/reference/truncate.html">nieprawda</a> dla oracle </strong>. chociaż zalogowany i chroniony przez funkcję cofania i ponawiania oracle, <code>truncate</code> i inne instrukcje ddl <em>nie można</em> zostać wycofane przez użytkownika, ponieważ oracle wydaje <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11\_question\_id:2860243091362">niejawne zobowiązania</a> natychmiast przed i po wszystkich instrukcjach ddl.</p> </li> <li> <p><strong> <code>truncate</code> jest minimalnie zalogowany </strong>, a nie w pełni zalogowany. co to znaczy? powiedzmy, że <code>truncate</code> stół. zamiast umieszczać każdy usunięty wiersz w dzienniku transakcji, <code>truncate</code> po prostu zaznacza strony danych, na których żyją, jako nieprzydzielone. właśnie dlatego jest tak szybki. dlatego też nie można odzyskać wierszy tabeli <code>truncate</code> z dziennika transakcji przy użyciu czytnika dziennika. znajdziesz tam tylko odniesienia do stron zwolnionych danych.</p> <p>porównaj to z <code>delete</code>. jeśli <code>delete</code> wszystkie wiersze w tabeli i zatwierdzisz transakcję, nadal teoretycznie możesz znaleźć usunięte wiersze w dzienniku transakcji i odzyskać je stamtąd. jest tak, ponieważ <code>delete</code> zapisuje każdy usunięty wiersz w dzienniku transakcji. w przypadku dużych tabel spowoduje to, że będzie on znacznie wolniejszy niż <code>truncate</code>.</p> </li> </ul><h1> <a href="http://msdn.microsoft.com/en-us/library/ms173790.aspx"> upuść </a> jest równie szybki jak truncate.</h1><ul> <li>podobnie jak <code>truncate</code>, <strong> <code>drop</code> jest operacją minimalnie rejestrowaną. </strong> oznacza to, że <code>drop</code> można wycofać też. oznacza to również <a href="http://msdn.microsoft.com/en-us/library/dd425070.aspx">działa dokładnie tak samo</a> jak <code>truncate</code>. zamiast usuwać poszczególne wiersze, <strong> <code>drop</code> oznacza odpowiednie strony danych jako nieprzydzielone i dodatkowo oznacza metadane tabeli jako usunięte </strong>.</li> <li> <p>ponieważ <code>truncate</code> i <code>drop</code> działają dokładnie w ten sam sposób, działają tak samo szybko. <strong> nie ma sensu <code>truncate</code> - przechodzenie do tabeli przed <code>drop</code> - pisanie jej. </strong> uruchom <a href="https://gist.github.com/1349319">this skrypt demo</a> w twojej instancji programistycznej, jeśli mi nie wierzysz.</p> <p>na mojej lokalnej maszynie z ciepłą pamięcią podręczną wyniki są następujące:</p> <pre><code>table row count: 134,217,728 run# transaction duration (ms) truncate truncate then drop drop  01 0 1 4 02 0 39 1 03 0 1 1 04 0 2 1 05 0 1 1 06 0 25 1 07 0 1 1 08 0 1 1 09 0 1 1 10 0 12 1  avg 0 8.4 1.3 </code></pre> <p>tak więc, dla tabeli 134 <em>milionów</em> wierszy, zarówno <code>drop</code>, jak i <code>truncate</code> nie zajmują w ogóle czasu. (w przypadku zimnego bufora zajmują około 2-3 sekund dla pierwszego uruchomienia lub dwóch). uważam również, że wyższy średni czas trwania operacji <code>truncate</code>, a następnie <code>drop</code>, można przypisać zmianom obciążenia na moja lokalna maszyna i <em>nie</em>, ponieważ kombinacja jest w magiczny sposób o rząd wielkości gorsza niż poszczególne operacje. w końcu są prawie dokładnie tym samym.</p> <p>jeśli interesują cię bardziej szczegółowe informacje na temat narzutu związanego z logowaniem tych operacji, <a href="https://dba.stackexchange.com/questions/4163/why-use-both-truncate-and-drop/7685#7685">martin ma proste wyjaśnienie</a> tego.</p> </li> </ul> Jest to dość szerokie, ale dam ci tak ogólną odpowiedź, jak tylko mogę.

**CTE ...**

* Są niezindeksowalne (ale mogą używać istniejących indeksów na obiektach, do których istnieją odwołania)
* Nie można mieć ograniczeń
* Są zasadniczo jednorazowe `VIEW` s
* Trwać tylko do momentu uruchomienia następnego zapytania
* Może być rekurencyjny
* Nie masz dedykowanych statystyk (polegaj na statystykach dotyczących podstawowych obiektów)

**#Temp Tables ...**

* Są prawdziwe zmaterializowane tabele, które istnieją w tempdb
* Może być indeksowany
* Może mieć ograniczenia
* Trwaj w życiu obecnego POŁĄCZENIA
* Może odwoływać się do innych zapytań lub podprocedur
* Masz dedykowane statystyki generowane przez silnik

Jeśli chodzi o użycie każdego z nich, mają bardzo różne przypadki użycia.Jeśli masz bardzo duży zestaw wyników lub musisz odwołać się do niego więcej niż raz, umieść go w tabeli `#temp` .Jeśli ma być rekurencyjny, jednorazowy lub po prostu dla uproszczenia logicznego, preferowany jest `CTE` .

Ponadto należy `CTE`**nigdy nie używaj do wykonania**.Prawie nigdy nie przyspieszysz przy użyciu CTE, ponieważ znowu jest to widok jednorazowy.Możesz zrobić z nimi kilka fajnych rzeczy, ale przyspieszenie zapytania nie jest jednym z nich. <p>postgres ma bardzo elastyczną obsługę <a href="http://www.postgresql.org/docs/current/static/arrays.html">tablice</a> i <a href="http://www.postgresql.org/docs/current/static/rowtypes.html">typy złożone</a> . może to być coś, co próbujesz zrobić:</p><blockquote> <pre><code>create type my\_type as (val1 integer, val2 integer); </code></pre></blockquote><blockquote> <pre><code>create function my\_function(arr my\_type[]) returns text language plpgsql as $$ begin return arr::text; end;$$; </code></pre></blockquote><blockquote> <pre><code>select my\_function(array[row(1,2),row(3,4)]::my\_type[]); </code></pre> <pre> | moja\_funkcja | | :  <a href="http://dbfiddle.uk/?rdbms=postgres\_9.4&amp;fiddle=207971f904ff986920e88a9cd76bcab6">| {„(1,2)”, „(3,4)”} | </pre></blockquote><p><em> dbfiddle  (tutaj</a> </em></p> Obie alternatywy, które zostały wskazane ma wady i zalety, decyzja zależy od tego jak to plusy i minusy wpływa na aplikację:



> 
> Pobrać wszystkie wartości z bazy danych przy użyciu zwykłego PHP następnie przechowywać je w zmiennej JavaScript, później szukać sekund rozwijanej listy (jeśli wartość pierwszej rozwijanej liście na zmiany wartości) w zmiennej JavaScript (array).
> 
> 
> 


Plusy:


* Minimalizuje petycje http, trzeba tylko jedną prośbę, aby odzyskać całość danych.
* Minimalizuje kwerenda jest potrzebne w bazie danych, na ogół tylko wykonać jedno zapytanie.


Cons:


* Zwiększa ilość pamięci używanej jeśli masz duże ilości danych.
* Jeśli dane zmienia się nieustannie, użytkownicy nie widzą zmian aż odświeżyć całą stronę.



> 
> Jeśli pierwszy rozwijanej liście zmian wartości jest następnie pobrać wartości dla drugiej listy rozwijanej z bazą danych za pomocą technologii AJAX (jQuery).
> 
> 
> 


Plusy:


* Nie trzeba do przechowywania wszystkich danych w pamięci całych jedynie dane, które zostaną wykorzystane, co zmniejsza ilość potrzebnej pamięci.
* Twoje dane są aktualizowane za każdym razem, bez konieczności przeładowania strony tylko zmieniając wybór.


Cons:


* Trzeba wykonać kilka petycji HTTP i biegnie querys z bazą danych przy każdej zmianie wyboru.


Osobiście preferuję metodę 2, jeśli ilość danych jest stosunkowo duża i wydajność nie jest koniecznością. druga pewnie jest dla sprawdzenia poprwanosci odpowiedzi, kucze nie pamietam dokladnie tych komend ale sprobuj skopjowac je z tej gotowej tabelki i ewentualnie cos pozmieniaj- a dokladniej t wspolrzedne pol zeby sie zgadzaly (wstaw tylko w pierwszych ramkach a reszte zlap za jeden rog i przeciagnij to ci sie skopiuje dla innych wynikow) Często jest to realizowane za pomocą [„Data Driven Subskrypcje”](https://msdn.microsoft.com/en-us/library/ms169673.aspx) , ale ta funkcja jest dostępna tylko w niektórych wersjach SQL: Enterprise i Business Intelligence, ale nie w standardzie.


Tak więc istnieje wiele twórczych obejścia. Ale nie lubię żadnego z nich, a wszystko to wymaga uczciwej trochę pracy poza SSRS.


Do obejścia widziałem to: Tworzenie subskrypcji, a następnie wyłączenie lub włączenie zadanie SQL, który wysyła subskrypcję (zidentyfikowanego przez GUID).


Tworzenie .exe .NET otoki do obsługi wysyłania wiadomości e-mail. Można go uruchomić kwerendę najpierw sprawdzić, ile zwracane są wiersze, a następnie w razie potrzeby wygenerować raport i umieścić go w wiadomości e-mail. <p>logiczne przetwarzanie zapytania odbywa się na <a href="http://msdn.microsoft.com/en-us/library/ms189499.aspx"> msdn </a> (napisane przez zespół microsoft sql server, a nie firmy zewnętrzne)</p><pre><code>1. from 2. on 3. join 4. where 5. group by 6. with cube or with rollup 7. having 8. select 9. distinct 10. order by 11. top </code></pre><p>tabela pochodna podąża za tym, a następnie zapytanie zewnętrzne robi to ponownie itp. itd</p><p>jest to <strong> logiczne </strong> chociaż: nie <strong> rzeczywiste </strong>. bez względu na to, jak sql server to robi, semantyka jest honorowana <em> do litery</em>. „rzeczywisty” jest określany przez optymalizator zapytań (qo) i unikasz wspomnianego pośredniego produktu cartesion.</p><p>warto wspomnieć, że sql jest deklaratywny: mówisz „co”, a nie „jak”, jak w przypadku programowania proceduralnego/imperatywnego (java, .net). zatem powiedzenie „dzieje się to wcześniej” jest w wielu przypadkach błędne (np. założenie, że występują zwarcia lub kolejność l-do-r, gdzie)</p><p>w powyższym przypadku qo wygeneruje ten sam plan, bez względu na jego strukturę, ponieważ jest to proste zapytanie.</p><p>jednak qo opiera się na kosztach i w przypadku złożonego zapytania wygenerowanie idealnego planu może zająć 2 tygodnie. więc robi to „wystarczająco dobrze”, co tak naprawdę nie jest.</p><p>twój pierwszy przypadek może pomóc optymalizatorowi w znalezieniu lepszego planu, ponieważ logiczna kolejność przetwarzania jest inna dla 2 zapytań. ale może nie.</p><p>użyłem tej sztuczki w sql server 2000, aby uzyskać 60-krotną poprawę wydajności w zapytaniach dotyczących raportowania. ponieważ qo ulepsza wersję do wersji, staje się coraz lepszy w rozwiązywaniu tych problemów.</p><p>i wspomniana książka: istnieje spór<br> patrz so i kolejne linki: <a href="https://stackoverflow.com/q/3270338/27535">https://stackoverflow.com/q/3270338/27535</a> </p> <p>możesz go znaleźć w domyślnym katalogu, w zależności od instalacji. na przykład w moim red hat jest to/var/log/mysql /</p><p>czas zapytania to czas w sekundach, po upływie którego powinien rozpocząć się zapis. można to zrobić podczas uruchamiania i podczas uruchamiania.</p><p>log-slow-queries = /var/log/mysql/mysql-slow.log</p><p>long\_query\_time = 1</p> ogólnie doszedłem mniej więcej, dlaczego takie problemy.

[zdjęcie] marcin20000:

przy takim kalkulatorze to nie tak długo, a to drugie to norma…

odpal livrcd z linuksem… zobaczymy efekty.

kalkulator nie kalkulator, ale jest to komputer który jest używany głównie do przeglądania internetu i d…