Tutorial SQL

Przykłady, tutorial, gotowe rozwiązania sql.

Kategoria: MS SQL


Mierzenie wydajności zapytania na przykładzie NOT EXITS


Jakiś czas temu szperając po otchłani internetu natrafiłem na opinie, że powinno się używać EXISTS lub not EXISTS. Sam nie miałem zbyt wiele doświadczeń z EXIST, ponieważ korzystałem głównie z IN lub NOT IN temat zaciekawił mnie tym bardziej, że u innych znajomych użytkowników ms sql też rzadko spotykałem tą konstrukcje, postanowiłem to sprawdzić.



Jak zmierzyć wydajność zapytania w MS SQL Server?





Interesuje mnie przede wszystkim zużycie procesora i szybkość zapytania, wykorzystanie dysku.



SET STATISTICS IO ON




SET STATISTICS TIME ON




Uruchomienie w/w statystyk pozwoli nam na ich odczyt w zakładce message



Kolejna kwestia to fakt że server ms sql \\\'zapamiętuje\\\' to co kazaliśmy mu wyszukać, więc kolejne zapytanie wykona się zawsze szybciej a tego chcemy uniknąć. W tym celu użyjemy polceń:

CHECKPOINT 
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS





CHECKPOINT - wymusi zapisanie na dysku wszystkich stron które zostały zmodyfikowane w pamięci.



DBCC DROPCLEANBUFFERS - wyczyści cache danych, aby wykonanie zapytania wymusiło odczyt danych z dysku.



DBCC FREEPROCCACHE - usuwa plany zapytań.



Kolejnym krokiem będzie wygenerowanie tabel do testu, użyje do tego polecenia newid().



create table bigTable (lp1 char(4) not null, lp2 varchar(4) null)

declare @i int
Set @i=0
while @i <200000
begin
insert into bigTable
	SELECT left(newid(),4),left(newid(),4)
Set @i = @i+1
end
select * from bigTable

select top 500 * into smallTable from bigTable order by newid()




Test wydajności NOT IN MS SQL SERVER



Gdy już mamy dwie tabele możemy przystąpić do testów. Na pierwszy ogień wytypowałem NOT IN.
create table #time (ex int)
declare @i int
set @i =1
while @i <100
begin

	DECLARE @coldRunStart Datetime,
	@coldRunFinish Datetime

	CHECKPOINT 
	DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
	DBCC FREEPROCCACHE WITH NO_INFOMSGS

	SET @coldRunStart = getdate()

	SET STATISTICS IO ON
	SET STATISTICS TIME ON 

	 SELECT count(1)
		from
		bigtable where lp2 not in (select lp2 from smalltable)
		group by lp1

	SET STATISTICS TIME OFF  
	SET STATISTICS IO OFF

	SET @coldRunFinish = getdate()
	insert into #time
	SELECT DATEDIFF(ms, @coldRunStart, @coldRunFinish)

	set @i =@i+1
end

select min(ex),max(ex),avg(ex) from #time




Wynik min:1470, max:2356, avg:1717


Test wydajności NOT EXISTS w MS SQL SERVER



Teraz czas na NOT EXISTS


create table #time (ex int)
declare @i int
set @i =1
while @i <100
begin
	DECLARE @coldRunStart Datetime,
	@coldRunFinish Datetime

	CHECKPOINT 
	DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
	DBCC FREEPROCCACHE WITH NO_INFOMSGS

	SET @coldRunStart = getdate()

	SET STATISTICS IO ON
	SET STATISTICS TIME ON 

	 SELECT count(*)
		from
		bigtable a where not exists (select 1 from smalltable b where a.lp2=b.lp2)
		group by lp1

	SET STATISTICS TIME OFF  
	SET STATISTICS IO OFF

	SET @coldRunFinish = getdate()

	insert into #time
	SELECT DATEDIFF(ms, @coldRunStart, @coldRunFinish)

	set @i =@i+1
end

select min(ex),max(ex),avg(ex) from #time





Wynik min:936 ,max:1780, avg:1160



Wyniki mówią same za siebie NOT EXISTS jest szybsze ale zapytanie działa jedynie szybciej na kolumnie pozwalającej na wartości NULL, na not null NOT IN sprawuje się trochę lepiej ale to sami możecie przetestować.
powrót















SQL-KURSY.pl poleca:

Rozpocznij kolonizacje egzoplanety

Książki
ksikaksikaksikaksikaksika

Copyright 2010-2011 Mariusz Kujawski adres mariuszhk@op.pl

obob ob

Valid HTML 4.01 Transitional