Cum se întoarce o pagină de rezultate din SQL?

Multe aplicații au rețele care afișează date dintr-un tabel de baze de date pe o singură pagină. Multe dintre ele permit utilizatorului să aleagă numărul de înregistrări pe pagină, să sorteze pe orice coloană și să navigheze înainte și înapoi prin rezultate.

Ce este un algoritm bun pentru a implementa acest model fără a aduce întreaga tabelă clientului și apoi filtrarea datelor despre client. Cum aduceți doar înregistrările pe care doriți să le afișați utilizatorului?

LINQ simplifică soluția?

0
fr hi bn
adăugat autor Lukas Eder, sursa

8 răspunsuri

Pe MS SQL Server 2005 și mai recent, ROW_NUMBER() pare să funcționeze :

T-SQL: Paging cu ROW_NUMBER()

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)

SELECT * 
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY OrderDate
         ,OrderID;
0
adăugat
emulația de paginare ROW_NUMBER() sau clauza OFFSET .. FETCH a SQL Server 2012 poate fi destul de lentă pentru numerele mari ale paginilor: 4guysfromrolla.com/webtech/042606-1.shtml . În acest caz, metoda căuta poate fi o alegere mai bună, deoarece permite paginarea în timp constant.
adăugat autor Lukas Eder, sursa

Oracle Solution:

select * from (
    select a.*, rownum rnum from (
        YOUR_QUERY_GOES_HERE -- including the order by
    ) a
    where rownum <= MAX_ROW
 ) where rnum >= MIN_ROW
0
adăugat

De fapt, LINQ are Skip și Take metode care pot fi combinate pentru a alege ce înregistrări sunt preluate.

Verifică-i pe cei afară.

For DB: Pagination In SQL Server 2005

0
adăugat

There is a discussion about this Here

Tehnica primește numărul paginii 100.000 dintr-o bază de date de 150.000 linii în 78ms

Utilizând cunoștințele de optimizare și SET ROWCOUNT, primul număr de angajat din pagina solicitată este stocat într-o variabilă locală pentru un punct de pornire. Apoi, SET ROWCOUNT la numărul maxim de înregistrări solicitate în @maximumRows. Acest lucru permite paginarea setului de rezultate într-o manieră mult mai eficientă. Folosind această metodă, de asemenea, profită de indicii pre-existenți pe masă, deoarece merge direct la tabela de bază și nu la un tabel creat local.

Mă tem că nu pot judeca dacă este mai bine decât răspunsul curent acceptat.

0
adăugat
O altă abordare foarte rapidă este căuta metoda , care permite paginarea în timp constant (poate chiar mai rapid decât 78ms)
adăugat autor Lukas Eder, sursa

Există în esență două modalități de a face paginarea în baza de date (presupun că utilizați SQL Server):

Utilizarea funcției OFFSET

Alții au explicat modul în care funcția de clasificare ROW_NUMBER() OVER() poate fi utilizată pentru a efectua pagini. Merită menționat faptul că SQL Server 2012 a inclus în final suport pentru standardul SQL OFFSET Clauza FETCH :

SELECT first_name, last_name, score
FROM players
ORDER BY score DESC
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY

Dacă utilizați SQL Server 2012 și compatibilitatea înapoi nu este o problemă, probabil că preferați această clauză, deoarece va fi executată mai optim de SQL Server în cazuri de colțuri.

Utilizarea metodei SEEK

Există un mod complet diferit, mult mai rapid, dar mai puțin cunoscut, de a efectua paginarea în SQL. Aceasta este deseori numită "metoda de căutare", așa cum este descrisă în acest blog post aici .

SELECT TOP 10 first_name, last_name, score
FROM players
WHERE (score < @previousScore)
   OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC

The @previousScore and @previousPlayerId values are the respective values of the last record from the previous page. This allows you to fetch the "next" page. If the ORDER BY direction is ASC, simply use > instead.

Cu ajutorul metodei de mai sus, nu puteți sări imediat la pagina 4 fără să fi preluat mai întâi cele 40 de înregistrări anterioare. Dar de multe ori nu vrei să sari atât de departe. În schimb, obțineți o interogare mult mai rapidă, care ar putea fi capabilă să preia datele în timp constant, în funcție de indexare. În plus, paginile dvs. rămân "stabile", indiferent dacă datele de bază se modifică (de exemplu, la pagina 1, în timp ce vă aflați la pagina 4).

Aceasta este cea mai bună modalitate de a implementa paginarea atunci când leneșul încărc mai multe date în aplicațiile web, de exemplu.

Rețineți că "metoda de căutare" este numită și paginarea cu chei .

0
adăugat

Aș recomanda fie să folosiți LINQ, fie să încercați să copiați ceea ce face. Am o aplicație unde folosesc metodele LINQ Take and Skip pentru a prelua datele paginate. Codul arată astfel:

MyDataContext db = new MyDataContext();
var results = db.Products
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize);

Rularea SQL Server Profiler relevă faptul că LINQ convertește această interogare în SQL similar cu:

SELECT [ProductId], [Name], [Cost], and so on...
FROM (
    SELECT [ProductId], [Name], [Cost], [ROW_NUMBER]
    FROM (
       SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS [ROW_NUMBER], 
           [ProductId], [Name], [Cost]
       FROM [Products]
    )
    WHERE [ROW_NUMBER] BETWEEN 10 AND 20
)
ORDER BY [ROW_NUMBER]

În limba engleză simplă:
1. Filtrați rândurile și utilizați funcția ROW_NUMBER pentru a adăuga numere de rând în ordinea dorită 2. Filtrați (1) pentru a returna numai numerele de rând dorite pe pagina dvs. 3. Sortați (2) numărul de rând, care este același cu ordinea dorită (în acest caz, cu numele).

0
adăugat
Se întâmplă să știți de ce LINQ ascunde dublu instrucțiunea SELECT actuală? Este (a fost?) Că un truc de performanță subtil pentru unele versiune SQL Server? Consider că nivelul 2 ar putea fi fuzionat cu nivelul 1.
adăugat autor Lukas Eder, sursa

LINQ combinat cu expresii lambda și clase anonime în .Net 3.5 simplifică acest tip de lucru.

Interogarea bazei de date:

var customers = from c in db.customers
                join p in db.purchases on c.CustomerID equals p.CustomerID
                where p.purchases > 5
                select c;

Numărul de înregistrări pe pagină:

customers = customers.Skip(pageNum * pageSize).Take(pageSize);

Sortare după orice coloană:

customers = customers.OrderBy(c => c.LastName);

Obținerea câmpurilor selectate numai de pe server:

var customers = from c in db.customers
                join p in db.purchases on c.CustomerID equals p.CustomerID
                where p.purchases > 5
                select new
                {
                    CustomerID = c.CustomerID,
                    FirstName = c.FirstName,
                    LastName = c.LastName
                };

Aceasta creează o clasă anonimă tipărită static, în care puteți accesa proprietățile sale:

var firstCustomer = customer.First();
int id = firstCustomer.CustomerID;

Rezultatele interogărilor sunt încărcate leneș în mod implicit, deci nu vorbiți cu baza de date până când nu aveți nevoie de date. LINQ in .Net, de asemenea, simplifică foarte mult actualizările prin păstrarea unui text datacontext al oricăror modificări pe care le-ați făcut și actualizând doar câmpurile pe care le modificați.

0
adăugat
Prima afirmație pare să tragă totul din DB și apoi a doua instrucțiune să devină un subset. Dacă vrei doar un subset în primul rând? Am 90.000 de rânduri și vreau doar pagina 4 din 10 rânduri.
adăugat autor Snowy, sursa
expresii @ScSub LINQ sunt leneș-încărcate, astfel încât primul apel nu face de fapt nimic la început. Ai putea apela = customers.Skip clienți (30) .Take (10) și ar trage înapoi doar ceea ce vrei.
adăugat autor Adam Lassek, sursa

Există câteva soluții pe care le folosesc cu MS SQL 2005.

Una dintre ele este ROW_NUMBER (). Dar, personal, nu-mi place ROW_NUMBER() pentru că nu funcționează pentru rezultate mari (DB pe care lucrez este foarte mare - peste date de 1TB care rulează mii de interogări în al doilea rând - știi - mari rețele sociale site-ul).

Iată soluția mea preferată.

Voi folosi un fel de cod pseudo al T-SQL.

Să găsim oa doua pagină de utilizatori ordonată după nume, prenume, unde fiecare pagină are 10 înregistrări.

@page = 2 -- input parameter
@size = 10 -- can be optional input parameter

if @page < 1 then begin
    @page = 1 -- check page number
end
@start = (@page-1) * @size + 1 -- @page starts at record no @start

-- find the beginning of page @page
SELECT TOP (@start)
    @forename = forename,
    @surname = surname
    @id = id
FROM
    users
ORDER BY
    forename,
    surname,
    id -- to keep correct order in case of have two John Smith.

-- select @size records starting from @start
SELECT TOP (@size)
    id,
    forename,
    surname
FROM
    users
WHERE
    (forename = @forename and surname = @surname and id >= @id) -- the same name and surname, but bigger id
    OR (forename = @forename and surname > @surname) -- the same name, but bigger surname, id doesn't matter
    OR (forename > @forename) -- bigger forename, the rest doesn't matter
ORDER BY
    forename,
    surname,
    id
0
adăugat