február 2013 havi bejegyzések

IF vs. CASE

Nap mint nap használjuk ezt a két parancsot, de vajon rá lehet e fogni az egyikre, hogy jobb a másiknál? Már régóta figyelgetem a kettő közötti különbséget és azt vettem észre, hogy a CASE “hosszútávon” gyorsabb, mint az IF. Viszont igazi, mélyreható választ még nem találtam rá, hogy miért, persze lehet én google-zok rosszul 🙂

Minden esetre megosztok néhány mérési eredményt, ami mutatja, hogy a CASE jobban teljesít, mint az IF.

use tempdb
go
set nocount on;
go

-- Eldobálunk mindent, ha már lenne
if object_id('tempdb.dbo.usp_case_in_while', 'P') is not null drop procedure dbo.usp_case_in_while
if object_id('tempdb.dbo.usp_if_in_while', 'P') is not null drop procedure dbo.usp_if_in_while
if object_id('tempdb.dbo.#result', 'U') is not null drop table #result
go
-- Létrehozzuk az eredményt táblát
create table #result ([type] tinyint not null, execution_time bigint not null)
go

-- Eljárás CASE-el
create procedure usp_case_in_while
	@j bigint,
	@from_while bigint
as
declare
	@id int,
	@from_time datetime,
	@to_time datetime,
	@diff bigint,
	@i bigint = 0

	select @from_time = getdate()
	while (@i < @j) begin
		select @i += 1
		select @id = case when @from_while > 100000 then 1 else 2 end
	end
	select @to_time = getdate()

	select @diff = datediff(millisecond, @from_time, @to_time)
	insert into #result ([type], execution_time) values (3, @diff)
go

-- Eljárás IF-el
create procedure usp_if_in_while
	@j bigint,
	@from_while bigint
as
declare
	@id int,
	@from_time datetime,
	@to_time datetime,
	@diff bigint,
	@i bigint = 0

	select @from_time = getdate()
	while (@i < @j) begin
		select @i += 1
		if (@from_while > 100000) set @id = 1
		else set @id = 2
	end
	select @to_time = getdate()

	select @diff = datediff(millisecond, @from_time, @to_time)
	insert into #result ([type], execution_time) values (4, @diff)

go

-- Mérés indul
declare
	@id int,
	@from_time datetime,
	@to_time datetime,
	@diff bigint,
	@from_while bigint = 0,
	@to_while bigint = 1000, -- Ennyiszer fog lefutni a külső ciklus
	@i bigint = 0,
	@j bigint = 300000 -- Ennyiszer fog lefutni a belső ciklus

while (@from_while < @to_while) begin
	-- Módszer 1: CASE-el
	select @from_time = getdate(), @i = 0
	while (@i < @j) begin
		select @i += 1
		select @id = case when @from_while > 100000 then 1 else 2 end
	end
	select @to_time = getdate()

	select @diff = datediff(millisecond, @from_time, @to_time)
	insert into #result ([type], execution_time) values (1, @diff)

	-- Módszer 2: IF-el
	select @from_time = getdate(), @i = 0
	while (@i < @j) begin
		select @i += 1
		if (@from_while > 100000) set @id = 1
		else set @id = 2
	end
	select @to_time = getdate()

	select @diff = datediff(millisecond, @from_time, @to_time)
	insert into #result ([type], execution_time) values (2, @diff)

	-- Módszer 3: CASE SP-ben
	exec dbo.usp_case_in_while @j, @from_while

	-- Módszer 4: IF SP-ben
	exec dbo.usp_if_in_while @j, @from_while

	select @from_while += 1
end

-- Eredmények
select
	case [type]
		when 1 then 'CASE'
		when 2 then 'IF'
		when 3 then 'CASE IN STORED PROCEDURE'
		when 4 then 'IF IN STORED PROCEDURE'
	else 'Unknown' end as [type],
	avg(execution_time) avg_result,
	min(execution_time) min_result,
	max(execution_time) max_result,
	count(*) execution_count
from #result
group by [type]

/******************************************************************************
------------------------------------ RESULT -----------------------------------
type						avg_result	min_result	max_result	execution_count
-------------------------------------------------------------------------------
CASE						3110		2673		3643		100
IF							3813		3250		4873		100
CASE IN STORED PROCEDURE	1975		1703		2500		100
IF IN STORED PROCEDURE		2156		1846		3313		100
******************************************************************************/

/******************************************************************************
------------------------------------ RESULT -----------------------------------
type						avg_result	min_result	max_result	execution_count
CASE IN STORED PROCEDURE	496			466			830			1000
CASE						818			780			1203		1000
IF IN STORED PROCEDURE		562			530			843			1000
IF							993			966			1236		1000
*******************************************************************************/

A TOP a JOIN és az optimalizer

A TOP parancsnak hatása van a JOIN-ok működésére, ugyanis a TOP (x) parancs megváltoztathatja egy lekérdezés végrehajtási tervét. Hogy ezt megértsük, előbb nézzük meg a JOIN-ok típusait.
Vannak logikai és fizikai JOIN-ok.

A logikai JOIN-okat mi írogatjuk a query-kbe:
–    INNER JOIN
–    LEFT (OUTER) JOIN
–    RIGHT (OUTER) JOIN
–    FULL OUTER JOIN
–    CROSS JOIN
–    CROSS APPLY
–    OUTER APPLY
–    (NOT) EXISTS
–    WHERE conditions

A fizikai JOIN-ok: Ilyenekre fordítja az SQL szerver a logikai join-jainkat és az Execution Plan-ban láthatjuk őket.
–    NESTED LOOP
–    MERGE JOIN
–    HASH MATCH

NESTED LOOP: Általában akkor választ ilyet az SQL szerver, ha az egyik tábla kicsi. Ez gyakorlatilag két egymásba ágyazott for ciklus, tehát annyiszor fog végig menni az egyik táblán ahány sort meg fog vizsgálni a másik táblából.

MERGE JOIN: Ez olyan esetben szokott előjönni, amikor a táblák nagyobbak és a kapcsolandó oszlopok már sorba vannak rendezve. Itt egyszer megy a szerver végig mindkét táblán, méghozzá a következőképpen. Elindul az egyiken, és amíg ugyanolyan értéket talál (pl: foreign_id = 1) addig megy, majd ha már mást talál (pl: foreign_id = 2), akkor megáll. Ezután elindul a másik táblán és addig megy, amíg ugyanezt értéket találja (foreign_id = 1). Ezt követően visszatér az első táblára és folytatja onnan, ahonnan abbahagyta (foreign_id = 2). Így megy végig a táblákon.

HASH MATCH: Ez marad akkor, amikor két nagyon nagy táblát kell összekapcsolni és ezek a táblák esetleg index nélküli oszlop(ok)al  vannak JOIN-olva. Hash algoritmus alapján a táblát szétválasztja kisebb kupacokba az oszlopok bizonyos tulajdonsága alapján, majd ezekben a kupacokban keresve viszonylag gyorsan meglesznek a sorok. Írok egy gyenge példát: Vegyünk egy táblát, amiben nevek vannak. Ekkor egy kupac lehet az ’A’ betűvel kezdő, egy másik a ’B’ betűvel kezdődő nevek, és így tovább. Ez elég sok CPU-t és diszket (tempdb) is igényelhet.

És akkor most jöjjön a lényeg 🙂
Ha egy JOIN-os lekérdezésbe TOP-ot teszünk, akkor a TOP után írt számtól függően az SQL szerver megváltoztathatja a fizikai JOIN-t a lekérdezési tervben. Az a tapasztalatom, hogy egy TOP (10)-re inkább LOOP joint választ, míg egy TOP (sok)-ra MERGE vagy HASH algoritmust. Gyakran előfordult, hogy két vagy több többszáz milliós táblát kellett JOIN-olnom és amikor TOP (10)-et írtam, akkor LOOP JOIN-t választott és előfordult, hogy nagyon lassú (akár több órás) futási idővel. Mivel már tudjuk, hogy hogyan működik a LOOP join ezért azt is kitalálhatjuk, hogy csak addig hatékony, amíg a megtalált sorok a vizsgált soroknak az elején vannak. (Pl: egy 1.000.000 soros keresésben hamarabb van meg az eredményt, ha a 10-dik sortól megvannak a találatok, mintha csak a 900.000-dik sortól lennének).  Ilyenkor kell nekünk rásegíteni egy kicsit.

Itt kihangsúlyoznám, hogy ezt a módszert ne az éles kódba írjuk, hanem inkább csak akkor használjuk, amikor ad-hoc lekérdezést kell gyártanunk, de nincs több óránk kivárni az eredményt és ebédszünetre se akarunk menni.

Tehát a technikák:
Használhatjuk az OPTION utasítást a lekérdezés végén:
–    OPTION (LOOP JOIN)
–    OPTION (MERGE JOIN)
–    OPTION (HASH JOIN)

Ez addig jó, amíg csak két táblát kapcsolunk össze, mivel az összes join-t arra állítja be, amit az OPTION-ban megadtunk.
Ha több táblát kapcsolunk, akkor viszont más módszert alkalmazni, mert előfordulhat, hogy az egyik helyen a LOOP join lenne jó, a másikon pedig HASH. Itt jöhet jól a logikai és fizikai join kombinálása. Pl: INNER LOOP JOIN, LEFT HASH JOIN.

Ma csak egy gyenge demót fogok mutatni.
Csináljunk magunknak teszt adatokat. Segítségképpen a Teszt adatok létrehozása linken találunk egy scriptet, amivel ezt megtehetjük.
Nézzük meg az alábbi két Query Execution Plan-ját, illetve a százalékokat, hogy melyiket ítéli az SQL szerver jobbnak és mennyivel. Ezt követően futtassuk az elsőt. Aztán a másodikat, de ezt nem muszáj ám kivárni, mert a különbség „szemmértékkel” is látható lesz. 🙂

-- Itt gyorsan hoz eredményt
select top 10 * from accounts a
inner join transactions t
    on a.id = t.accounts_id
left join  transactions t2
    on a.id = t2.accounts_id
full outer join  transactions t3
    on a.id = t3.accounts_id

-- Itt úgy beoptimalizálom, hogy csak győzük kivárni 🙂
select top 10 * from accounts a
inner hash join transactions t
    on a.id = t.accounts_id
left outer merge join  transactions t2
    on a.id = t2.accounts_id
full outer loop join  transactions t3
    on a.id = t3.accounts_id

Ajánlom mindenkinek, hogy nyomkodja, kombinálja kicsit a logikai és fizikai join-okat (de ne éles környezetben), mert kis tapasztalattal egész jól rá lehet érezni a használatukra.