在ETL过程中使用IN-MEMORY表作为登台表的模式 - 估计行数= 1个问题 [英] Pattern using IN-MEMORY tables as staging table during ETL process - Estimated row count = 1 issue

问题描述

您好，

我们正在使用IN-MEMORY表作为临时表

ETL过程按组织并行执行

数据操作后，我们将日期复制到CLUSTERED COLUMN STORE表（使用INSERT INTO SELECT）

问题是估计行数不稳定并设置为1.

除了效率低下的执行计划外，此问题导致错误写入列存储：所有数据都转到DELTA STORE而不是BULK插入到压缩的ROW GROUP中（实际行数每个进程计数~10​​M）

我明白错误估计错误统计数据，因为数据总是从登台表中删除

并且执行计划的重新生成在< g class =" gr_ gr_965 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep"数据-GR-ID =" 965" ID = QUOT; 965">错< / g取代;统计。

我尝试手动更新统计信息并重新编译，但结果有限且不稳定

我不知道该表何时具有存储过程的组织将重新编译 

OPTION（RECOMPILE）也无济于事。

所以我们正在寻找使用登台表的正确模式。

SQL 2017标准版CU 13

谢谢 

Yuri。

解决方案

< blockquote>

美好的一天，

请提供完整的XML执行计划

Hello,

We are using IN-MEMORY tables as a staging table

The ETL process is executed in parallel per Organization

After data manipulation, we copy date to CLUSTERED COLUMN STORE table (with INSERT INTO SELECT)

The issue is estimated row count that unstable and set to 1.

Except to inefficient execution plan, this issue results in the wrong write to column store: all data goes to DELTA STORE instead of BULK insert into compressed ROW GROUP (the actual rows count ~ 10M per process)

I understand that the wrong estimation due to wrong statistics since the data always deleted from the staging table

and regeneration of the execution plan run on <g class="gr_ gr_965 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" data-gr-id="965" id="965">wrong</g> statistics.

I tried update statistics manually and recompile but this had limited and unstable result

I cannot know when the table has the Organization that store procedure will recompile 

OPTION (RECOMPILE) does not help as well.

So we are looking for the correct pattern for work with staging tables.

SQL 2017 Standard edition CU 13

Thank you 

Yuri.

解决方案

Good day,

Please provide the full XML Execution Plan

这篇关于在ETL过程中使用IN-MEMORY表作为登台表的模式 - 估计行数= 1个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！