Pytanie w sprawie sql-server-2005, sql-server, sql – Skuteczny sposób uzyskiwania @@ rowcount z zapytania za pomocą parametru numer_wiersza

30

Mam drogie zapytanie za pomocą funkcji row_number over () w SQL Server 2005. Zwracam tylko podlistę tych rekordów, gdy zapytanie jest podzielone na strony. Chciałbym jednak również zwrócić całkowitą liczbę rekordów, a nie tylko podzestaw z podziałem na strony. Dwukrotne uruchomienie zapytania w celu uzyskania liczby nie wchodzi w rachubę.

Wybór liczby (*) również nie wchodzi w rachubę, ponieważ wydajność jest absolutnie straszna, gdy próbowałem tego dokonać.

To, co naprawdę kocham, to @@ ROW_NUMBERROWCOUNT :-)

Twoja odpowiedź

4   odpowiedź
4

Jeśli liczba (*) jest powolna, musisz najpierw rozwiązać ten problem, uważnie analizując indeksy i upewniając się, że statystyki są aktualne.

Z mojego doświadczenia wynika, że ​​nie ma nic lepszego niż wykonanie dwóch oddzielnych zapytań, jednego w celu uzyskania strony danych, a drugiego w celu uzyskania całkowitej liczby. Używanie tabeli tymczasowej w celu uzyskania całkowitej liczby jest strategią przegrywającą, ponieważ zwiększa się liczba wierszy. Np. Koszt włożenia 10 000 000 milionów wierszy do tabeli tymczasowej po prostu dla ich policzenia będzie oczywiście nadmierny.

Chociaż zgadzam się z tobą całkowicie, to zapytanie jest skomplikowane i muszę w tym przypadku wybrać ścieżkę optymalizacji. Phil Bennett
36

Przez lata stos potworów deweloperskich zmienił się w wydajne zestawy wyników stronicowania. Jednak nie ma jednej odpowiedzi - zależy to od przypadku użycia. Częścią przypadku użycia jest wydajne uzyskiwanie strony, część zastanawia się, ile wierszy zawiera kompletny zestaw wyników. Tak bardzo przepraszam, jeśli trochę zabłądzę do stronicowania, ale te dwie rzeczy są w moim umyśle ściśle powiązane.

Istnieje wiele strategii, z których większość jest zła, jeśli masz jakąkolwiek ilość danych i nie pasuje do przypadku użycia. Chociaż nie jest to pełna lista, to niektóre z opcji są dostępne .....

Uruchom osobnoCount(*)uruchom oddzielne zapytanie, które wykonuje proste „wybierz liczbę (*) z MyTable”prosty i łatwy do małego stolikadobry na niefiltrowanym dużym stole, który jest wąski lub ma kompaktowy indeks nieklastrowany, którego można użyćzałamuje się, gdy masz skomplikowaneWHERE/JOIN kryteria, ponieważ uruchamianieWHERE/JOIN dwa razy jest drogi.rozkłada się na szeroki indeks, ponieważ liczba odczytów rośnie.PołączyćROW_Number() OVER() iCOUNT(1) OVER(PARTITION By 1)Zostało to zasugerowane przez @RBarryYoung. Ma tę zaletę, że jest prosty w implementacji i bardzo elastyczny.Wadą jest to, że istnieje wiele powodów, dla których może to stać się bardzo kosztowne szybko.Na przykład w DB pracuję obecnie w tabeli Media zawierającej około 6000 wierszy. Nie jest szczególnie szeroki, ma całkowitą klastrową PK i kompaktowy unikalny indeks. Ale to prosteCOUNT(*) OVER(PARTITION BY 1) as TotalRows powoduje ~ 12 000 odczytów. Porównaj to z prostymSELECT COUNT(*) FROM Media - 12 czytań. Wowzers.

AKTUALIZACJA - problem z czytaniami, o którym wspomniałem, to trochę śledź. Okazuje się, że w przypadku funkcji okienkowych jednostka używana do pomiaru odczytów jest w pewnym sensie mieszana. Wynik netto to ogromna liczba odczytów. Więcej informacji na ten temat można znaleźć tutaj:Dlaczego logiczne odczyty dla funkcji agregowanych w oknie są tak wysokie?

Tabele Temp / Zmienne TabeliIstnieje wiele strategii, które pobierają zestaw wyników i wstawiają odpowiednie klucze lub segmenty wyników do zmiennych tabel / zmiennych tabeli.W przypadku małych i średnich zestawów wyników może to zapewnić świetne wyniki.Ten typ strategii działa na prawie każdej platformie / wersji SQL.Wielokrotne działanie na zestawie wyników (często wymaganie) jest również łatwe.Wadą jest praca z dużymi zestawami wyników ... wstawienie kilku milionów wierszy do tabeli tymczasowej ma koszt.Łącząc problem, w systemie o dużej objętości presja na TempDB może być dość istotna, a tabele tymczasowe działają w TempDB.Numer sumy Gaussa / podwójnego rzęduTen pomysł polega napodzbiór czegoś, co matematyk Gauss odkrył (jak zsumować serię liczb). Podzbiór ten polega na tym, jak uzyskać liczbę wierszy z dowolnego punktu w tabeli.Z serii liczb (Row_Number()) liczba wierszy od 1 do N wynosi(N + 1) - 1. Więcej wyjaśnień w linkach.Wygląda na to, że formuła wyrównała się do N, ale jeśli pozostaniesz z formułą, wydarzą się ciekawe rzeczy, możesz obliczyć liczbę wierszy ze strony na środku tabeli.Wynik netto to tyROW_Number() OVER(Order by ID) iROW_Number() OVER(Order by ID DESC) następnie zsumuj dwie liczby i odejmij 1.Używając mojej tabeli Media jako przykładu, moje odczyty spadły z 12 000 do około 75.Na większej stronie skończyło się powtarzanie danych wiele razy, ale przesunięcie w odczytach może być tego warte.Nie testowałem tego na zbyt wielu scenariuszach, więc może się rozpaść w innych scenariuszach.Góra (@n) / SET ROWCOUNTNie są to specyficzne strategie, ale optymalizacje oparte na tym, co wiemy o optymalizatorze zapytań.Kreatywnie używając Top (@n) [top może być zmienną w SQL 2008] lub SET ROWCOUNT może zmniejszyć zestaw roboczy ... nawet jeśli ciągniesz środkową stronę zestawu wyników, wciąż możesz zawęzić wynikTe pomysły działają z powodu zachowania optymalizatora zapytań ... dodatek Service Pack / poprawka może zmienić zachowanie (chociaż prawdopodobnie nie).W instancjach certian SET ROWCOUNT może być nieco dokładnyTa strategia nie odpowiada za uzyskanie pełnej liczby wierszy, a jedynie zwiększa wydajność stronicowaniaWięc co ma zrobić programista?

Przeczytaj mój dobry człowiek, przeczytaj. Oto kilka artykułów, na których oparłem się ...

Bardziej wydajna metoda przywoływania dużych zestawów wynikówOptymalizacja stronicowania po stronie serwera - część IOptymalizacja stronicowania po stronie serwera - część IIWyjaśnienie sumy GaussaZwrot wyników za pomocą Microsoft SQL Server 2005ROW_NUMBER () OVER Not Fast Wystarczająco duży zestaw wynikówPobieranie pierwszych N rekordów z zapytania SQLStronicowanie po stronie serwera przy użyciu SQL Server 2005Dlaczego logiczne odczyty dla funkcji agregowanych w oknie są tak wysokie?

Mam nadzieję, że to pomoże.

Tak, są zdecydowanie w pamięci. Wypełnia szpulę i ponownie wykorzystuje tę samą szpulę z przewijaniem do tyłu i rebindami w innych obszarach planu (zwykła szpula podwyrażeń). Po prostu nie mogłem wyjaśnić tak dużej liczby odczytów, biorąc pod uwagę plan i dane. Martin Smith
Tak poza tym.Właśnie dostałem odpowiedź na moje pierwotne pytanie i okazuje się, że logiczne odczyty dla szpul są liczone inaczej niż dla normalnych stołów. Tak więc liczby nie są bezpośrednio porównywalne. Martin Smith
@ Martin - właśnie zobaczyłem odpowiedź. Dzięki, że dałeś mi znać. EBarr
@Martin - ja też tego nie uporządkowałem, ale zauważyłem kilka rzeczy. Po pierwsze, podejrzewam, że widzisz w odczytach pamięci (tzn. Twój stół jest buforowany). W przypadku większego zestawu danych wystąpiłaby implozja. Po drugie, wydaje się, że rozgałęzia plan wykonania dla każdej funkcji okienkowej (tak jakbyś wykonał połączenie). Po trzecie, często kończy się na wielu wyszukiwaniach kluczy - patrz „Rozwiązanie początkowe” i „metody alternatywne” w artykule „Optymalizacja stronicowania po stronie serwera - część I”. EBarr
0

Robię to, wstawiając cały zestaw wyników z numerem_wiersza do tabeli tymczasowej, a następnie używam @@ rowcount z tego i używam zapytania w tym celu, aby zwrócić stronę potrzebnych danych.

36

Sprawdź agregat COUNT (*), gdy jest używany z OVER (PARTITON BY ..), tak:

<code>    SELECT
     ROW_NUMBER() OVER(ORDER BY object_id, column_id) as RowNum
    , COUNT(*) OVER(PARTITION BY 1) as TotalRows
    , * 
    FROM master.sys.columns
</code>

To jest IMHO najlepszy sposób, aby to zrobić bez konieczności wykonywania dwóch zapytań.

Rzeczywiście bardzo dobre rozwiązanie!, Chociaż dodajesz kolumnę do zapytania, którego potrzebujesz tylko w pierwszym wierszu. Ale myślę, że jest szybszy i zużywa mniej zasobów niż wykonanie dwóch oddzielnych zapytań ... Kat Lim Ruiz
Dziękuję, tylko to, czego szukałem !!! Phil Bennett

Powiązane pytania