'Snowflake to SQL Server data load performance

I am trying to improve performance of a SQL query used to move data from snowflake view to SQL Server table. Query uses linked server created using a ODBC driver to connect to Snowflake view from SQL Server using an OPENQUERY statement. Currently 50 million rows take around 2 hours to move the data. Please suggest if there is anything that can be done to improve performance.

Sample query we are using:

SELECT * 
INTO #temp
FROM OPENQUERY (SnowflakeServer, 'select * from "SnowflakeDB"."SnowflakeSchema"."mytable"')
sql-serversnowflake-cloud-data-platformsql-server-2016database-performancelinked-server


Solution 1:[1]

So this is not based on anything concrete, but a few ideas could be.

  1. Importing to a table with an columnstore index
  2. Doing the query using PolyBase
  3. Exporting your query to CSV and loading using BULK INSERT

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Nisd

Related Questions