anchormodeling

Methods for the Row in Effect

Jan 22nd, 2022 (edited)
3,745
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ----------------------------- TEST PARAMETERS -------------------------------
  2. /*
  3.     Different methods to find the rows in effect by Lars Rönnbäck, 2022.
  4.     This script is in the Public Domain.
  5.  
  6.     You need to set up a database with a size of 10GB to hold ~100 million
  7.  
  8.     To run a suite of tests from 1k to 100M:
  9.     EXEC performance_test 1000;         -- 1k
  10.     EXEC performance_test 10000;        -- 10k
  11.     EXEC performance_test 100000;       -- 100k
  12.     EXEC performance_test 1000000;      -- 1M
  13.     EXEC performance_test 10000000;     -- 10M
  14.     EXEC performance_test 100000000;    -- 100M
  15.  
  16.     HISTORY:
  17.     2021-01-21  CREATED
  18.     2021-01-22  Added additional functions
  19.     2021-01-23  Rewrote as stored procedure + bug fixes and improvements
  20.                 Added clustered columnstore index test
  21. */
  22.  
  23.  
  24. create or alter proc performance_test (
  25.     -- approximately how many attribute rows you will have in the attribute table
  26.     @approximateAttributeRows int = 1000,
  27.     -- how many versions you will maximally have for an attribute value
  28.     @maxVersions int = 100,
  29.     -- 30 is a statistical "magic" number for statistical significance
  30.     @runs int = 30,
  31.     -- controls whether sp_updatestats is run after table creation
  32.     @stat bit = 1,
  33.     -- checks that output from all functions are identical
  34.     -- (this is very slow so run it only on a small test)
  35.     @verification bit = 0
  36. )
  37. as
  38. begin
  39. set nocount on;
  40. --=======================================================================--
  41. drop table if exists #func;
  42. create table #func (
  43.     id int identity(1, 1) not null primary key,
  44.     func varchar(555)
  45. );
  46. insert into #func (func)
  47. values
  48. ('p_anchor_cte_row_number'),
  49. ('p_anchor_first_value'),
  50. ('p_anchor_left_join_max'),
  51. ('p_anchor_outer_apply_top_1'),
  52. ('p_anchor_row_number'),
  53. ('p_anchor_top_1_subselect'),
  54. ('p_anchor_with_ties');
  55.  
  56. select * from #func;
  57.  
  58. -- truncate table [dbo].[result];
  59. if OBJECT_ID('[dbo].[result]') is null
  60. begin
  61.     CREATE TABLE [dbo].[result](
  62.         [func] [varchar](555) NOT NULL,
  63.         [median_duration_ms] [float] NOT NULL,
  64.         [average_duration_ms] [bigint] NOT NULL,
  65.         [deviation_duration_ms] [float] NOT NULL,
  66.         [minimum_duration_ms] [bigint] NOT NULL,
  67.         [maximum_duration_ms] [bigint] NOT NULL,
  68.         [approximateAttributeRows] [bigint] NOT NULL,
  69.         [maxVersions] [int] NOT NULL,
  70.         [runs] [int] NOT NULL,
  71.         [cold] [bit] NOT NULL,
  72.         [stat] [bit] NOT NULL,
  73.         [columnar] [bit] NOT NULL,
  74.         [version] varchar(555) NOT NULL,
  75.         [tested_at] datetime NOT NULL default GETDATE()
  76.     );
  77. end
  78.  
  79. ---------------------------- TEST BENCH SETUP -------------------------------
  80. drop table if exists attribute_one;
  81. drop table if exists attribute_two;
  82. drop table if exists anchor;
  83.  
  84. create table anchor (
  85.     id int not null primary key
  86. );
  87.  
  88. with idGen as (
  89.     select 1 as id
  90.     union all
  91.     select id + 1
  92.     from idGen
  93.     where id < round(1E0 * @approximateAttributeRows / 25, 0)
  94. )
  95. insert into anchor (id)
  96. select id
  97. from idGen
  98. option (MAXRECURSION 0);
  99.  
  100. drop table if exists #version;
  101. select top (@maxVersions) *
  102. into #version
  103. from anchor
  104. order by id asc;
  105.  
  106. alter table #version add primary key (id);
  107.  
  108. create table attribute_one (
  109.     id int not null foreign key references anchor(id),
  110.     val varchar(max),
  111.     since date not null,
  112.     primary key (
  113.         id asc,
  114.         since desc
  115.     )
  116. );
  117.  
  118. insert into attribute_one (id, val, since)
  119. select
  120.     a.id,
  121.     'ID: ' + cast(a.id as varchar(10)) + ' VERSION: ' + cast(v.id as varchar(10)),
  122.     dateadd(day, v.id - a.id % @maxVersions, '2020-01-01')
  123. from anchor a
  124. join #version v
  125. on v.id <= rand(checksum(a.id)) * (a.id % @maxVersions);
  126.  
  127. create table attribute_two (
  128.     id int not null foreign key references anchor(id),
  129.     val varchar(max),
  130.     since date not null,
  131.     unique (
  132.         id asc,
  133.         since desc
  134.     )
  135. );
  136. create clustered columnstore index cc_attribute_two on attribute_two;
  137.  
  138. insert into attribute_two (id, val, since)
  139. select
  140.     a.id,
  141.     'ID: ' + cast(a.id as varchar(10)) + ' VERSION: ' + cast(v.id as varchar(10)),
  142.     dateadd(day, v.id - a.id % @maxVersions, '2020-01-01')
  143. from anchor a
  144. join #version v
  145. on v.id <= rand(checksum(a.id)) * (a.id % @maxVersions);
  146.  
  147. --------------------------------- TESTING ------------------------------------
  148. declare @at char(10) = '2019-12-01';
  149.  
  150. drop table if exists #timing;
  151. create table #timing (
  152.     func varchar(555) not null,
  153.     cold bit not null,
  154.     columnar bit not null,
  155.     iteration int not null,
  156.     start_time datetime2(7) not null,
  157.     end_time datetime2(7) not null,
  158.     duration_ms as datediff(ms, start_time, end_time),
  159.     primary key (
  160.         func,
  161.         cold,
  162.         columnar,
  163.         iteration
  164.     )
  165. );
  166.  
  167. drop table if exists #result;
  168. select top 0 id, val_one, since_one
  169. into #result
  170. from p_anchor_row_number(@at);
  171.  
  172.  
  173. if (@verification = 1)
  174. begin
  175.     drop table if exists #failed_verification;
  176.     select top 0
  177.         a.id,
  178.         a1.val as val_one,
  179.         a1.since as since_one,
  180.         a2.val as val_two,
  181.         a2.since as since_two,
  182.         CAST(null as varchar(555)) as failing_functions
  183.     into #failed_verification
  184.     from anchor a
  185.     join attribute_one a1
  186.     on a1.id = a.id
  187.     join attribute_two a2
  188.     on a2.id = a.id;
  189.  
  190.     declare @VERIFICATION_SQL varchar(max);
  191.     set @VERIFICATION_SQL = (
  192.         select '
  193.             insert into #failed_verification
  194.             select *, ''' + f1.func + ' x ' + f2.func + '''
  195.             from (
  196.                 select * from ' + f1.func + '(DEFAULT)
  197.                 except
  198.                 select * from ' + f2.func + '(DEFAULT)
  199.             ) x
  200.         ' as [text()]
  201.         from #func f1
  202.         cross join #func f2
  203.         where f1.id <> f2.id
  204.         for xml path (''), type
  205.     ).value('.', 'varchar(max)');
  206.  
  207.     EXEC(@VERIFICATION_SQL);
  208.  
  209.     select * from #failed_verification;
  210. end
  211.  
  212. select (
  213.     select '
  214.        SET SHOWPLAN_XML ON;;
  215.         GO
  216.         ' + (
  217.             select '
  218.                 select id, val_one, since_one
  219.                 from ' + f.func + '(''' + @at + ''');
  220.                 select id, val_two, since_two
  221.                 from ' + f.func + '(''' + @at + ''');
  222.             ' as [text()]
  223.             from #func f
  224.             for xml path (''), type
  225.         ).value('.', 'varchar(max)') + '
  226.         GO
  227.         SET SHOWPLAN_XML OFF;
  228.         GO
  229.     ' for xml path(''), type
  230. ) as [Run the following separately if you want to collect execution plans];
  231.  
  232.  
  233. declare @id int;
  234. declare @func varchar(555);
  235. declare @SQL varchar(max);
  236. declare @iter int;
  237. declare @start datetime2(7);
  238. declare @end datetime2(7);
  239. declare @db_id int;
  240.  
  241. -------------- COLD TEST (clustered index) ------------------
  242. set @id = (select min(id) from #func);
  243.  
  244. while(@id is not null)
  245. begin
  246.  
  247.     set @func = (
  248.         select func
  249.         from #func
  250.         where id = @id
  251.     );
  252.  
  253.     set @SQL = '
  254.     select id, val_one, since_one
  255.     into #result
  256.     from ' + @func + '(''' + @at + ''')
  257.     OPTION (OPTIMIZE FOR UNKNOWN);
  258.     ';
  259.  
  260.     set @iter = 0;
  261.  
  262.     while (@iter < @runs)
  263.     begin
  264.         truncate table #result;
  265.  
  266.         -- cold
  267.         CHECKPOINT;
  268.         DBCC DROPCLEANBUFFERS;
  269.         DBCC FREESYSTEMCACHE ('ALL');
  270.         DBCC FREEPROCCACHE WITH NO_INFOMSGS;
  271.         SET @db_id = DB_ID();
  272.         DBCC FLUSHPROCINDB (@db_id);
  273.  
  274.         set @start = SYSDATETIME()
  275.         EXEC(@SQL);
  276.         set @end = SYSDATETIME();
  277.        
  278.         insert into #timing (
  279.             func,
  280.             cold,
  281.             columnar,
  282.             iteration,
  283.             start_time,
  284.             end_time
  285.         )
  286.         values (
  287.             @func,
  288.             1,
  289.             0,
  290.             @iter,
  291.             @start,
  292.             @end
  293.         );
  294.  
  295.         set @iter = @iter + 1;
  296.     end
  297.  
  298.     set @id = (
  299.         select id
  300.         from #func
  301.         where id = @id + 1
  302.     );
  303.  
  304. end
  305.  
  306. -------------- COLD TEST (columnstore) ------------------
  307. set @id = (select min(id) from #func);
  308.  
  309. while(@id is not null)
  310. begin
  311.  
  312.     set @func = (
  313.         select func
  314.         from #func
  315.         where id = @id
  316.     );
  317.  
  318.     set @SQL = '
  319.     select id, val_two, since_two
  320.     into #result
  321.     from ' + @func + '(''' + @at + ''')
  322.     OPTION (OPTIMIZE FOR UNKNOWN);
  323.     ';
  324.  
  325.     set @iter = 0;
  326.  
  327.     while (@iter < @runs)
  328.     begin
  329.         truncate table #result;
  330.  
  331.         -- cold
  332.         CHECKPOINT;
  333.         DBCC DROPCLEANBUFFERS;
  334.         DBCC FREESYSTEMCACHE ('ALL');
  335.         DBCC FREEPROCCACHE WITH NO_INFOMSGS;
  336.         SET @db_id = DB_ID();
  337.         DBCC FLUSHPROCINDB (@db_id);
  338.  
  339.         set @start = SYSDATETIME()
  340.         EXEC(@SQL);
  341.         set @end = SYSDATETIME();
  342.        
  343.         insert into #timing (
  344.             func,
  345.             cold,
  346.             columnar,
  347.             iteration,
  348.             start_time,
  349.             end_time
  350.         )
  351.         values (
  352.             @func,
  353.             1,
  354.             1,
  355.             @iter,
  356.             @start,
  357.             @end
  358.         );
  359.  
  360.         set @iter = @iter + 1;
  361.     end
  362.  
  363.     set @id = (
  364.         select id
  365.         from #func
  366.         where id = @id + 1
  367.     );
  368.  
  369. end
  370.  
  371. -- update statistics before warm tests
  372. if @stat = 1
  373. begin
  374.     EXEC sp_updatestats
  375. end
  376.  
  377. -------------- WARM TEST (clustered index) ------------------
  378. set @id = (select min(id) from #func);
  379.  
  380. while(@id is not null)
  381. begin
  382.  
  383.     set @func = (
  384.         select func
  385.         from #func
  386.         where id = @id
  387.     );
  388.  
  389.     set @SQL = '
  390.     select id, val_one, since_one
  391.     into #result
  392.     from ' + @func + '(''' + @at + ''');
  393.     ';
  394.  
  395.     set @iter = 0;
  396.  
  397.     while (@iter < @runs)
  398.     begin
  399.         truncate table #result;
  400.  
  401.         -- warm
  402.         set @start = SYSDATETIME()
  403.         EXEC(@SQL);
  404.         set @end = SYSDATETIME();
  405.  
  406.         insert into #timing (
  407.             func,
  408.             cold,
  409.             columnar,
  410.             iteration,
  411.             start_time,
  412.             end_time
  413.         )
  414.         values (
  415.             @func,
  416.             0,
  417.             0,
  418.             @iter,
  419.             @start,
  420.             @end
  421.         );
  422.  
  423.         set @iter = @iter + 1;
  424.     end
  425.  
  426.     set @id = (
  427.         select id
  428.         from #func
  429.         where id = @id + 1
  430.     );
  431.  
  432. end
  433.  
  434. -------------- WARM TEST (columnstore) ------------------
  435. set @id = (select min(id) from #func);
  436.  
  437. while(@id is not null)
  438. begin
  439.  
  440.     set @func = (
  441.         select func
  442.         from #func
  443.         where id = @id
  444.     );
  445.  
  446.     set @SQL = '
  447.     select id, val_two, since_two
  448.     into #result
  449.     from ' + @func + '(''' + @at + ''');
  450.     ';
  451.  
  452.     set @iter = 0;
  453.  
  454.     while (@iter < @runs)
  455.     begin
  456.         truncate table #result;
  457.  
  458.         -- warm
  459.         set @start = SYSDATETIME()
  460.         EXEC(@SQL);
  461.         set @end = SYSDATETIME();
  462.  
  463.         insert into #timing (
  464.             func,
  465.             cold,
  466.             columnar,
  467.             iteration,
  468.             start_time,
  469.             end_time
  470.         )
  471.         values (
  472.             @func,
  473.             0,
  474.             1,
  475.             @iter,
  476.             @start,
  477.             @end
  478.         );
  479.  
  480.         set @iter = @iter + 1;
  481.     end
  482.  
  483.     set @id = (
  484.         select id
  485.         from #func
  486.         where id = @id + 1
  487.     );
  488.  
  489. end
  490.  
  491. insert into result (
  492.     [func],
  493.     [median_duration_ms],
  494.     [average_duration_ms],
  495.     [deviation_duration_ms],
  496.     [minimum_duration_ms],
  497.     [maximum_duration_ms],
  498.     [approximateAttributeRows],
  499.     [maxVersions],
  500.     [runs],
  501.     [cold],
  502.     [columnar],
  503.     [stat],
  504.     [version]
  505. )
  506. select
  507.     [func],
  508.     [median_duration_ms],
  509.     [average_duration_ms],
  510.     [deviation_duration_ms],
  511.     [minimum_duration_ms],
  512.     [maximum_duration_ms],
  513.     @approximateAttributeRows,
  514.     @maxVersions,
  515.     @runs,
  516.     [cold],
  517.     [columnar],
  518.     @stat,
  519.     @@VERSION
  520. from (
  521.     select
  522.         func,
  523.         cold,
  524.         columnar,
  525.         MAX(median_duration_ms) as median_duration_ms,
  526.         AVG(duration_ms) as average_duration_ms,
  527.         STDEVP(duration_ms) as deviation_duration_ms,
  528.         MIN(duration_ms) as minimum_duration_ms,
  529.         MAX(duration_ms) as maximum_duration_ms
  530.     from (
  531.         select
  532.             *,
  533.             median_duration_ms = PERCENTILE_CONT(0.5)
  534.                 within group (order by duration_ms)
  535.                 over (partition by func, cold, columnar)
  536.         from
  537.             #timing
  538.     ) t
  539.     group by func, cold, columnar
  540. ) f
  541. order by 2;
  542.  
  543. select * from result;
  544. end
  545. go
  546.  
  547. ------------------------------------- FUNCTIONS ------------------------------------------
  548. /* Note that some functions need to use a group by "trick" to retain table elimination */
  549.  
  550. drop function if exists p_anchor_row_number;
  551. go
  552. create function p_anchor_row_number (
  553.     @at date = '9999-12-31'
  554. )
  555. returns table as return
  556. select
  557.     a.*,
  558.     a1.val as val_one,
  559.     a1.since as since_one,
  560.     a2.val as val_two,
  561.     a2.since as since_two
  562. from anchor a
  563. left join (
  564.     select
  565.         id,
  566.         max(val) as val,
  567.         max(since) as since
  568.     from (
  569.         select  
  570.             *,
  571.             row_number() over (partition by id order by since desc) as recency
  572.         from
  573.             attribute_one
  574.         where
  575.             since <= @at
  576.     ) one
  577.     where one.recency = 1
  578.     group by (id)
  579. ) a1
  580. on a1.id = a.id
  581. left join (
  582.     select
  583.         id,
  584.         max(val) as val,
  585.         max(since) as since
  586.     from (
  587.         select  
  588.             *,
  589.             row_number() over (partition by id order by since desc) as recency
  590.         from
  591.             attribute_two
  592.         where
  593.             since <= @at
  594.     ) two
  595.     where two.recency = 1
  596.     group by id
  597. ) a2
  598. on a2.id = a.id;
  599. go
  600.  
  601. --select top 100 * from p_anchor_row_number(DEFAULT);
  602. --select top 100 id from p_anchor_row_number(DEFAULT);
  603.  
  604. drop function if exists p_anchor_first_value;
  605. go
  606. create function p_anchor_first_value (
  607.     @at date = '9999-12-31'
  608. )
  609. returns table as return
  610. select
  611.     a.*,
  612.     a1.val as val_one,
  613.     a1.since as since_one,
  614.     a2.val as val_two,
  615.     a2.since as since_two
  616. from anchor a
  617. outer apply (
  618.     select top 1
  619.         first_value(val) over (partition by id order by since desc) as val,
  620.         first_value(since) over (partition by id order by since desc) as since
  621.     from attribute_one
  622.     where id = a.id and since <= @at
  623. ) a1
  624. outer apply (
  625.     select top 1
  626.         first_value(val) over (partition by id order by since desc) as val,
  627.         first_value(since) over (partition by id order by since desc) as since
  628.     from attribute_two
  629.     where id = a.id and since <= @at
  630. ) a2
  631. go
  632.  
  633. --select top 100 * from p_anchor_first_value(DEFAULT);
  634. --select top 100 id from p_anchor_first_value(DEFAULT);
  635.  
  636. drop function if exists p_anchor_top_1_subselect;
  637. go
  638. create function p_anchor_top_1_subselect (
  639.     @at date = '9999-12-31'
  640. )
  641. returns table as return
  642. select
  643.     a.*,
  644.     a1.val as val_one,
  645.     a1.since as since_one,
  646.     a2.val as val_two,
  647.     a2.since as since_two
  648. from anchor a
  649. left join attribute_one a1
  650. on a1.id = a.id
  651. and a1.since = (
  652.     select top 1
  653.         sub.since
  654.     from attribute_one sub
  655.     where sub.id = a.id
  656.     and sub.since <= @at
  657. )
  658. left join attribute_two a2
  659. on a2.id = a.id
  660. and a2.since = (
  661.     select top 1
  662.         sub.since
  663.     from attribute_two sub
  664.     where sub.id = a.id
  665.     and sub.since <= @at
  666. )
  667. go
  668.  
  669. --select top 100 * from p_anchor_top_1_subselect(DEFAULT);
  670. --select top 100 id from p_anchor_top_1_subselect(DEFAULT);
  671.  
  672. drop function if exists p_anchor_outer_apply_top_1;
  673. go
  674. create function p_anchor_outer_apply_top_1 (
  675.     @at date = '9999-12-31'
  676. )
  677. returns table as return
  678. select
  679.     a.*,
  680.     a1.val as val_one,
  681.     a1.since as since_one,
  682.     a2.val as val_two,
  683.     a2.since as since_two
  684. from anchor a
  685. outer apply (
  686.     select top 1
  687.         *
  688.     from attribute_one sub
  689.     where sub.id = a.id
  690.     and sub.since <= @at
  691.     order by sub.since desc
  692. ) a1
  693. outer apply (
  694.     select top 1
  695.         *
  696.     from attribute_two sub
  697.     where sub.id = a.id
  698.     and sub.since <= @at
  699.     order by sub.since desc
  700. ) a2
  701. go
  702.  
  703. --select top 100 * from p_anchor_outer_apply_top_1(DEFAULT);
  704. --select top 100 id from p_anchor_outer_apply_top_1(DEFAULT);
  705.  
  706. drop function if exists p_anchor_with_ties;
  707. go
  708. create function p_anchor_with_ties (
  709.     @at date = '9999-12-31'
  710. )
  711. returns table as return
  712. select
  713.     a.*,
  714.     a1.val as val_one,
  715.     a1.since as since_one,
  716.     a2.val as val_two,
  717.     a2.since as since_two
  718. from anchor a
  719. left join (
  720.     select
  721.         id,
  722.         max(val) as val,
  723.         max(since) as since
  724.     from (
  725.         select top 1 with ties
  726.             *
  727.         from attribute_one
  728.         where since <= @at
  729.         order by row_number() over (partition by id order by since desc)
  730.     ) one
  731.     group by id
  732. ) a1
  733. on a1.id = a.id
  734. left join (
  735.     select
  736.         id,
  737.         max(val) as val,
  738.         max(since) as since
  739.     from (
  740.         select top 1 with ties
  741.             *
  742.         from attribute_two
  743.         where since <= @at
  744.         order by row_number() over (partition by id order by since desc)
  745.     ) two
  746.     group by id
  747. ) a2
  748. on a2.id = a.id
  749. go
  750.  
  751. --select top 100 * from p_anchor_with_ties(DEFAULT);
  752. --select top 100 id from p_anchor_with_ties(DEFAULT);
  753.  
  754. drop function if exists p_anchor_cte_row_number;
  755. go
  756. create function p_anchor_cte_row_number (
  757.     @at date = '9999-12-31'
  758. )
  759. returns table as return
  760. with one as (
  761.     select
  762.         id,
  763.         max(val) as val,
  764.         max(since) as since
  765.     from (
  766.         select  
  767.             *,
  768.             row_number() over (partition by id order by since desc) as recency
  769.         from
  770.             attribute_one
  771.         where
  772.             since <= @at
  773.     ) one
  774.     where one.recency = 1
  775.     group by (id)
  776. ),
  777. two as (
  778.     select
  779.         id,
  780.         max(val) as val,
  781.         max(since) as since
  782.     from (
  783.         select  
  784.             *,
  785.             row_number() over (partition by id order by since desc) as recency
  786.         from
  787.             attribute_two
  788.         where
  789.             since <= @at
  790.     ) two
  791.     where two.recency = 1
  792.     group by id
  793. )
  794. select
  795.     a.*,
  796.     a1.val as val_one,
  797.     a1.since as since_one,
  798.     a2.val as val_two,
  799.     a2.since as since_two
  800. from anchor a
  801. left join one a1
  802. on a1.id = a.id
  803. left join two a2
  804. on a2.id = a.id
  805. go
  806.  
  807. --select top 100 * from p_anchor_cte_row_number(DEFAULT);
  808. --select top 100 id from p_anchor_cte_row_number(DEFAULT);
  809.  
  810. drop function if exists p_anchor_left_join_max;
  811. go
  812. create function p_anchor_left_join_max (
  813.     @at date = '9999-12-31'
  814. )
  815. returns table as return
  816. select
  817.     a.*,
  818.     a1.val as val_one,
  819.     a1.since as since_one,
  820.     a2.val as val_two,
  821.     a2.since as since_two
  822. from anchor a
  823. left join (
  824.     select
  825.         id,
  826.         MAX(since) as since
  827.     from attribute_one
  828.     where since <= @at
  829.     group by id
  830. ) one_max
  831. on one_max.id = a.id
  832. left join attribute_one a1
  833. on a1.id = one_max.id
  834. and a1.since = one_max.since
  835. left join (
  836.     select
  837.         id,
  838.         MAX(since) as since
  839.     from attribute_two
  840.     where since <= @at
  841.     group by id
  842. ) two_max
  843. on two_max.id = a.id
  844. left join attribute_two a2
  845. on a2.id = one_max.id
  846. and a2.since = one_max.since
  847. go
  848.  
  849. --select top 100 * from p_anchor_left_join_max(DEFAULT);
  850. --select top 100 id from p_anchor_left_join_max(DEFAULT);
  851.  
RAW Paste Data Copied