Programozás kategória bejegyzései

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.

Reklámok

Optimalizáció check constraint-el

A check constraint fő feladata, hogy az általunk meghatározott szabályoknak megfelelő adatokat (itt is figyelni kell a NULL-ra!) engedi be adott mezőbe, egyfajta validáció. Ez által az insert és update műveletek kicsit lassulnak a check constraint-el bíró mezőkön, hiszen a validálás idő.

Ha a check constraint-et a “with check” opcióval hozzuk létre, akkor a már meglévő adatokat is ellenőrzi, hogy megfelelnek e az általunk definiált feltétel(ek)nek. Az ezzel az opcióval létrehozott constraint egy nagyon erős teljesítménynövelő hatást is kiválthat. Hogyan?

Pl: Ha van egy státusz mezőnk, ami tinyint típusú, check contraint-al biztosítjuk, hogy 1-10-ig mehetnek bele adatok, a query-nk pedig így néz ki: SELECT * FROM tábla WHERE státusz = 11, akkor az optimalizer fogja tudni, hogy 11-es érték biztosan nincs az oszlopban, így eredmény sem lesz, ezért egy page-et sem fog felolvasni a táblából. Ebben az esetben sokkal hatékonyabb, mint bármelyik index!

Ez pedig arról jutott eszembe, hogy mostanában volt egy esetem. Olyan lekérdezésre bukkantam, amit már sehol nem használnak, de másodpercente sokkal többször hívták a kelleténél, toronymagasan vezette az egységnyi idő alatti CPU és diszk használatot. Ez a query így nézett ki (a query csak illusztráció 🙂 :

SELECT p.[user_id] p.[transaction_amount]
FROM [#tmp_user_property] p
JOIN [#tmp_user] u ON u.id = p.[user_id]
WHERE u.[name] = 'User 5'
    AND p.[transaction_date] > '2014-01-01 00:00:00.000'
GO

Csináljuk a lekérdezéshez adatokat:

USE [tempdb]
GO

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

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

CREATE TABLE #tmp_user (
    id INT NOT NULL PRIMARY KEY CLUSTERED
    ,NAME VARCHAR(128) NOT NULL
    )
GO

CREATE TABLE #tmp_user_property (
    id INT NOT NULL PRIMARY KEY CLUSTERED
    ,[user_id] INT NOT NULL
    ,transaction_amount INT NOT NULL
    ,transaction_date DATETIME NOT NULL
    )
GO

CREATE INDEX ix_user_id ON [#tmp_user_property] ([user_id])
GO

INSERT INTO [#tmp_user] ([id],[name])
SELECT TOP 10000
    row_number() OVER (ORDER BY (SELECT NULL)) AS id
    ,CONCAT ('User ',row_number() OVER (ORDER BY (SELECT NULL))) AS NAME
FROM sys.[columns] c1
CROSS JOIN sys.[columns] c2
GO

INSERT INTO [#tmp_user_property] (
    [id]
    ,[user_id]
    ,[transaction_amount]
    ,[transaction_date]
)
SELECT TOP 1000 t.id
    ,t.id AS [user_id]
    ,left(abs(checksum(newid())), 4) AS transaction_amount
    ,dateadd(day, row_number() OVER (ORDER BY (SELECT NULL)), '2010-01-01')
FROM [#tmp_user] t
ORDER BY t.[id]
GO

Ha megnézzük az adatokat, akkor láthatjuk, hogy 2014 évnél korábbiak vannak benne, tehát a fenti query soha nem ad eredményt.

Kapcsoljuk be az IO statisztikát:

set statistics io on;

Kapcsoljuk be az “Include Actual Execution Plan (Ctrl + M)” funkciót a management studióban, majd futtassuk a lekérdezést.

Ezeket kaptam:
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#tmp_user_property__000000000006’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

A plan pedig így néz ki:
050_result_01

Itt szépen látszik, hogy 6 darab 8 kb-os lapot érintett a lekérdezés és két táblát. Bár a végrehajtási terveben van egy Clustered Index Scan és egy jó index-el ezen lehet javítani, de nekünk most ennél több kell!

Rakjuk fel a check constraint-et:

ALTER TABLE [#tmp_user_property]
	WITH CHECK ADD CONSTRAINT CH_user_property_transaction_date CHECK (transaction_date < '2013-01-01')

Futtassuk ismét a lekérdezést, majd lássuk mit kapunk:
A Messages fülön: (0 row(s) affected)
A plan pedig ez:
050_result_02

A messages fülön láthatjuk, hogy egyetlen page-et sem érintettünk (a táblában szereplő adatokból), a végrehajtási tervből pedig látszik, hogy a táblához nem is nyúlt az optimalizer.

A fenti példánál mégegyszerű volt megoldani a problémát, mert megkértem a fejlesztőt, hogy nézzen már utána, hogy mire van használva a query. Viszont vannak esetek, amikor nincs fejlesztő, akivel lehet ezt le lehet kommunikálni, ilyen esetekben ez a check constraint nagyon jól jöhet.

A check constraint-ek egy másik, sokkal jobb felhasználási területe a particionált view. Akit érdekel a téma keressen rá, mert lehet, hogy holnap már hasznát tudja venni 🙂

 

Több soron futó triggerek

A triggerek az adatbázis szerverek egyik legveszélyesebb jószágai. A háttérben dolgoznak szépen csendesen, mindenki által elfeledve. Ráadásul a teljesítményre is rosszul hatnak (sőt, láttam már szervert is bedőlni miattuk). Persze azért találták ki, hogy éljünk velük, de amíg lehet, célszerű kerülni a használatukat, szerintem.

Az SQL szerverben a triggereket nincs lehetőség soronként (mint pl. Oracle-ben a “for each row”) futtatni, hanem utasításonként futnak le. Egyesek szerint tehetünk cursor-t a triggerbe (aztán szégyelljük el magunkat rendesen), de azzal nem a trigger fog soronként lefutni, hanem a feldolgozás.

A lényeg, hogy az inserted és deleted táblákban több sor is lehet. Ez ne feledjük! No és ezt miért mondom? Mert divat az inserted vagy deleted táblákból változókba tenni az értékeket és a későbbiekben azokkal dolgozni.

Végezetül demózzunk egy kicsit. Csinálunk egy users táblát a felhasználók adatinak tárolására és egy log táblát az users-hez, amibe mentjük minden módosításkor a módosult sorokat.

 

use tempdb
go

if object_id('tbl_users', 'U') is not null
	drop table [tbl_users]
go

if object_id('log_tbl_users', 'U') is not null
	drop table [log_tbl_users]
go

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

create table [tbl_users] (
	[id] int not null primary key,
	[name] varchar(255) not null,
	[password] varchar(36) not null,
	[user_hash] uniqueidentifier not null,
	[address] varchar(255) null
)
go

create table [log_tbl_users] (
	[id] int identity not null primary key,
	[tbl_users_id] int not null,
	[name] varchar(255) not null,
	[password] varchar(36) not null,
	[user_hash] uniqueidentifier not null,
	[address] varchar(255) null,
	[created_at] datetime default getutcdate()
)
go

;with cte as (
	select top 100
		row_number() over(order by (select null)) as id
	from sys.columns c
)

insert into [dbo].[tbl_users] (
		[id] ,
		[name] ,
		[password] ,
		[user_hash] ,
		[address]
	)
select
	id,
	concat('Name ', id) as [name],
	newid() as [password],
	newid() as user_hash,
	concat('Address ', id) as [address]
from [cte]

go
-- A trigger, ami csak 1 sor módosítására van felkészítve
create trigger trg_tbl_users_update on [dbo].[tbl_users]
after update
as
	declare
		@id int,
		@name varchar(255),
		@password varchar(36),
		@user_hash uniqueidentifier,
		@address varchar(255)

	select
		@id = [id],
		@name = [name],
		@password = [password],
		@user_hash = [user_hash],
		@address = [address]
	from deleted

	insert into [dbo].[log_tbl_users]
	        ( [tbl_users_id] ,
	          [name] ,
	          [password] ,
	          [user_hash] ,
	          [address]
	        )
	values (
		@id,
		@name,
		@password,
		@user_hash,
		@address
	)
go

Van egy 100 soros users táblánk és egy 0 soros log táblánk. Adjunk ki egy módosító parancsot, ami több sort érint:

begin tran
	update [dbo].[tbl_users]
	set [name] += '1'
	where [id] < 10

	select * from [dbo].[log_tbl_users] order by id
rollback
go

Az eredmény 1 sor.

049_tobbsoros_triggerek_result_1

Módosítsuk a triggerünket úgy, hogy alkalmas legyen több sor módosítását lekövetni:

alter trigger trg_tbl_users_update on [dbo].[tbl_users]
after update
as
	insert into [dbo].[log_tbl_users] (
			[tbl_users_id] ,
			[name] ,
			[password] ,
			[user_hash] ,
			[address]
	    )
	select
		[id],
		[name],
		[password],
		[user_hash],
		[address]
	from deleted
go

Futtassuk ismét az update-et:

begin tran
    update [dbo].[tbl_users]
    set [name] += '1'
    where [id] &lt; 10

    select * from [dbo].[log_tbl_users] order by id
rollback
go

Az eredményünk végre az elvárt:
049_tobbsoros_triggerek_result_2

 

És még egy fontos dolog. Az output  cause és a trigger nem kompatibilisek. Vagy egyik, vagy másik.