Lekérdezések kategória bejegyzései

Hiányzó index megkerülése

Előfordul olyan eset, amikor kéne egy index, de nincs. Feltenni nem tudjuk, nem akarjuk, nem lehet, stb, a lényeg, hogy nem is lesz, de a query-t fel kellene gyorsítani. Ilyen esetben meg lehet próbálkozni hasonlóságokat keresni az adatbázisban, amikre támaszkodva kicsit másképp, de megírható az sql kód, ami ugyanazt az eredményhalmazt adja vissza, viszont teljesítményben nagyságrendekkel felülmúlja azt.

Kifejtem egy egyszerű példán keresztül, aztán le is demózzuk. Van egy transactions táblánk sok sorral az alábbi mezőkkel:
– id: egyedi azonosító, folyamatosan növekvő sorszámmal
– user_id: adott user-hez tartozik a tranzakció
– amount: a tranzakció összege
– create_date: a tranzakció dátuma (kb. adatbázisba kerülés időpontja)
– egyéb, számunkra most lényegtelen oszlopok

A táblán 1 index van, az id mezőn, ami primary key és clustered index is.

A feladat, hogy számoljuk meg adott időponttól a felhasználói tranzakciókat. Ebben az esetben a lekérdezés ez lenne:

select
	count(*) as cnt
from transactions
where create_date >= @datum

Index híján ebből clustered index scan lesz, ami jelen esetben egyenlő a teljes tábla beolvasásával és ez nem szokott hatékony lenni. Ilyenkor jöhet az ötletelés, az ötlet pedig itt a hasonlóságon alapul. Az id és a create_date mezők hasonlítanak egymásra abban, hogy mindketten kb. növekvő sorrendben haladnak. Nézzünk erről egy képet:

missing_index_01

Azokat a sorokat keressük, ahol a create_date >= ‘2010-01-17’ -el. A zöld vonal alatti rész a miénk és itt már látszik, hogy a 16-os id-tól kellenének a sorok. Ebben az esetben a lekérdezésünk már így nézne ki:

select
	count(*) as cnt
from transactions
where create_date >= '2010-01-17'
    and id >= 16
group by user_id

Ezzel már sikerül kizárni a tábla 3/4-ed részét ami sok sornál jelentős adatmennyiség. Mivel a gyakorlatban az adatbázisokban a dátum nem szokott monoton nőni, ezért a dátumszűrést célszerű továbbra is meghagyni. A következő feladat megtalálni a 16-os id-t. Ezt olcsón a logaritmikus kereséssel tehetjük meg.

A terv készen is lenne, de még meg kell oldani, hogy a gyakorlatban az adatbázisban a dátumok nem monoton nőnek, hanem egyszer-egyszer nagyobb időpont kerül a kisebb elé, bár kis időkülönbség van köztük. Pl: a fenti képen a 16-os és a 18-es id dátumait megcseréljük. Ilyenkor van az, hogy kicsit alá kell lőni a dátumnak és nem azt az id-t keressük, amihez a ‘2010-01-17’ tartozik, hanem a két nappal azelőtti, ‘2010-01-15’, ami a 14-es id. Ekkor a lekérdezés már így fog kinézni:

select
	count(*) as cnt
from transactions
where create_date >= '2010-01-17'
    and id >= 14
group by user_id

Ez tök jó, de mi van, ha nincs ‘2010-01-15’-ös dátum? Erre kell megadni egy tartománykeresést. Pl: Olyan id-t keresünk, ami ‘2015-01-12’ és ‘2015-01-15’ között van. Az SQL szerver pedig meg fogja találni a 11-14 id-k valamelyikét, nekünk mindegy is, hogy melyiket, a lényeg, hogy legyen egy id-nk, amivel a tábla nagy részét ki lehet zárni a where feltételben.

 

Egy millió soron fogunk dolgozni, de ennek a sokkal többszörösén is jól használható a logika, sőt, minél több a sor, valószínűleg annál nagyobb a nyereség. És akkor a demó.

Hozzuk létre az objektumokat és adatokat:

use [tempdb]
go

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

if object_id('dbo.usp_transactions_count_by_date_with_hop_search', 'P') is not null
	drop procedure dbo.usp_transactions_count_by_date_with_hop_search
go

if object_id('dbo.usp_transactions_count_by_date', 'P') is not null
	drop procedure dbo.usp_transactions_count_by_date
go

if object_id('dbo.v_proc_stat', 'V') is not null
	drop view dbo.v_proc_stat
go

-- Később ezt a view-t kérdezzük le, egyetlen szerepe, a blog hosszának csökkentése.
create view dbo.v_proc_stat
as
	select top 10
		p.[name],
		s.[execution_count],
		s.[total_elapsed_time],
		s.[total_worker_time],
		s.[total_logical_reads],
		s.[total_logical_writes],
		s.[plan_handle]
	from sys.[procedures] p
	join sys.[dm_exec_procedure_stats] s
		on p.[object_id] = s.[object_id]
	where p.[name] in ('usp_transactions_count_by_date', 'usp_transactions_count_by_date_with_hop_search')
	order by p.[name]
go

-- Tábla készítés
create table dbo.transactions (
	id int not null,
	user_id int not null,
	amount int not null constraint df_transactions_amount default 0,
	create_date datetime not null,
	char_field char(40) not null
	constraint pk_transactions primary key clustered (id)
)
go

-- Adatok betöltése
;with cte as (
	select top 1000000
		row_number() over(order by (select null)) as id
	from sys.[columns] c1
	cross join sys.[columns] c2
	cross join sys.[columns] c3
	cross join sys.[columns] c4
	cross join sys.[columns] c5
	cross join sys.[columns] c6
	cross join sys.[columns] c7

)


insert into [dbo].[transactions] with (tablock)
        ( [id] ,
          [user_id] ,
          [amount] ,
          [create_date],
		  [char_field]
        )
select
	id,
	[id] % 20000 + 1 as user_id,
	left(abs(checksum(newid())),4) as amount,
	dateadd(mi, id, '2010-01-01 00:00:00.000') as create_date,
	'' as charfield
from [cte]

go

-- Ellenőrzés
select top 20 * 
from [dbo].[transactions] 
where [id] >= 499995 
order by [id] 
go

-- Dátumok növekvő sorrendjének megbontása
;with cte as (
	select top 10 
		[id], 
		create_date,
		1000009 - id as upd_id
	from [dbo].[transactions] 
	where id >= 500000
	order by [id]
)
 
update c1
set c1.create_date = c2.create_date
from cte c1 
join cte c2 
	on c1.[id] = c2.[upd_id]

go
-- Ellenőrzés
select top 20 * 
from [dbo].[transactions] 
where [id] >= 499995 
order by [id] 
go

-- Tárolt eljárás logaritmikus kereséssel
create procedure dbo.usp_transactions_count_by_date_with_hop_search
	@search_date datetime  
as

	set nocount on;
	
	declare @search_date_from datetime = dateadd(mi, - 15, @search_date)
	declare @search_date_to datetime = dateadd(mi, - 8, @search_date)

	/*
	select 
		@search_date as search_date, 
		@search_date_from as search_date_from, 
		@search_date_to as search_date_to
	*/

	declare @hop int = 0
	declare @max_hop int = 30

	declare @min_id int 
	declare @max_id int
	declare @tmp_id int
	declare @result_id int

	declare @min_date datetime
	declare @max_date datetime
	declare @tmp_date datetime

	declare @end int = 0

	select top 1
			@min_id = [id], 
			@min_date = [create_date]
	from [dbo].[transactions]
	order by [id] asc


	select top 1
			@max_id = [id], 
			@max_date = [create_date]
	from [dbo].[transactions]
	order by [id] desc



	while (@end = 0)
	begin
		select top 1 
			@tmp_id = [id],
			@tmp_date = [create_date]
		from [dbo].[transactions]
		where [id] >= ((@max_id - @min_id) / 2) + @min_id
		order by [id] 

		if @search_date_from between @min_date and @tmp_date 
		begin
			set @max_date = @tmp_date
			set @max_id = @tmp_id
		end
		else if @search_date_from between @tmp_date and @max_date
		begin
			set @min_date = @tmp_date
			set @min_id = @tmp_id
		end

		if @min_date between @search_date_from and @search_date_to
		begin
			set @end = 1
			set @result_id = @min_id
		end
		else if @max_date between @search_date_from and @search_date_to
		begin
			set @end = 1
			set @result_id = @max_id
		end

		set @hop += 1;

		if @hop >= @max_hop
		begin
			set @end = 1;
			set @result_id = 0;
		end
	end

	/*
	select 
		@hop as hop, 
		@max_hop as max_hop,
		@result_id as result_id
	*/

	select 
		count(*) as cnt
	from [dbo].[transactions] 
	where [id] >= @result_id
		and [create_date] >= @search_date
go

-- Tárolt eljárás "normál" kereséssel
create procedure dbo.usp_transactions_count_by_date
	@search_date datetime
as
	set nocount on;

	select 
		count(*) as cnt
	from [dbo].[transactions] 
	where [create_date] >= @search_date
go

Futtasuk az első tesztünket és lássuk az eredményt is:

/* id = 500002: tábla közepe */
exec dbo.[usp_transactions_count_by_date_with_hop_search] @search_date = '2010-12-14 05:27:00.000'
go 10
exec dbo.[usp_transactions_count_by_date] @search_date = '2010-12-14 05:27:00.000'
go 10

select * from v_proc_stat
go

03_index_search

Látszik, hogy kicsit olcsóbb volt a logaritmikus kereséses módszer.
Töröljük a planokat, aztán jöjjön a következő teszt, a tábla végéről keresünk:

-- select * from v_proc_stat plan_handle mezőjéből kell behelyettesíteni a varbinarit
DBCC FREEPROCCACHE (0x05001B00CC7C5D4380584F3A0200000001000000000000000000000000000000000000000000000000000000) WITH NO_INFOMSGS
DBCC FREEPROCCACHE (0x05001B00935869421015C7C50100000001000000000000000000000000000000000000000000000000000000) WITH NO_INFOMSGS
go

/* id = 999892: tábla vége */
exec dbo.[usp_transactions_count_by_date_with_hop_search] @search_date = '2011-11-26 09:01:00.000'
go 10
exec dbo.[usp_transactions_count_by_date] @search_date = '2011-11-26 09:01:00.000'
go 10

select * from v_proc_stat
go

Nézzük az eredményt:
04_index_search
Láthatjuk, hogy sokkal, de sokkal hatékonyabb volt a logaritmikus keresés.
Nézzük tesztet a tábla eleljéről is:

DBCC FREEPROCCACHE (0x05001B00CC7C5D4380584F3A0200000001000000000000000000000000000000000000000000000000000000) WITH NO_INFOMSGS
DBCC FREEPROCCACHE (0x05001B00CC7C5D4380584F3A0200000001000000000000000000000000000000000000000000000000000000) WITH NO_INFOMSGS
go

/* id = 10: tábla eleje */
exec dbo.[usp_transactions_count_by_date_with_hop_search] @search_date = '2010-01-01 00:10:00.000'
go 10
exec dbo.[usp_transactions_count_by_date] @search_date = '2010-01-01 00:10:00.000'
go 10

select * from v_proc_stat
go

Az eredmény:
05_index_search
Láthatjuk, hogy itt kicsit rosszabb volt a logaritmikus keresés.

Nézzük egy összesített tesztet is:

DBCC FREEPROCCACHE (0x05001B00CC7C5D4380584F3A0200000001000000000000000000000000000000000000000000000000000000) WITH NO_INFOMSGS
DBCC FREEPROCCACHE (0x05001B00935869421015C7C50100000001000000000000000000000000000000000000000000000000000000) WITH NO_INFOMSGS
go


exec dbo.[usp_transactions_count_by_date_with_hop_search] @search_date = '2010-12-14 05:27:00.000'
go 10
exec dbo.[usp_transactions_count_by_date] @search_date = '2010-12-14 05:27:00.000'
go 10

exec dbo.[usp_transactions_count_by_date_with_hop_search] @search_date = '2011-11-26 09:01:00.000'
go 10
exec dbo.[usp_transactions_count_by_date] @search_date = '2011-11-26 09:01:00.000'
go 10

exec dbo.[usp_transactions_count_by_date_with_hop_search] @search_date = '2010-01-01 00:10:00.000'
go 10
exec dbo.[usp_transactions_count_by_date] @search_date = '2010-01-01 00:10:00.000'
go 10


select * from v_proc_stat
go

Az eredmény:
06_index_search

Itt is jelentős teljesítmény növekedést értünk el.

A fenti példákból jól látszott, hogy akkor volt sokkal hatékonyabb a logaritmikus kereséses módszer a teljes táblaolvasással szemben, ha a tábla egy jelentős részét sikerült kizárni. Én pont így használom az  sok-sok millió soros táblákon és marha gyors. A scriptből többet is ki lehet hozni, aki akarja, annak hajrá! 🙂

Reklámok

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! 🙂

Teljesítményigényes tárolt eljárások – egyszerűen

Ebben a postban egy egyszerű query-t mutatnék, amivel ki tudjuk listázni a szerveren futó tárolt eljárásokat, és hogy azok eddig mennyi erőforrást használtak.

Lássuk is ezt a query-t:

 

use [master]
go

select
    db_name(database_id) as dbname,
    OBJECT_NAME(object_id, database_id) as sp_name,
    count(*) as stat_count,
    sum([total_worker_time]) sum_total_worker_time,
    sum([total_elapsed_time]) sum_total_elipsed_time,
    min([cached_time]) min_cached_time,
    max([cached_time]) max_cached_time,
    min([last_execution_time]) min_last_execution_time,
    max([last_execution_time]) max_last_execution_time,
    sum([execution_count]) sum_execution_count,
    sum(total_worker_time) / sum(execution_count) as avg_worker_time_per_count_in_microsecond,
    sum(total_logical_reads) as sum_total_logical_reads,
    sum(total_logical_writes) as sum_total_logical_writes,
    min(min_worker_time) as min_worker_time,
    max([max_worker_time]) as max_worker_time,
    max([last_worker_time]) as max_last_worker_time,
    sum(total_physical_reads) as sum_total_physical_reads
from sys.[dm_exec_procedure_stats] ps
--where database_id = 2
group by database_id, object_id
order by sum_total_worker_time desc
--order by avg_worker_time_per_count_in_microsecond desc
--order by sum_execution_count desc

Összesen 1 db nézet, elég egyszerű.

Megvan a resultset, de mit keressünk benne?
Én általában 3 dolog szerint keresek, ezeket pedig jól mutatják az order by záradékok. Akkor mindenképpen gyanút kell fogni, ha az első találatok egy vagy több helyiértékkel több költségbe kerülnek, mint az többi eljárás.

1.) sum_total_worker_time: Ez megmutatja, hogy eddig összesen mennyi CPU erőforrásba került a tárolt eljárás. A számítás kezdete a cache-be kerülés időpontja.

2.) avg_worker_time_per_count_in_microsecond: Ez megmutatja, átlagosan mennyi CPU erőforrásba került a tárol eljárás. Ez azért hasznos mutató, mert a sum_total_worker_time torz képet mutat, ha 1 tárolt eljárás újra fordul és a cache-be kerülés időpontja jóval későbbi, mint a többi eljárásé. Pl: “A” tárol eljárás 10 napja van a cache-ben, futott 1.000-szer és elhasznált 10.000 CPU-t, míg “B” tárolt eljárás pedig csak 1 napja van a cache-ben futott 2-szer és elhasznált 500 CPU-t. Az átlagból látszik, hogy “B” tárolt eljárás sokkal költségesebb.

3.) sum_execution_count: Ez pedig azért, hogy lássuk mit hívunk legtöbbször, ugyanis előfordulhat, hogy az alkalmazás cache-be tárolható az adat. Tipikusan ilyen adat egy felhasználó numerikus és karakteres azonosítója, mert azokra nem jellemző, hogy változnak. Ha nem minden alkalommal a db-ből kérjük le, hanem az alkalmazás cache-ből kiolvassa, máris sokat könnyítettünk az adatbázison.

Nézzünk egy kis demót. Csinálunk 1 táblát, készítünk 3 db tárolt eljárást (+ 1 függvényt) és megnézzük, hogy melyik mennyi erőforrásba került.

 

use tempdb
go

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

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

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

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

if object_id('udf_get_user_amount_by_name', 'FN') is not null
	drop function udf_get_user_amount_by_name
go

-- Create table
create table users (
	id int identity primary key,
	name nvarchar(255) not null,
	amount int not null,
	country_code varchar(2) not null,
	created_at datetime not null
)

go

-- Create data
;with cte as (
	select top (10000)
		row_number() over(order by (select null)) as rownumber,
		abs(checksum(newid())) as random_number
	from sys.columns c1
	join sys.columns c2
		on 1 = 1
)

insert into users
	(name, amount, country_code, created_at)
select
	concat('Name', rownumber) as name,
	left(random_number,4) as amount,
	case left(random_number,1)
		when 1 then 'HU'
		when 2 then 'US'
		when 3 then 'GB'
		when 4 then 'CH'
		when 5 then 'RU'
		when 6 then 'LU'
		when 7 then 'RO'
		when 8 then 'CZ'
		when 9 then 'SW'
		when 0 then 'DE'
		else null
	end as country_code,
	dateadd(day, -cast(left(random_number,3) as int), getdate()) as created_at
from cte
go

create index IX_name on users (name)
go

-- select * from users
-- go

-- Create stored procedures
-- SP1
create procedure usp_get_one_user_by_id
	@user_id int
as
	set nocount on;

	select
		id,
		name,
		amount,
		country_code,
		created_at
	from users
	where id = @user_id
go

-- SP2
create procedure usp_get_one_user_by_name
	@name nvarchar(255)
as
	set nocount on;

	select
		id,
		name,
		amount,
		country_code,
		created_at
	from users
	where name = @name
go

-- SP3  with function
create function udf_get_user_amount_by_name (
	@name nvarchar(255)
)
returns int
as
begin
	declare @amount int
	select @amount = amount
	from users
	where name > ''

	select @amount = amount
	from users
	where name = @name

	return @amount
end
go

create procedure usp_get_one_user_by_id_bad
	@user_id int
as
	set nocount on;

	select
		id,
		dbo.udf_get_user_amount_by_name(name) as name,
		amount,
		country_code,
		created_at
	from users
	where id = @user_id
go

Futtatjuk a fenti query-t, a where záradékot visszakommentezve, hogy csak a tempdb-ben futó sp-ket lássuk.
Nálam ez az eredmény jött ki (csak a postban tárgyalt fontosabb oszlopokat emeltem ki):

048_sp_cost_result

Tisztán látszik, hogy melyik eljárásokon kell optimalizálni. Aki akar kicsit tanulni, az a “usp_get_one_user_by_id_bad” eljárást elmezve bukkanthat új dolgokra 🙂