RE: Stairway to Columnstore Indexes Level 5: Adding New Data To Columnstore Indexes

SSC-Dedicated

Points: 36494

July 6, 2018 at 1:37 pm

#1996626

Hugo Kornelis - Friday, July 6, 2018 12:51 PM
Hugo Kornelis - Friday, July 6, 2018 12:51 PM
curious_sqldba - Friday, July 6, 2018 10:50 AM
Excellent article @hugo. THANKYOU.
Question : You menationed "The standard T-SQL INSERT ... SELECT statement will also use the bulk load method if the total number of rows inserted is large enough, as shown by the results of listing 5-2. However, the T-SQL MERGE statement will always use the trickle insert method, regardless of the number of rows to be inserted.". Do you know how many records should it insert to use bulk load method? I couldn't find any documentation related to this. Is this new in 2016?
Thanks for the kind words! And thanks for your question, as it gives me the opportunity to clarify what I clearly had not described clear enough.
Two methods of inserting data exist:
* Trickle load: Add rows to open delta store rowgroup, using row mode storage. (Will eventually be compressed into columnstore storage when the rowgroup is full, e.g. has 1,048,576 rows)
* Bulk load: Added in a new rowgroup that is immediately compressed into columnstore storage after the insert finishes.
Bulk load is used when the following conditions apply:
1: Insert has at least 102,400 rows; AND
2: Insert is done by either any of the tools that use the Bulk Insert API (as listed in the article), or done by an INSERT INTO ... SELECT ... statement.
Trickle insert is used when the following conditions apply:
1. Insert has 101,399 rows; OR
2: Insert is done through a tool that does not suport Bulk Insert API; OR
3: Rows are added by a MERGE instead of an INSERT statemment (regardless of number of rows); OR
4: Rows are added as new due to an UPDATE statement (see http://www.sqlservercentral.com/articles/Stairway+Series/136174/), also regardless of number of rows.
So the short answer to your question is: "102,400" :satisfied:

Thanks for the kind words! And thanks for your question, as it gives me the opportunity to clarify what I clearly had not described clear enough.
Two methods of inserting data exist:
* Trickle load: Add rows to open delta store rowgroup, using row mode storage. (Will eventually be compressed into columnstore storage when the rowgroup is full, e.g. has 1,048,576 rows)
* Bulk load: Added in a new rowgroup that is immediately compressed into columnstore storage after the insert finishes.
Bulk load is used when the following conditions apply:
1: Insert has at least 102,400 rows; AND
2: Insert is done by either any of the tools that use the Bulk Insert API (as listed in the article), or done by an INSERT INTO ... SELECT ... statement.
Trickle insert is used when the following conditions apply:
1. Insert has 101,399 rows; OR
2: Insert is done through a tool that does not suport Bulk Insert API; OR
3: Rows are added by a MERGE instead of an INSERT statemment (regardless of number of rows); OR
4: Rows are added as new due to an UPDATE statement (see http://www.sqlservercentral.com/articles/Stairway+Series/136174/), also regardless of number of rows.
So the short answer to your question is: "102,400" :satisfied:

Appreciate for detail explanation. I have noticed when i load data using SSIS fast data load option it is way faster than my t-sql. Below is my sample t-sql. I would like to execute below t-sql just like how would execute through SSIS. I am not sure how can i confirm if below sql is actually inserting data using bulk load method. TIA
INSERT INTO Archive.dbo.PT ( col1 , col2 , col3 ) SELECT TOP 1000000 a.col1 , a.col2 , a.col3 FROM Edb.dbo.PT a JOIN #T1 b ON a.col1 = b.col1 AND a.col2 = b.col2;