február 2015 havi bejegyzések

Random join

Úgy gondolom, hogy jó, ha van talonban egy olyan példa script, amivel random lehet sorokat join-olni, akár teszt adatok létrehozásához, akár más célból. Az alapfelállás az, hogy van 2 táblánk, legyen ez első mondjuk user, ahol a felhasználóinkat tároljuk, a második meg a country, amelyekkel a felhasználók valamilyen kapcsolatba kerülhetnek. Itt három féle random join-t mutatnék:

  1. 1-1 kapcsolat van a felhasználók és counrty között és minden felhasználóhoz kötelezően tartozik 1 country.
  2. 1-1 kapcsolat van a felhasználók és country között, de a felhasználókhoz nem feltétlen tartozik country.
  3. 1-több kapcsolat van a felhasználók és country között, ahol 1 felhasználóhoz 1 vagy több country tartozik.

 

Mindhárom esetben a checksum() függvény lesz a segítségünkre, amit a join-ba fogunk tenni, a moduló osztással pedig garantáljuk, hogy olyan számokat kapjunk, ami a country tábla id-jaival egyezik vagy 0 értéket ad, feladattól függően. A feladat kitétele, hogy a country táblában a számok folytonosak legyenek, tehát ne legyenek lukak a sorszámok között.

Lássuk a demót:

IF object_id('tempdb..#user') IS NOT NULL
	DROP TABLE #user
GO

IF object_id('tempdb..#county') IS NOT NULL
	DROP TABLE #county
GO

CREATE TABLE #user (
	id INT NOT NULL PRIMARY KEY
	,NAME VARCHAR(255) NOT NULL
	)

CREATE TABLE #county (
	id INT NOT NULL PRIMARY KEY
	,country VARCHAR(255) NOT NULL
	)
GO
INSERT INTO #county ([id],[country])
VALUES
	(1,'HU'),
	(2,'RO'),
	(3,'US'),
	(4,'RU');

;WITH cte AS (
	SELECT TOP 1000 row_number() OVER (ORDER BY (SELECT NULL)) AS id
	FROM sys.[columns] c
	CROSS JOIN sys.[columns] c2
	)
INSERT INTO #user ([id],[name])
SELECT TOP 1000 id
	,CONCAT ('User ',[id]) AS NAME
FROM [cte] c

--select * from #county
--select * from #user
-- Első eset: mindig legyen 1 sor az userhez
SELECT u.*,c.*
FROM #user u
JOIN #county c ON ABS(CHECKSUM(NEWID(), u.id)) % 4 + 1 = c.[id]

-- Második eset, amikor 1 userhez 1 vagy 0 country tartozik
SELECT u.*,c.*
FROM #user u
JOIN #county c ON CHECKSUM(NEWID(), u.id) % 4 = c.[id]

-- Harmadik eset, a kapcsoló táblás, amikor 1 userhez több country tartozhat
SELECT u.*,c.*
FROM #user u
JOIN #county c ON ABS(CHECKSUM(NEWID(), u.id)) % 4 + 1 >= c.[id]
ORDER BY 1,2,3

Ha esetleg nem folytonos sorszámozású a country táblánk, arra is van megoldás, amit a CTE és a row_number páros nyújt:

;with cte as (
    select *, row_number() over(order by (select null)) as rn
    from #county
)

 

Majd a country tábla helyett, ezt a CTE-t használjuk a join-ban, illetve a rn oszloppal végezzük a  mezőkapcsolást.

 

Végezetül, akit érdekel eltöprenghet, hogy fenti példákban, a táblák nagyságától függően az SQL Server melyik fizikai join-t (LOOP, HASH, MERGE) fogja választani a végrehajtási tervben, illetve melyiket biztosan nem. Aztán tesztelje le, tanuljon és profitáljon belőle! 🙂

Reklámok

Az SQL-es logika

Több programozóval beszélve jutottam arra következtetésre, hogy ez téma megér egy postot.

A “hagyományos” programozás általában úgy megy, hogy a feladatot lebontjuk a legkisebb egységekre, ezeket lekódoljuk, majd ezen kódrészleteket használjuk fel. Nos, ez a logika az SQL-ben nem szokott hatékony lenni. Az SQL a halmazműveletekben erős, erre kell építkezni. Nézzünk egy nagyon egyszerű példát, aztán próbáljuk is ki:

Vannak user-eink, akik tárolnak nálunk pénzt. Kapunk egy user listát, akiknek x összegű bónuszt jóvá kell írni. Az adatbázisunk álljon 3 táblából:

  1. users tábla: id, név, egyenleg
  2. tranzakciók tábla: id, user_id, összeg, jogcím
  3. bónusz: user_id, bónusz összege

A “hagyományos” programozói logika a feladatot a következőképpen oldaná meg (Az update_one_user eljárás lesz):

  1. Ciklust indítunk a bónusz táblában lévő userekre
  2. Tranzakció indul
  3. Update adott user egyenlegén (UPDATE users SET összeg = összeg + bónusz WHERE id = @ciklus_user_id)
  4. Tranzakció insert adott userhez (INSERT INTO tranzakciók (user_id, összeg, jogcím) VALUES (@ciklus_user_id, bónusz, ‘bónusz jóváírás’)
  5. Tranzakció vége
  6. Ciklus vége

Ha megnézzük ezt a logikát, akkor látjuk, hogy users * 2 adatmódosító utasítás.

Ezzel szemben az SQL-es logika a következőt mondja (Az update_with_join eljárás lesz):

  1. Tranzakció indul
  2. Adjuk hozzá a userek egyenlegéhez a bónuszt (UPDATE u SET u.egyenleg = u.egyenleg + b.bónusz FROM users u JOIN bónusz b ON u.id = b.user_id)
  3. Könyveljük el a tranzakciók táblába is (INSERT INTO tranzakciók (user_id, összeg, jogcím) SELECT user_id, bónusz, ‘bónusz jóváírás’ FROM bónusz)
  4. Tranzakció vége

Nos, ebben az esetben ez két utasítás.

Köztes megoldás lehet, ha a tranzakciók táblára teszünk egy triggert, ami a tranzakcióhoz tartozó user egyenlegét frissíti. Részemről nem szeretem a triggereket, de a teszt kedvéért ezt is megcsináljuk, csak hogy lássuk mennyivel lassabb (Az update_with_trigger eljárás lesz):

  1. Tranzakció indul
  2. Könyveljük el a tranzakciók táblába is (INSERT INTO tranzakciók (user_id, összeg, jogcím) SELECT user_id, bónusz, ‘bónusz jóváírás’ FROM bónusz).
  3. Tranzakció vége

 

Nézzük a demót:
Hozzuk létre a táblákat és eljárásokat:

use tempdb
go

if object_id('users', 'U') is not null
	drop table users
go

if object_id('transactions', 'U') is not null
	drop table transactions
go

if object_id('user_bonus', 'U') is not null
	drop table user_bonus
go

if object_id('update_one_user', 'P') is not null
	drop procedure update_one_user
go

if object_id('update_with_join', 'P') is not null
	drop procedure update_with_join
go

if object_id('update_with_trigger', 'P') is not null
	drop procedure update_with_trigger
go

create table users (
	id int not null primary key,
	name varchar(255) not null,
	balance int not null
)

go

create table transactions (
	id int not null identity primary key,
	user_id int not null,
	amount int not null,
	pretence nvarchar(255) not null
)

go

create table user_bonus (
	user_id int not null primary key,
	bonus_amount int not null
)

go

create procedure update_one_user
	@user_id int,
	@amount int,
	@pretence nvarchar(255)
as
begin
	set nocount on;

	begin tran
		update users
		set balance += @amount
		where id = @user_id

		insert into transactions (user_id, amount, pretence)
		values (@user_id, @amount, @pretence)
	commit
end

go

create procedure update_with_join
as
begin
	set nocount on;

	begin tran
		update u
		set u.balance += b.bonus_amount
		from users u
		join user_bonus b
			on u.id = b.user_id

		insert into transactions (user_id, amount, pretence)
		select user_id, bonus_amount, N'bónusz jóváírás' as pretence
		from user_bonus
		order by user_id
	commit
end

go

create procedure update_with_trigger
as
begin
	set nocount on;

	begin tran
		insert into transactions (user_id, amount, pretence)
		select user_id, bonus_amount, N'bónusz jóváírás' as pretence
		from user_bonus
		order by user_id
	commit
end
go

Töltsük fel a táblákat:

use tempdb
go

truncate table users;
truncate table transactions;
truncate table user_bonus;
go

;with cte as (
	select top (10000)
		row_number() over(order by (select null)) as id
	from sys.columns c1
	cross join sys.columns c2
)

insert into users (id, name, balance)
select
	id,
	concat('Name ', id) as name,
	id * 1000 as balance
from cte

go

insert into transactions (user_id, amount, pretence)
select id as user_id, balance as amount, N'befizetés' as pretence
from users

go

insert into user_bonus (user_id, bonus_amount)
select id as user_id, left(abs(checksum(newid())),4) from users order by id
go

select * from users u
join user_bonus b
	on u.id = b.user_id
order by u.id

Végezzük el a tesztet:

use tempdb
go

set nocount on;

declare @user_id int
declare @bonus_amount int
declare C cursor for
	select user_id, bonus_amount
	from user_bonus
	order by user_id

open C
	fetch next from C into @user_id, @bonus_amount
	while (@@FETCH_STATUS = 0)
	begin
		exec update_one_user
			@user_id = @user_id,
			@amount = @bonus_amount,
			@pretence = N'bónusz jóváírás'
		fetch next from C into @user_id, @bonus_amount
	end
close C
deallocate C
go

execute update_with_join
go

if object_id('trg_update_user_balance', 'TR') is not null
	drop trigger trg_update_user_balance
go

create trigger trg_update_user_balance
	on dbo.transactions after insert
as
begin
	update u
	set u.balance += i.amount
	from users u
	join inserted i
		on u.id = i.user_id
end
go

exec update_with_trigger

Végezetül nézzük meg az eredményt:

use tempdb
go

select
	p.name,
	s.execution_count,
	s.total_worker_time,
	s.total_logical_reads,
	s.total_logical_writes,
	s.total_physical_reads
from sys.dm_exec_procedure_stats s
join sys.procedures p
	on s.object_id = p.object_id
where s.database_id = db_id()

Nálam ezt adta vissza a lekérdezés:

52_result

Tisztán látszik, hogy az SQL-es logika volt a legolcsóbb a szervernek.

Ha egyszerűen akarnám megfogalmazni az SQL-es logikát, akkor azt mondanám, hogy halmazonként (jelen esetben táblánként) hajtjuk végre a műveleteket (és nem pl: userenként, ami itt egy alkalmazás logikai egység), amit az SQL szerver nagyságrendekkel gyorsabban kezel.

Ez a példa nagyon leegyszerűsített, a valóságban ezek a feladatok jóval bonyolultabbak és nagyobb adatmennyiségeken kell dolgozni, ahol az SQL-es logikát is ciklusba kell helyezni (több millió sor update-je egyben fut x óráig, aztán lehet rollback lesz a vége mert nem lesz elég erőforrása a szervernek, közben elérhetetlen lesz az egész adatbázis, mert lock alá kerülnek a táblák), de nem egyesével, hanem nagyobb egységekben (pl: 10.000-esével) hajtódnak végre a műveletek.