Azure synapse : Fastest way to load into Fact table

sakuraime 2,316 Reputation points
2020-12-03T06:42:29.127+00:00

what's the fastest way to load/insert in to Current fact table(NOT CREATE A NEW ONE) ?

– INSERT INTO … SELECT FROM …

– CREATE TABLE … AS SELECT …
– Polybase
– BCP
– OPENROWSET / BULK INSERT
– COPY INTO …

??

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,369 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. David Browne - msft 3,766 Reputation points
    2020-12-07T21:36:06.203+00:00

    BCP loads through the head node, so definitely not as scalable as COPY INTO.

    1 person found this answer helpful.
  2. HimanshuSinha-msft 19,376 Reputation points Microsoft Employee
    2020-12-04T02:28:08.997+00:00

    Hello @sakuraime ,
    Thanks for the ask and using thge forum .
    Since you mentioned that you do not want to create a new one , you should not consider the below will create the table .

    1. CREATE TABLE … AS SELECT …

    INSERT INTO … SELECT FROM … : This is a logged event as so it will be slower the below ones .
    BULK INSERT : This is minimally logged and so its will be slower then BCP .
    BCP should be the fastest among these .
    Polybase shoule be the fastest as polybase automatically parallelizes the data load process

    Thanks
    Himanshu