SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Hugo Kornelis
Hugo Kornelis
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58981 Visits: 14164
Comments posted to this topic are about the item Stairway to Columnstore Indexes Level 5: Adding New Data To Columnstore Indexes


Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

TheComedian
TheComedian
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2539 Visits: 531
"if you have between 100,000 and 120,000 new rows per day, a single daily load would result in either lots of rows in the delta store, or lots of small rowgroups"

Good tip/remark. I thought of the same thing when I first read up on Delta Store and the min/max values for row groups.
curious_sqldba
curious_sqldba
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32934 Visits: 4024
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?
Hugo Kornelis
Hugo Kornelis
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58981 Visits: 14164
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



Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

curious_sqldba
curious_sqldba
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32934 Visits: 4024
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;

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search