[es] - [Tutorial] Optimizovan paging kroz LINQ to SQL, a bogami i kroz obican SQL

mmix
Miljan Mitrović
Chief Software Architect
Financial Intelligence Software Ltd.
Passau, Deutschland

SuperModerator
Član broj: 17944
Poruke: 6050

+4631 Profil

[Tutorial] Optimizovan paging kroz LINQ to SQL, a bogami i kroz obican SQL

^{17.05.2008. u 11:55 - pre 206 meseci}

Petljajuci nesto po LINQ to SQL extenzijama naleteo sam na nesto veoma interesantno sto bih podelio sa vama, siguran sam da ce vam zatrebati. Obicno, kad imamo neku web page-enabled kontrolu (grid, list, itd), ucitamo celu listu podataka sa servera i "premotamo" do pocetka zeljenje stranice i uzmemo pageSize podataka za prikaz sto ume da bude skupo kad source tabela ima vise desetina jiljada redova i vise.

Postoji i bolje resenje, ovo je primer za LINQ to SQL (PagingDataContext je vezan za AdventureWorks bazu, tabela Contacts):

Code:

        public string[] GetPage(int pageIndex, int pageSize)
        {
            PagingDataContext db = new PagingDataContext();
            var prezimena = from p in db.Contacts
                            orderby p.LastName
                            select p.LastName;
            // izvuci stranicu
            prezimena = prezimena.Skip(pageIndex * pageSize).Take(pageSize);
            // izvrsi SQL i vrati podatke
            return prezimena.ToArray();
        }

Obratite paznju na Skip<>() i Take<>() ekstenzije, prva kaze preskoci toliko elemenata, druga kaze onda uzmi toliko elemenata. Ocekivao sam relano da LINQ to SQL ucita sve podatke i da onda offline premota kolekciju, medjutim kad se pozove sa pageIndex = 12 i pageSize = 10, LINQ to SQL generise sledeci upit.

Code:

exec sp_executesql N'
SELECT [t1].[LastName]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[LastName]) AS [ROW_NUMBER], [t0].[LastName]
    FROM [Person].[Contact] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
',N'@p0 int,@p1 int',@p0=120,@p1=10

Ova skripta bukvalno vraca samo 10 redova pocev od 121-og i veoma je brza, execution tree je potpuno linearan, iz 5 operacija i ako je orderby pokriven indeksom skeniranje indeksa i generisanje segmenta sa brojem reda je veoma, veoma brzo. Na kraju je i sam ekterni IO rasterecen jer se salje samo pageSize redova. Iako se podaci izvlace iz nested tabele, nema nikave transofrmacije pa se polja direktno prenose u finalni recordset, a posto je ROW_NUMBER vec napravljen inkrementalno nad zeljenim sortom, finalni rezultat sortiran po ROW_NUMBER je takodje sortiran po istom redosledu

Cak iako ne koristite LINQ to SQL, moze lako da se napravi adapter ili DataReader koristeci sledecu formu SQL-a:

Code:

SELECT <lista polja iz B koja matchuje donju listu polja iz A>
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY <sort lista iz A>) AS ROW_NUMBER, <lista polja iz A>
    FROM <source tabela> AS A
    ) AS B
WHERE B.ROW_NUMBER BETWEEN <donja granica> AND <gornja granica>
ORDER BY B.ROW_NUMBER

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Odgovor na temu

deerbeer
Beograd

Član broj: 174418
Poruke: 1189
*.adsl-1.sezampro.yu.

+395 Profil

Re: [Tutorial] Optimizovan paging kroz LINQ to SQL, a bogami i kroz obican SQL

^{18.05.2008. u 15:14 - pre 206 meseci}

Citat:

....podataka za prikaz sto ume da bude skupo kad source tabela ima vise desetina jiljada redova i vise.

Postojao je takav problem pre izlaska SQL2005 (ROWSELECT() ROWNUMBER() itd...) i LINQ-a pa je developerima ostalo da se dovijaju na razne nacine ..
Pod SQL2000 sam pravio stored procedure koje bi radile page-ing sa scroll-ing kursorom na sledeci nacin :

Code:

CREATE PROCEDURE GetPageResult @from int ,@pageSize int
  DECLARE @result table ( FirstName varchar(30),LastName varchar(50))

  DECLARE crs CURSOR
  SCROLL /* kursor mora biti definisam sa scroll opcijom inace ostaje FETCH NEXT kao jedina komanda .. ...*/
  FOR
  SELECT FirstName , LastName  FROM authors

  OPEN crs
  DECLARE @counter int
  SET @counter = 1

  DECLARE @FirstName varchar (30)
  DECLARE @LastName varchar (30)

  FETCH ABSOLUTE @from  FROM crs /* govori kursoru koliko redova da preskoci */
  INTO @FirstName,@LastName

  WHILE @@FETCH_STATUS = 0 AND @counter < @pageSize /* dodatno se proverava uslov za parametar pageSize */
  BEGIN

    FETCH PRIOR FROM crs /*uzima sledeci red od tekuceg .... */
    INTO @FirstName,@LastName

    INSERT INTO @result (FirstName,LastName)
    VALUES (@FirstName,@LastName)

    SET @counter = @counter + 1
  END

CLOSE crs
DEALLOCATE crs

SELECT * FROM @result

MS je to sad sve elegantno i lepo upakovao i veoma je zgodno za rad

Viva lollapalooza

Odgovor na temu