november 2012 havi bejegyzések

Amikor a TOP (sok) gyorsabb, mint a TOP (kevés)

A TOP parancs nagyon jó kis cucc, nagyban lehet vele növelni a lekérdezések sebességét. Ez azért van, mert az optimalizálót segítjük, hogy x sor visszaadására készüljön. Viszont van amikor ez a parancs “furcsán viselkedik” és a TOP 1 sokkal lassabban fut le, mint a TOP 1.000.000.000. Ilyen esetben az optimalizáló “rosszul” dönt. Demózzunk le egy ilyen esetet.

Csináljunk magunknak teszt adatokat. Segítségképpen a Teszt adatok létrehozása linken találunk egy scriptet, amivel ezt megtehetjük.

A demó scriptben az accountok-hoz tartozó tranzakciók összegét összeadjuk, majd megsorszámozzuk a sorokat az összegek nagyságrendi sorrendjében, de egyedileg. Ezután kikeressük, hogy van e olyan két egymás utáni sor, ahol az ügyfelek tranzakciói között 20.000-nél  nagyobb összeg az eltérés.

-- Előbb nézzük a gyorsabbat, nekem ez 1 mp alatt adott vissza eredményt
USE [tempdb]
GO

WITH cte AS (
  SELECT
    accounts_id,
    SUM(amount) sum_amount,
    ROW_NUMBER() OVER(ORDER BY SUM(amount)) as rwn
  FROM transactions
  GROUP BY accounts_id
)

SELECT TOP 1000000000 *
FROM cte c1
INNER JOIN cte c2
    ON c1.rwn = c2.rwn + 1
WHERE c2.sum_amount - c1.sum_amount > 20000

Most csináljuk annyit, hogy a TOP sokat módosítsuk TOP 1-re és futtassuk a scriptet. Nekem elég vacak gépem van, így 52 percet futott.

Ez execution plan-t megnézve a két lekérdezés között 1 nagyon fontos különbséget látunk. Egyik helyen merge join-al kapcsolja a táblákat, másik helyen nested loops join-al. Ha a  TOP 1-es lekérdezésünk mögé odatesszük, hogy OPTION (MERGE JOIN) , akkor máris viharos sebességgel kapjuk az eredményt.
De miért nem tudta az optimalizáló, hogy másképpen kellene végrehajtani a JOIN műveletet? Azért, mert, ha két VIEW-t (bár ez most CTE, de ugyanúgy viselkedik, mint a VIEW) join-olunk, akkor az optimalizáló nem biztos, hogy jól fog dönteni. Ha jobban tetszik, a CTE helyett készíthetünk egy VIEW-t vagy egy függvényt, ami táblát ad vissza, mind lassú lesz.

Ezzel ma két dolgot tanultunk meg:
1.) Hogyan írjuk lassú lekérdezést (vagy, hogy hogyan kerüljük azt el) 🙂
2.) VIEW-t VIEW-val ne JOIN-oljunk, mert annak beláthatatlan következményei lesznek.

Reklámok

Bűnök a SELECT után

A SELECT utáni bűnök egyik formája, ha úgynevezett “alselect”-et futtatunk a FROM előtt. Ez azért bűn, mert ez az “alselect” annyiszor fog lefutni, amennyi sort ad vissza a lekérdezésünk.

Hogy jobban megértsük, demózzunk.
Csináljunk magunknak teszt adatokat. Segítségképpen a Teszt adatok létrehozása linken találunk egy scriptet, amivel ezt megtehetjük.

Demó 1:
Az optimalizáló okos és tudni fogja, hogy mit akarunk, ezért csak aprónak tűnő különbségeket fogunk látni. Nyissunk egy “New Query” ablakot a Management Studio-ban, nyomjuk meg az “Include Actual Execution Plan” gombot, majd másoljuk bele az alábbi két query-t és kattuntsunk az Execute gombra, hogy tudjuk egymáshoz hasonlíttatni a két lekérdezést.

USE tempdb
GO

-- Bekapcsoljuk a IO mûveletek mutatását
SET STATISTICS IO ON;
-- Bekapcsoljuk a CPU használat mutatását
SET STATISTICS TIME ON;
GO

-- Query 1
SELECT
    a.id,
    SUM(t.amount) as total_amount
FROM accounts a
LEFT JOIN transactions t
    ON a.id = t.accounts_id
WHERE a.id BETWEEN 1 AND 100
GROUP BY a.id

-- Query 2
SELECT
    id,
    (SELECT SUM(t.amount) FROM transactions t WHERE a.id = t.accounts_id) as total_amount
FROM accounts a
WHERE a.id BETWEEN 1 AND 100

Nézzük elõször a “Messages” fület. Nálam ez látható (Csak a lényeget emelem ki):
Query 1:
Table ‘accounts’. Scan count 1, logical reads 4
Table ‘transactions’. Scan count 1, logical reads 1027
CPU time = 31 ms, elapsed time = 32 ms.

Query 2:
Table ‘accounts’. Scan count 1, logical reads 4
Table ‘transactions’. Scan count 1, logical reads 1027
CPU time = 16 ms, elapsed time = 75 ms.

A disk IO megegyezik, viszont a CPU-ból látszik, hogy a Query 2 több időbe került, mint a Query 1.

Gyerünk tovább, nézzük meg az Exectuion plan fület (Sajnos képet valamiért nem tudok bemásolni, de záros határidőn belül megoldom).
Nekem azt írja, hogy 50%-50%, de ne higgyünk neki!
Ha megnézzük a végrehajtási tervet, akkor látszik, hogy a Query 2-ben egy Compute Scalar (0%)-al
több van benne. Hiába ír rá 0%-ot, az csak egy viszonyított érték és akkor is egy plusz művelet, ami processzor időt fog vinni. Sok kicsi sokra megy, főleg az SQL világában.

Kicsit módosítjuk a lekérdezéseket, hogy teljesen szembeötlő legyen a különbség.
Demo 2:

USE tempdb
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

-- Query 3
SELECT
    a.id,
    SUM(t.amount) as total_amount,
    MAX(t.amount) as max_amount
FROM accounts a
LEFT JOIN transactions t
    ON a.id = t.accounts_id
WHERE a.id BETWEEN 1 AND 100
GROUP BY a.id

-- Query 4
SELECT
    id,
    (SELECT SUM(t.amount) FROM transactions t WHERE a.id = t.accounts_id) as total_amount,
    (SELECT MAX(t.amount) FROM transactions t WHERE a.id = t.accounts_id) as max_amount
FROM accounts a
WHERE a.id BETWEEN 1 AND 100

Ha a fentebb leír módokon összehasonlítjuk a két lekérdezést, már feltűnő lesz a különbség, de az elemzés örömét már meghagyom nektek 🙂

Új oszlop hozzáadása online

Új oszlop hozzáadása egy táblához NULL-al online művelet, míg NOT NULL-al offline (SQL Server 2008 R2-ig biztosan), de megkerülhető. Mi most NULL-os oszlopot fogunk hozzáadni egy táblához. A hozzáadásánál az lesz a probléma, hogy az SQL szerver lock-olni fogja a táblát (noha csak meta bejegyzést emlegetnek a books online-ban). Ezt megváltoztatni nem tudjuk, de fel tudunk készülni rá. Készítünk egy alap scriptet, amit 4 részre osztunk, tehát 4 management studio query ablakra lesz szükségünk.

Hogyan futtassuk a scripteket?

Futtassuk a Session 1-et.
Ha befejezte a futást, akkor indítsuk sorban a Session 2-öt, Session 3-at, Session 4-et.

Session 1 (első ablak): Teszt tábla és adatok generálása:

use tempdb
go

-- Ha létezik a tábla, eldobjuk
if object_id('test1', 'U') is not null drop table test1
go

-- Hozzuk létre a táblánkat
create table test1 (
    id int identity(1,1) not null primary key,
    szoveg nvarchar(60) not null,
    szam int not null
)

-- Töltsük fel adatokkal
insert into test1 (szoveg, szam)
  select NEWID(), CHECKSUM(NEWID())
  from sys.columns

-- Ellenőrizzük, hogy sikerült e
select * from test1

Session 2 (második ablak): Tábla intenzív használatának szimulálása:

use tempdb
go

-- Állítsuk a tranzakciós szintet brutálra
set transaction isolation level serializable
go

-- Hogy többször futtatható legyen a scriptünk (itt megjegyezném, hogy akkor leszünk igazán jó script írók, a scritünk többször futtathatóra van megírva)
if exists (select 1 from sys.columns where object_id = OBJECT_ID('test1') and name = 'szam2')
alter table test1 drop column szam2

-- Szimuláljuk, hogy 45 mp-en keresztül intenzíven használva van a tábla
begin tran
    update top (10) test1
    set szam = -szam
    where id = 23

    waitfor delay '00:00:45.000'
commit

Session 3 (ablak 3): Oszlop hozzáadása, ahogy el tudjuk rontani.

use tempdb
go

-- Ezzel garantáltan bedöntjük a szervert, itt annyit fogunk látni, hogy "homokórázunk".
alter table test1 add szam2 int null

-- Nem fogunk error-t látni, viszont megfogjuk a test1 táblát jó időre.
select @@ERROR as error_code

Session 4 (ablak 4): Amivel teszteljük, hogy a szerver bedől e:

use tempdb
go

/***************************************************************
Szimuláljuk a felhasználókat, ami jelen esetben 1 db, az éles szerveren sokezek.
Itt még azt kell megjegyezni, hogy ez a sor nincs benne az UPDATE-elendő sorok között!
Kérdezzünk a táblából! Ha ez nem fut le, a szerver tutira bedől.
***************************************************************/
select * from test1 where id = 100

Amit látnunk kell, hogy a Session 3  csak akkor fejezi be a futását, ha a Session 2, a Session 4 pedig a Session 3 befejeztével fog lefutni. Tehát sorban függőség alakul ki, ami így néz ki: Session 4 vár Session 3-ra, Session 3 pedig Session 2-re. Tehát a felhasználóink csak várnak, csak várnak… Most 45 mp-et várunk aztán helyre áll majd minden.

Ha lefutottak a sciptjeink, akkor cseréljük ki a Session 3 kódját az alábbira:

use tempdb
go

-- Ha 10 ms-os lockba ütközünk, akkor megszakítjuk a futást
set lock_timeout 10;
-- Nem akarunk kiírásokat
set nocount on;
go

declare
   @error int
  ,@count_lock int = 0

-- Ezt állítólag tilos, viszont egyszerű vele kódot írni és books online-ban is több helyen látni a használatát.
while (1=1) begin
   -- Próbáljuk meg a következő utasításokat
   begin try
      -- Létezik e már az oszlop? (ugye többször futtatható!)
      if exists (select 1 from sys.columns where object_id = OBJECT_ID('test1') and name = 'szam2')
      -- Ha már létezik az oszlop, akkor nincs mit hozzáadni, kilépünk
      break;
      -- Hozzáadjuk az új oszlopot (ha tudjuk)
      alter table test1 add szam2 int null
   end try
   -- Ha hibába ütközünk, akkor lekezeljük
   begin catch
      -- eltesszük a hibakódot
      set @error = @@ERROR
      -- Ha nem lock-ba futunk, akkor kilépünk és megvizsgáljuk a problémát
      if @error != 1222 break;
      -- Összeszámoljuk, hogy mennyi lockba ütköztünk
      set @count_lock += 1
   end catch

   -- Várunk az újra futás előtt egy kicsit, így nem nyírjuk ki a procit
   waitfor delay '00:00:01.000'
end

-- Megnézzünk az eredményt, hogy mi történt a futás alatt
select @error last_error_number, @count_lock as count_lock

Futtassuk sorban a scripteket, mint előbb (Session 2, Session 3, Session 4), és most nyugodtan tenyereljünk rá a Session 4 ablakunkban az F5 gombra. Ha mindent jól csináltunk, akkor folyamatosan kapjuk az eredményeket.

Boldogok a felhasználók, mert kapnak eredményt. Boldogok vagyunk mi is, mert nem kell magyarázkodni a főnöknek. Boldog a főnök is, mert megy a biznic.
Nekem a rekordom, hogy egyhuzamban 10 napig update-eltem egy intenzíven használt pár száz milliós táblát ezzel a módszerrel minden probléma nélkül. Persze az update az teljesen más, mint egy oszlop hozzáadása, ezért az a script továbbfejlesztését igényelte.