ETL 过程和范围查找
4.60/5 (4投票s)
ETL 过程和范围查找
在典型的数据仓库项目中，有一项是任何开发人员都无法避免的步骤：合并和加载数据。通常，数据仓库数据库会合并来自不同数据源的数据，因此必须实施适当的数据清理和数据加载流程。
加载过程可能需要中间步骤，将数据加载到暂存区域表，执行数据质量流程，验证业务规则，验证迭代，最后加载事实表。最终的事实表加载过程主要执行查找操作以丰富数据。这些查找操作将允许在所有维度和事实记录之间关联数据，因为它们会将正确的维度代理键迁移到事实粒度中。
一旦查找操作构成了最终加载过程的大部分开销，我将尝试提供关于从有限维度记录丰富事实表的查找方法的相关证据。典型示例包括年龄范围、随时间变化（缓慢变化的维度 - SCDs）的地址、随时间变化的家庭成员（由日期界定）等。
在这种情况下，识别映射代理键所需的条件是 >= 和 <=，这会阻止数据库引擎使用唯一索引，并使搜索操作变得非常慢。这些查找通常称为范围查找。在本教程中，我将“玩转”一个工资范围维度。
本教程附带一个 T-SQL 脚本，该脚本生成超过三千二百万行介于 250 欧元和 19999 欧元之间的随机工资。读者只需创建一个空的 SQL Server 数据库，运行过程创建脚本并执行它（该脚本已在核心 I7 机器、16GB 内存和 512GB 固态硬盘上运行的 SQL Server 2017 数据库上进行了测试，如果在 Oracle 或其他关系数据库引擎（甚至 Microsoft Access）上创建类似的代码，也应该获得相同的结果）。该过程创建了正确的范围维度、相应的辅助查找表、随机工资值，并使用三种查找方法将一系列顺序插入语句插入事实表中。这些方法是：
- 使用 BETWEEN 子句针对维度的一个子查询；
- 使用辅助查找表的子查询；
- 针对辅助查找表的外部连接操作；
对于每个插入序列，记录以随机方式从基本原始数据表中提取（样本随着 2 的幂次增加）。最后，该过程显示了每个连接方法执行一次大规模插入语句到最终事实表中所需的毫秒数。要解释所使用的逻辑，请查看下面的模式。
在顶部的模式（传统查找序列）中，ETL 过程会处理原始数据，并使用有限值方法（范围查找）对所需维度执行查找。
如果 Tbl_Wage 中的工资值介于值 A 和值 B 之间，则从 Dim_Income_Range 中提取相应的代理键并将其添加到最终事实表中。这种方法会阻止典型的数据库引擎使用唯一索引扫描。
绕过这些情况的一种可能方法是创建一个仅在 ETL 丰富过程中使用的辅助查找表。在报表或数据探索级别，应使用相应的维度，因为范围描述（应出现在报表中）存在于其中。
话虽如此，我必须提到这种方法可能不适用于所有情况，它取决于最终辅助表的大小。所以，让我们看一个任意范围。
[1500 到 2000[
如果我们假设工资只有两位小数，那么范围边界之间的可能值是有限的，因此可以对其进行枚举（作为有限集合）。我的意思是，可以创建一个只有两个字段的单个表：绝对工资值（例如，45.01；45.02；45.03 等）以及分析维度中存在的相应代理键。
该表将能够容纳一个合适的唯一索引（群集、非群集、SQL Server 列存储、Oracle IOT 等），能够大大加快查找过程。与索引的第一个列是工资值相关。
使用这种方法时，开发人员必须做好妥协的准备。可能需要截断小数位数，因此必须平衡所需的精度与 ETL 速度的提高。诸如
- 我真的需要这么高的精度吗？
应该有助于减少辅助表中记录的最终数量。这种方法还有另一个限制，与顶部和底部范围有关。如果它们在一个边界处是无限的（-∞ 或 +∞），则过程必须假设一个非常高或非常低的值来定义下限和上限。辅助查找表无法容纳如此大量的行，主要是因为最终的预期收益可能会消失。因此，如果上限或下限非常高或非常低，可以在插入语句中添加一个适当的硬编码条件，该条件会检查工资是否落入这些范围并分配硬编码的代理键。
当然，这可以通过使用动态 SQL 来处理，但通常这些维度是高度静态的，所以硬编码一个或两个代理键值应该没有问题。
您应该考虑这篇帖子主要与 SQL 方法有关，有些工具甚至可以在并行多线程方法中优化查找。例如，IBM Datastage 使用数据集。它们是高度优化的数据结构，可以使查找过程运行得更快。此外，Microsoft SSDT（以前的 SSIS）允许开发人员使用 .net 框架的全部功能，因此可以使用内存数据结构（如哈希映射或字典）来大大改进范围查找。我在网上找到并阅读的与此主题相关的最有趣的帖子如下：
- https://kohera.be/blog/sql-server/ssis-boosting-range-lookups-script-component/
- https://www.oraylis.de/blog/combining-multiple-tables-with-valid-from-to-date-ranges-into-a-single-dimension
- https://www.ibm.com/support/knowledgecenter/en/SSZJPZ_9.1.0/com.ibm.swg.im.iis.ds.parjob.dev.doc/topics/c_deeref_Lookup_Stage.html
如果所需的查找次数非常多，开发人员确实应该考虑上述方法。使用这些方法，甚至可以开发适当的 .net 组件，将它们包含在 SSDT 项目中，甚至准备它们来处理数据子集（这将允许并行数据流）。但这篇帖子是关于 SQL 的，因为 ETL 工具可能不可用，而且如果您不需要离开数据库范围，没有什么比运行纯 SQL 语句更快了。
让我们看看基于附加脚本的最终结论。运行它之后，最终输出应该与下面的输出类似（有些行被排除）。最重要的是最终列表。在那里，您将能够观察每个样本的连接操作的时间。让我们将最终结果放入图表中，请查看后面的图片。
*************************************************************************************
Creating wage range dimension ...
*************************************************************************************
Loading wage range dimension ...
*************************************************************************************
Creating auxiliar lookup table from the base dimension (this step runs only once) ...
*************************************************************************************
Row 50000
...
Row 2000000
*************************************************************************************
Finished creating auxiliary lookup table ...
*************************************************************************************
Generating over than 30 million random wage records between
250 Eur and 19999 Eur (this step runs only once) ...
*************************************************************************************
Finished generating over than 30 million random wage records ...
*************************************************************************************
Starting sample chunks insertion ...
*************************************************************************************
| 时间（毫秒） | 子查询（between） | 子查询（辅助 Lkp） | 外部连接（辅助 Lkp） |
| 样本大小 [2] | 616 | | 10774 | | 230 | |
| 样本大小 [4] | 0 | | 7040 | | 223 | |
| 样本大小 [8] | 3 | | 7217 | | 227 | |
| 样本大小 [16] | 0 | | 6926 | | 227 | |
| 样本大小 [32] | 3 | | 7007 | | 230 | |
| 样本大小 [64] | 3 | | 7020 | | 230 | |
| 样本大小 [128] | 4 | | 6870 | | 230 | |
| 样本大小 [256] | 10 | | 7020 | | 233 | |
| 样本大小 [512] | 17 | | 6850 | | 233 | |
| 样本大小 [1024] | 30 | | 6880 | | 240 | |
| 样本大小 [2048] | 64 | | 6946 | | 254 | |
| 样本大小 [4096] | 123 | | 7257 | | 330 | |
| 样本大小 [8192] | 246 | | 7157 | | 317 | |
| 样本大小 [16384] | 470 | | 7190 | | 426 | |
| 样本大小 [32768] | 927 | | 7363 | | 604 | |
| 样本大小 [65536] | 1833 | | 7747 | | 910 | |
| 样本大小 [131072] | 3643 | | 8627 | | 1513 | |
| 样本大小 [262144] | 7273 | | 10820 | | 2577 | |
| 样本大小 [524288] | 14490 | | 14057 | | 4840 | |
| 样本大小 [1048576] | 29360 | | 21273 | | 9227 | |
| 样本大小 [2097152] | 58430 | | 35203 | | 17977 | |
| 样本大小 [4194304] | 93333 | | 52910 | | 28367 | |
| 样本大小 [8388608] | 93660 | | 52556 | | 27184 | |
| 样本大小 [16777216] | 93483 | | 53087 | | 27683 | |
| 样本大小 [33554432] | 93007 | | 53486 | | 26930 | |
查看上面的图表，随着样本量的增加，收益似乎很明显。在 250,000 行样本量之后，针对维度表的范围查找方法似乎开始呈指数级增长。另一方面，对索引表执行唯一扫描可以实现更快的查找操作。现在想象一下，您需要对每条记录执行二十次范围查找，如果我们假设收益约为 50%~70%，那么最终 ETL 过程应该会有巨大的改进。
再说一遍，像往常一样，这些都不是教条，每个项目都有其自己的要求，而且没有完美的解决方案适合所有情况。但一个经验法则似乎很明显：让数据库工作尽可能顺畅，避免蛮力方法，并利用数据库的特性。避免不必要的查找，了解您的基础数据。分析它，并尝试找到可以允许您使用唯一索引扫描而不是范围查找的查找模式。列连接、完整的数值列表枚举甚至完整的代理键值硬编码都可以极大地改进查找和加载时间。执行条件查找，例如，如果传入值为 null，则 CASE 语句内的简单 NVL 或 ISNULL 语句将阻止查找操作并分配默认的 UNKNOWN 或 EMPTY 值。一如既往，要富有创造力，并寻求其他开发者的意见和解决方案。
您可以执行的完整 T-SQL 脚本如下所示。
/*
This procedure performs insert sequential tests using three possible join operations
against a lookup dimension or an auxiliary lookup table
*/
create procedure [dbo].[sp_test_range_lookup]
as
declare @min_inf_limit decimal(18, 2)
declare @max_inf_limit decimal(18, 2)
declare @income_range_sk int
declare @c_base_rows cursor
declare @start_time datetime
declare @end_time datetime
declare @i int
declare @k int
declare @time_sub nvarchar(50)
declare @time_sub_aux nvarchar(50)
declare @time_sub_outer nvarchar(50)
declare @tmp_message nvarchar(500)
declare @u int
declare @t int
begin
/*test insert process*/
set nocount on
if object_id('dbo.Dim_Income_Range', 'U') IS NOT NULL
drop table dbo.Dim_Income_Range;
print '*************************************************************************************'
raiserror(N'Creating wage range dimension ...', 0, 1) with nowait;
create table dbo.dim_income_range(Income_Range_Sk [int] identity(1,1) NOT NULL,
Inf_Limit [decimal](18, 2) NOT NULL,
Sup_Limit [decimal](18, 2) NOT NULL,
Range_Desc [nvarchar](50) NOT NULL,
constraint PK_Dim_Income_Class primary key clustered (Income_Range_Sk asc)) on [primary]
print '*************************************************************************************'
raiserror(N'Loading wage range dimension ...', 0, 1) with nowait;
set identity_insert [dbo].[dim_income_range] on
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (1, cast(0.00 as decimal(18, 2)), cast(500.00 as decimal(18, 2)), N'[0 to 500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (2, cast(500.01 as decimal(18, 2)), cast(1000.00 as decimal(18, 2)), N'[500,01 to 1000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (3, cast(1000.01 as decimal(18, 2)), cast(1500.00 as decimal(18, 2)), N'[1000,01 to 1500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (4, cast(1500.01 as decimal(18, 2)), cast(2000.00 as decimal(18, 2)), N'[1500,01 to 2000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (5, cast(2000.01 as decimal(18, 2)), cast(2500.00 as decimal(18, 2)), N'[2000,01 to 2500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (6, cast(2500.01 as decimal(18, 2)), cast(3000.00 as decimal(18, 2)), N'[2500,01 to 3000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (7, cast(3000.01 as decimal(18, 2)), cast(3500.00 as decimal(18, 2)), N'[3000,01 to 3500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (8, cast(3500.01 as decimal(18, 2)), cast(4000.00 as decimal(18, 2)), N'[3500,01 to 4000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (9, cast(4000.01 as decimal(18, 2)), cast(4500.00 as decimal(18, 2)), N'[4000,01 to 4500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (10, cast(4500.01 as decimal(18, 2)), cast(5000.00 as decimal(18, 2)), N'[4500,01 to 5000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (11, cast(5000.01 as decimal(18, 2)), cast(5500.00 as decimal(18, 2)), N'[5000,01 to 5500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (12, cast(5500.01 as decimal(18, 2)), cast(6000.00 as decimal(18, 2)), N'[5500,01 to 6000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (13, cast(6000.01 as decimal(18, 2)), cast(6500.00 as decimal(18, 2)), N'[6000,01 to 6500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (14, cast(6500.01 as decimal(18, 2)), cast(7000.00 as decimal(18, 2)), N'[6500,01 to 7000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (15, cast(7000.01 as decimal(18, 2)), cast(7500.00 as decimal(18, 2)), N'[7000,01 to 7500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (16, cast(7500.01 as decimal(18, 2)), cast(8000.00 as decimal(18, 2)), N'[7500,01 to 8000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (17, cast(8000.01 as decimal(18, 2)), cast(8500.00 as decimal(18, 2)), N'[8000,01 to 8500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (18, cast(8500.01 as decimal(18, 2)), cast(9000.00 as decimal(18, 2)), N'[8500,01 to 9000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (19, cast(9000.01 as decimal(18, 2)), cast(9500.00 as decimal(18, 2)), N'[9000,01 to 9500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (20, cast(9500.01 as decimal(18, 2)), cast(10000.00 as decimal(18, 2)), N'[9500,01 to 10000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (21, cast(10000.01 as decimal(18, 2)), cast(10500.00 as decimal(18, 2)), N'[10000,01 to 10500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (22, cast(10500.01 as decimal(18, 2)), cast(11000.00 as decimal(18, 2)), N'[10500,01 to 11000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (23, cast(11000.01 as decimal(18, 2)), cast(11500.00 as decimal(18, 2)), N'[11000,01 to 11500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (24, cast(11500.01 as decimal(18, 2)), cast(12000.00 as decimal(18, 2)), N'[11500,01 to 12000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (25, cast(12000.01 as decimal(18, 2)), cast(12500.00 as decimal(18, 2)), N'[12000,01 to 12500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (26, cast(12500.01 as decimal(18, 2)), cast(13000.00 as decimal(18, 2)), N'[12500,01 to 13000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (27, cast(13000.01 as decimal(18, 2)), cast(13500.00 as decimal(18, 2)), N'[13000,01 to 13500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (28, cast(13500.01 as decimal(18, 2)), cast(14000.00 as decimal(18, 2)), N'[13500,01 to 14000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (29, cast(14000.01 as decimal(18, 2)), cast(14500.00 as decimal(18, 2)), N'[14000,01 to 14500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (30, cast(14500.01 as decimal(18, 2)), cast(15000.00 as decimal(18, 2)), N'[14500,01 to 15000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (31, cast(15000.01 as decimal(18, 2)), cast(15500.00 as decimal(18, 2)), N'[15000,01 to 15500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (32, cast(15500.01 as decimal(18, 2)), cast(16000.00 as decimal(18, 2)), N'[15500,01 to 16000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (33, cast(16000.01 as decimal(18, 2)), cast(16500.00 as decimal(18, 2)), N'[16000,01 to 16500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (34, cast(16500.01 as decimal(18, 2)), cast(17000.00 as decimal(18, 2)), N'[16500,01 to 17000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (35, cast(17000.01 as decimal(18, 2)), cast(17500.00 as decimal(18, 2)), N'[17000,01 to 17500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (36, cast(17500.01 as decimal(18, 2)), cast(18000.00 as decimal(18, 2)), N'[17500,01 to 18000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (37, cast(18000.01 as decimal(18, 2)), cast(18500.00 as decimal(18, 2)), N'[18000,01 to 18500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (38, cast(18500.01 as decimal(18, 2)), cast(19000.00 as decimal(18, 2)), N'[18500,01 to 19000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (39, cast(19000.01 as decimal(18, 2)), cast(19500.00 as decimal(18, 2)), N'[19000,01 to 19500[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (40, cast(19500.01 as decimal(18, 2)), cast(20000.00 as decimal(18, 2)), N'[19500,01 to 20000[')
insert [dbo].[dim_income_range] ([income_range_sk], [inf_limit], [sup_limit], [range_desc]) values (41, cast(20000.01 as decimal(18, 2)), cast(9999999999999999.00 as decimal(18, 2)), N'[20000,01 to +oo[')
set identity_insert [dbo].[dim_income_range] off
create unique nonclustered index ix0_dim_income_range on dbo.dim_income_range(inf_limit asc, sup_limit asc,income_range_sk asc);
print '*************************************************************************************';
raiserror(N'Creating auxiliar lookup table from the base dimension (this step runs only once) ...', 0, 1) with nowait;
print '*************************************************************************************';
if object_id('dbo.tbl_Income_Range_Lkp', 'U') IS NOT NULL
drop table dbo.tbl_income_range_lkp;
create table dbo.tbl_income_range_lkp
(income_range_sk [int] not null,
wage_value [decimal](18, 2) not null) on [primary];
select @min_inf_limit = min(inf_limit),
@max_inf_limit = max(inf_limit)
from dbo.dim_income_range
set @u = 0;
while @min_inf_limit <= @max_inf_limit
begin
select @income_range_sk = income_range_sk
from dim_income_range l
where @min_inf_limit between l.inf_limit and l.sup_limit
insert into tbl_income_range_lkp
values(@income_range_sk, @min_inf_limit)
select @min_inf_limit = @min_inf_limit + 0.01
set @u = @u + 1;
if @u % 50000 = 0
begin
set @tmp_message = N'Row ' + cast(@u as nvarchar(20));
raiserror(@tmp_message, 0, 1) with nowait;
end;
end;
create clustered columnstore index [ics_tbl_income_range_lkp] on [tbl_income_range_lkp] with (drop_existing = off, compression_delay = 0) on [primary];
print '*************************************************************************************'
raiserror(N'Finished creating auxiliar lookup table ...', 0, 1) with nowait;
if object_id('dbo.tbl_wage', 'U') IS NOT NULL
drop table dbo.tbl_wage;
print '*************************************************************************************'
raiserror(N'Generating over than 20 million random wage records between 250 Eur and 19999 Eur (this step runs only once) ...', 0, 1) with nowait;
/*create over than 12 million random wage records as the square root of 12.000.000 is ~3465*/
with tbl_a(id_seq) as (select 1 as id_seq),
tbl_b as (select id_seq
from tbl_a
union all
select id_seq + 1 as id_seq
from tbl_b
where id_seq < 5800),
tbl_cross_join as (select tbl1.id_seq
from tbl_b tbl1
cross join tbl_b tbl2)
select cast(row_number() over (order by id_seq) as int) as wage_id,
cast(round(rand(checksum(newid()))*(19999 - 250) + 250, 2) as decimal(18, 2)) as wage_value
into tbl_wage
from tbl_cross_join
option (maxrecursion 5800);
print '*************************************************************************************'
raiserror(N'Finished generating over than 12 million random wage records ...', 0, 1) with nowait;
if object_id('dbo.fac_wage', 'U') IS NOT NULL
drop table dbo.fac_wage;
create table dbo.fac_wage(
wage_sk [int] not null,
wage_value [decimal](18, 2) null,
income_range_sk [int] null
) on [primary];
print '*************************************************************************************';
print 'Starting sample chunks insertion ...';
print '*************************************************************************************';
print '';
print 'Time in milliseconds Sub Query (between) Sub Query (Aux Lkp) Outer Join (Aux Lkp)';
set @tmp_message = '---------------------------------------------------------------------------------------------';
raiserror(@tmp_message, 0, 1) with nowait;
set @i = 1;
set @u = 2;
set @k = @u;
while @k <= 35000000
begin
/*random mod approach*/
set @t = cast(floor(rand(checksum(newid())) * 10) as int);
/*test inserts using sub query*/
set @start_time = getdate();
truncate table fac_wage
insert into fac_wage
select o.wage_id as wage_sk,
o.wage_value,
(select t.Income_Range_Sk
from dim_income_range t
where o.wage_value between t.inf_limit and t.sup_limit) as income_range_sk
from (select top (@k) h.wage_id,
h.wage_value
from tbl_Wage h
where h.wage_id % 10 = @t) o;
set @end_time = getdate();
set @time_sub = cast(datediff(millisecond, @start_time ,@end_time) as nvarchar(50));
/*test inserts using sub query against aux table*/
set @start_time = getdate();
truncate table fac_wage
insert into fac_wage
select o.wage_id as wage_sk,
o.wage_value,
(select t.Income_Range_Sk
from tbl_income_range_lkp t
where t.wage_value = o.wage_value) as income_range_sk
from (select top (@k) h.wage_id,
h.wage_value
from tbl_Wage h
where h.wage_id % 10 = @t) o;
set @end_time = getdate();
set @time_sub_aux = cast(datediff(millisecond, @start_time ,@end_time) as nvarchar(50));
/*test inserts using outer join approach*/
set @start_time = getdate();
truncate table fac_wage
insert into fac_wage
select o.wage_id as wage_sk,
o.wage_value,
t.income_range_sk
from (select top (@k) h.wage_id,
h.wage_value
from tbl_Wage h
where h.wage_id % 10 = @t) o
left outer join tbl_income_range_lkp t
on o.wage_value = t.wage_value;
set @end_time = getdate();
set @time_sub_outer = cast(datediff(millisecond, @start_time ,@end_time) as nvarchar(50));
set @tmp_message = 'Sample Size [' + replicate(' ', 12 - len(cast(@k as nvarchar(50)))) + cast(@k as nvarchar(50)) + ']' +
replicate(' ', 20 - len(@time_sub)) + @time_sub + ' | ' +
replicate(' ', 20 - len(@time_sub_aux)) + @time_sub_aux + ' | ' +
replicate(' ', 20 - len(@time_sub_outer)) + @time_sub_outer + ' | ';
raiserror(@tmp_message, 0, 1) with nowait;
set @i = @i + 1;
set @k = power (@u, @i);
end
end