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 ;).