SQL Ninja gets Products from Group Tree CTE

SqlNinja Hi, today I want to show you comparison of 2 possible techniques for get data about all Products mapped to the Product Groups tree. I wrote, as usual, one T-SQL script that you can run each time you want to modify something to recalculate your results and check how they perform in your SQL Server. There are 2 nice things you can get from provided example. First is 2 ways of calculation that you can choose from. The second and more important thing is the way of testing from a server perspective. I sometimes see that people only check how long a particular query performs, but I want to show you also all the important information you can have, count, cpu, duration, reads, writes, and physical reads. Ok, so first, please look at the test of execution. In the end, I will show you its result.

use tempdb
go
-- clean dmv - 1st part to execute
dbcc dropcleanbuffers
dbcc freeproccache
go
-- test counter params - 2nd part to execute
declare @productc int = 250
declare @groupc int = 100
declare @mapc int = 10
set nocount on
-- schema
if object_id('Product') is not null
	drop table Product
create table Product (
	ID int identity(1,1) not null primary key clustered,
	Name nvarchar(100) not null
)
if object_id('ProductGroup') is not null
	drop table ProductGroup
create table ProductGroup (
	ID int identity(1,1) not null primary key clustered,
	ParentID int default 0,
	Name nvarchar(100) not null
)
if object_id('CustomerProductGroupMap') is not null
	drop table CustomerProductGroupMap
create table CustomerProductGroupMap (
	ID int identity(1,1) not null primary key clustered,
	CustomerID int not null default 0,
	ProductID int not null,
	ProductGroupID int not null
)
-- data model for lookup select
declare @i int = 0
while @i < @productc
begin
	insert into Product(Name)
	select 'Name' + str(cast(@i as varchar(40)), 10)
	insert into ProductGroup(Name)
	select 'Group' + str(cast(@i as varchar(40)), 10)
	declare @j int = 0
	while @j < @groupc
	begin
		insert into ProductGroup(ParentID, Name)
		select @i * @j, 'Group' + str(cast(@i*@j+@i+@j as varchar(40)), 10)
		declare @k int = 0
		while @k < @mapc
		begin
			insert into CustomerProductGroupMap(ProductID, ProductGroupID)
			select @i, @i*@j+@i+@j
			set @k = @k + 1
		end
		set @j = @j + 1
	end
	set @i = @i + 1
end
-- test 1 - select all
create table #r1(pgid int, pid int)
declare @pgid int
declare c cursor fast_forward for
select ID from ProductGroup where ParentID = 0
open c
fetch next from c into @pgid
while @@fetch_status = 0
begin
-- this can be table-value function that gets one root product group id
;with
g(ParentID,ID,n)
as
(
	select ParentID, ID, 0 n
	from ProductGroup
	where ID = @pgid
	union all
	select p.ParentID, p.ID, n + 1 n
	from g
	inner join ProductGroup p on g.ID = p.ParentID
	where
	n < @groupc -- recursion limit
)
insert into #r1(pgid, pid)
select distinct g.ID, m.ProductID
from g
inner join CustomerProductGroupMap m on ProductGroupID = g.ID
fetch next from c into @pgid
end
close c
deallocate c
drop table #r1
-- test 2 - select all
create table #r2(pgid int, pid int)
-- this can be table-value function that gets sets of root product group ids
;with
g(ParentID,ID,n)
as
(
	select ParentID, ID, 0 n
	from ProductGroup
	where ParentID = 0
	union all
	select p.ParentID, p.ID, n + 1 n
	from g
	inner join ProductGroup p on g.ID = p.ParentID
	where
	n < @groupc -- recursion limit
)
insert into #r2(pgid, pid)
select distinct g.ID, m.ProductID
from g
inner join CustomerProductGroupMap m on ProductGroupID = g.ID
drop table #r2
set nocount off
go
-- collect statistics - 3rd part to execute
; with s as (
select
substring (t.text,(qs.statement_start_offset/2) + 1,
((case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), t.text)) * 2
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) [query],
qs.execution_count [count],
qs.total_worker_time [cpu],
qs.last_elapsed_time [duration],
qs.total_logical_reads [reads],
qs.total_logical_writes [writes],
qs.total_physical_reads [p_reads]
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) t
)
select [query], [count], [cpu], [duration], [reads], [writes], [p_reads]
from s
-- without below where condition you get all queries ranking
where [query] like 'with%'
order by [cpu] desc, [count] desc, [duration] desc
go

And above code produces following results. Keep in mind that I replaced queries with test name. When you run it by yourself you get full text of your query.

query

count

cpu

duration

reads

writes

p_reads

1

test 1 cte

599

41223453

27458

4815359

56

3

2

test 2 cte

1

519892

607728

356428

156

366

You can now compare results. And you should know that on a big cloud stack of databases, the most important to you is CPU usage. If it is high, you can have latency issues for your web application. There is one more thing, tell me if all time #r1 and #r2 counts are the same? What happens when you put test1 cte and test2 cte in value-tables functions and use them both in query with duplicates? What happens with more levels in the product group tree? ;).

p ;).

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.