We are proud to share our inclusion in the 2024 Gartner Magic Quadrant for Data Integration Tools. We believe this recognition reflects the differentiated business outcomes CData delivers to our customers.
Get the Report →
Maximizing SQL Server Linked Server Efficiency with CData SQL Gateway
An SQL Server Linked Server is a feature in Microsoft SQL Server that allows the database engine to read data from external data sources and execute commands against remote databases. On the other hand, CData SQL Gateway enhances database connectivity by allowing SQL Server and other database systems to access remote SaaS, Big Data, and NoSQL data sources as if they were native relational databases.
CData SQL Gateway optimizes SQL Server Linked Servers by using TDS remoting, enabling direct, efficient ODBC communication with better query execution, reduced in-process execution, and improved performance.
Recent updates to the CData SQL Gateway have significantly enhanced its read and write speed performance. Users can now experience up to an 8x improvement in performance across all 270+ supported data sources.
This article compares the read and write performance of the Snowflake ODBC driver using two versions (Old [Build 8957] and New [Build 9165]) of the CData SQL Gateway remoting service in SQL Server with Linked Server when handling large datasets. We demonstrate how the latest version of CData SQL Gateway reads and writes large datasets into SQL Server a lot faster than the older version, with the help of varied performance tests.
The Data
Multiple datasets of varying sizes were used to test the read and write speeds of Snowflake ODBC drivers via the SQL Gateway remoting service.
| Schema | Table Name | Table Size | Table Number of Rows | Table Number of Columns |
|---|---|---|---|---|
| TPCH_SF100 | SUPPLIER | 65.6 MB | 1 million | 7 |
| TPCH_SF1 | ORDERS | 40.3 MB | 1.5 million | 9 |
| TPCH_SF10 | PARTSUPP | 356 MB | 8 million | 5 |
| TPCH_SF10 | ORDERS | 426 MB | 15 million | 9 |
Read and Write Performance using CData SQL Gateway
The primary objective of this investigation was to compare the performance of the SQL Gateway remoting service across two versions: SQL Gateway 2024 [Build 8957] (older) and SQL Gateway 2024 [Build 9165] (newer). This was achieved by executing the same set of queries using the Snowflake ODBC driver. We tested query performance on multiple Snowflake databases of varying sizes, row counts, and column structures, and the results are presented in the upcoming sections.
The tests are conducted on a system with the following configuration:
- Processor: 11th Gen Intel® Core™ i5-1135G7 @ 2.40GHz (8 CPUs), ~1.4GHz
- Memory: 16GB RAM
Performance Tests
1 Million rows
To demonstrate a reproducible comparison between the two versions of SQL Gateway, we used the Supplier table from TPCH_SF100 schema in Snowflake as the source and the SQL Server database as the destination. The remoting service was configured with SQL Server Linked Server for data reading and writing. Details of the Supplier table are provided below:
| Schema | Table Name | Table Size | Table Number of Rows | Table Number of Columns |
|---|---|---|---|---|
| TPCH_SF100 | SUPPLIER | 65.6 MB | 1 million | 7 |
Read Performance (SQL Gateway 2024 [Build 8957]):
The SELECT query execution time for 1 million rows using the older SQL Gateway version is 1 minute and 10 seconds.
Query used to read the data in SQL Server:
SELECT [S_SUPPKEY]
,[S_NAME]
,[S_ADDRESS]
,[S_NATIONKEY]
,[S_PHONE]
,[S_ACCTBAL]
,[S_COMMENT]
FROM [SNOWFLAKE].[CData.Snowflake.Sys].[Snowflake].[SUPPLIER]
GO
Read Performance (SQL Gateway 2024 [Build 9165]):
The SELECT query execution time for 1 million rows using the newer SQL Gateway version is 43 seconds. Please note that we have used the same query as for the older build to read data for testing.
| Overall Read Performance Comparison (both versions) | ||
|---|---|---|
| Build 8957 | Build 9165 | Performance Improvement |
| 1 minute 10 seconds | 43 seconds | +38.57% |
Write Performance (SQL Gateway 2024 [Build 8957]):
The INSERT query execution time for 1 million rows using the older SQL Gateway version is 1 minute and 22 seconds.
Query used to write the data in SQL Server:
USE SQLServerTables;
GO
CREATE TABLE dbo.Supplier (
Supplier_Key INT,
Supplier_Name NVARCHAR(50),
Supplier_Address NVARCHAR(200),
Supplier_NationKey INT,
Supplier_Phone NVARCHAR(50),
Supplier_AccountBal INT,
Supplier_Comment NVARCHAR(255),
);
GO
INSERT INTO [SQLServerTables].[dbo].[Supplier] ([Supplier_Key],[Supplier_Name],[Supplier_Address],[Supplier_NationKey],[Supplier_Phone],[Supplier_AccountBal],[Supplier_Comment])
SELECT [S_SUPPKEY]
,[S_NAME]
,[S_ADDRESS]
,[S_NATIONKEY]
,[S_PHONE]
,[S_ACCTBAL]
,[S_COMMENT]
FROM [SNOWFLAKE].[CData Snowflake Sys].[Snowflake].[SUPPLIER]
GOWrite Performance (SQL Gateway 2024 [Build 9165]):
The INSERT query execution time for 1 million rows using the newer SQL Gateway version is 51 seconds. Please note that we have used the same query to write data as for the older build for testing.
| Overall Write Performance Comparison (both versions) | ||
|---|---|---|
| Build 8957 | Build 9165 | Performance Improvement |
| 1 minute 22 seconds | 51 seconds | +37.80% |
As can be seen in the results, the newer version of CData SQL Gateway [Build 9165] regularly outperformed the older version of CData SQL Gateway [Build 8957], due to the recent improvements made.
1.5 Million rows
Next, we used the Orders table from TPCH_SF1 schema in Snowflake with 1.5 million rows of data as the source and the SQL Server database as the destination. The remoting service was configured with SQL Server Linked Server for data reading and writing. Details of the Orders table are provided below:
| Schema | Table Name | Table Size | Table Number of Rows | Table Number of Columns |
|---|---|---|---|---|
| TPCH_SF1 | ORDERS | 40.3 MB | 1.5 million | 9 |
Read Performance (SQL Gateway 2024 [Build 8957]):
The SELECT query execution time for 1.5 million rows using the older SQL Gateway version is 21 minute 22 seconds.
Query used to read the data in SQL Server:
SELECT [O_ORDERKEY]
,[O_CUSTKEY]
,[O_ORDERSTATUS]
,[O_TOTALPRICE]
,[O_ORDERDATE]
,[O_ORDERPRIORITY]
,[O_CLERK]
,[O_SHIPPRIORITY]
,[O_COMMENT]
FROM [SNOWFLAKE].[CData Snowflake Sys].[Snowflake].[ORDERS]
GORead Performance (SQL Gateway 2024 [Build 9165]):
The SELECT query execution time for 1.5 million rows using the newer SQL Gateway version is 4 minutes and 48 seconds. Please note that we have used the same query as for the older build to read data for testing.
| Overall Read Performance Comparison (both versions) | ||
|---|---|---|
| Build 8957 | Build 9165 | Performance Improvement |
| 21 minutes 22 seconds | 4 minutes 48 seconds | +77.55% |
Write Performance (SQL Gateway 2024 [Build 8957]):
The INSERT query execution time for 1.5 million rows using the older SQL Gateway version is 15 minutes and 55 seconds.
Query used to write the data in SQL Server:
USE SQLServerTables;
GO
CREATE TABLE dbo.Orders (
Order_Key INT,
Order_Customer_Key INT,
Order_Status NVARCHAR(100),
Order_Total_Price INT,
Order_Date date,
Order_Priority INT,
Order_Clerk NVARCHAR(200),
Order_Ship_Priority INT,
Order_Comment NVARCHAR(255),
);
GO
INSERT INTO [SQLServerTables].[dbo].[Orders] ([Order_Key],[Order_Customer_Key],[Order_Status],[Order_Total_Price],[Order_Date],[Order_Priority],[Order_Clerk],[Order_Ship_Priority],[Order_Comment])
SELECT [O_ORDERKEY]
,[O_CUSTKEY]
,[O_ORDERSTATUS]
,[O_TOTALPRICE]
,[O_ORDERDATE]
,[O_ORDERPRIORITY]
,[O_CLERK]
,[O_SHIPPRIORITY]
,[O_COMMENT]
FROM [SNOWFLAKE].[CData Snowflake Sys].[Snowflake].[ORDERS]
GOWrite Performance (SQL Gateway 2024 [Build 9165]):
The INSERT query execution time for 1.5 million rows using the newer SQL Gateway version is 13 minutes and 13 seconds. Please note that we have used the same query to write data as for the older build for testing.
| Overall Write Performance Comparison (both versions) | ||
|---|---|---|
| Build 8957 | Build 9165 | Performance Improvement |
| 15 minutes 55 seconds | 13 minutes 13 seconds | +16.96% |
8 Million rows
Next, we used the Parts Supply table from TPCH_SF10 schema in Snowflake with 8 million rows of data as the source and the SQL Server database as the destination. The remoting service was configured with SQL Server Linked Server for data reading and writing. Details of the Parts Supply table are provided below:
| Schema | Table Name | Table Size | Table Number of Rows | Table Number of Columns |
|---|---|---|---|---|
| TPCH_SF10 | PARTSUPP | 356 MB | 8 million | 5 |
Read Performance (SQL Gateway 2024 [Build 8957]):
The SELECT query execution time for 8 million rows using the older SQL Gateway version is 5 minutes and 39 seconds.
Query used to read the data in SQL Server:
SELECT [PS_PARTKEY]
,[PS_SUPPKEY]
,[PS_AVAILQTY]
,[PS_SUPPLYCOST]
,[PS_COMMENT]
FROM [SNOWFLAKE].[CData Snowflake Sys].[Snowflake].[PARTSUPP]
GORead Performance (SQL Gateway 2024 [Build 9165]):
The SELECT query execution time for 8 million rows using the newer SQL Gateway version is 4 minutes and 28 seconds. Please note that we have used the same query as for the older build to read data for testing.
| Overall Read Performance Comparison (both versions) | ||
|---|---|---|
| Build 8957 | Build 9165 | Performance Improvement |
| 5 minutes 39 seconds | 4 minutes 28 seconds | +20.94% |
Write Performance (SQL Gateway 2024 [Build 8957]):
The INSERT query execution time for 8 million rows using the older SQL Gateway version is 4 minutes and 52 seconds.
Query used to write the data in SQL Server:
USE SQLServerTables;
GO
CREATE TABLE dbo.PartsSupply (
Parts_Part_Key INT,
Parts_Supply_Key INT,
Parts_Supply_Available_Qty INT,
Parts_Supply_Cost INT,
Parts_Supply_Comments VARCHAR(255),
);
GO
INSERT INTO [SQLServerTables].[dbo].[Orders] ([Order_Key],[Order_Customer_Key],[Order_Status],[Order_Total_Price],[Order_Date],[Order_Priority],[Order_Clerk],[Order_Ship_Priority],[Order_Comment])
SELECT [PS_PARTKEY]
,[PS_SUPPKEY]
,[PS_AVAILQTY]
,[PS_SUPPLYCOST]
,[PS_COMMENT]
FROM [SNOWFLAKE].[CData Snowflake Sys].[Snowflake].[PARTSUPP]
GOWrite Performance (SQL Gateway 2024 [Build 9165]):
The INSERT query execution time for 8 million rows using the newer SQL Gateway version is 4 minutes and 40 seconds. Please note that we have used the same query to write data as for the older build for testing.
| Overall Write Performance Comparison (both versions) | ||
|---|---|---|
| Build 8957 | Build 9165 | Performance Improvement |
| 4 minutes 52 seconds | 4 minutes 40 seconds | +4.11% |
15 Million rows
Next, we used the Orders table from TPCH_SF10 schema in Snowflake with 15 million rows of data as the source and the SQL Server database as the destination. The remoting service was configured with SQL Server Linked Server for data reading and writing. Details of the Orders table are provided below:
| Schema | Table Name | Table Size | Table Number of Rows | Table Number of Columns |
|---|---|---|---|---|
| TPCH_SF10 | ORDERS | 426 MB | 15 million | 9 |
Read Performance (SQL Gateway 2024 [Build 8957]):
The SELECT query execution time for 15 million rows using the older SQL Gateway version is 6 minutes and 50 seconds.
Query used to read the data in SQL Server:
SELECT [O_ORDERKEY]
,[O_CUSTKEY]
,[O_ORDERSTATUS]
,[O_TOTALPRICE]
,[O_ORDERDATE]
,[O_ORDERPRIORITY]
,[O_CLERK]
,[O_SHIPPRIORITY]
,[O_COMMENT]
FROM [SNOWFLAKE].[CData Snowflake Sys].[Snowflake].[ORDERS]
GORead Performance (SQL Gateway 2024 [Build 9165]):
The SELECT query execution time for 15 million rows using the newer SQL Gateway version is 6 minutes and 22 seconds. Please note that we have used the same query as for the older build to read data for testing.
| Overall Read Performance Comparison (both versions) | ||
|---|---|---|
| Build 8957 | Build 9165 | Performance Improvement |
| 6 minutes 50 seconds | 6 minutes 22 seconds | +6.83% |
Write Performance (SQL Gateway 2024 [Build 8957]):
The INSERT query execution time for 15 million rows using the older SQL Gateway version is 9 minutes and 3 seconds.
Query used to write the data in SQL Server:
USE SQLServerTables;
GO
CREATE TABLE dbo.Orders (
Order_Key INT,
Order_Customer_Key INT,
Order_Status NVARCHAR(100),
Order_Total_Price INT,
Order_Date date,
Order_Priority INT,
Order_Clerk NVARCHAR(200),
Order_Ship_Priority INT,
Order_Comment NVARCHAR(255),
);
GO
INSERT INTO [SQLServerTables].[dbo].[Orders] ([Order_Key],[Order_Customer_Key],[Order_Status],[Order_Total_Price],[Order_Date],[Order_Priority],[Order_Clerk],[Order_Ship_Priority],[Order_Comment])
SELECT [O_ORDERKEY]
,[O_CUSTKEY]
,[O_ORDERSTATUS]
,[O_TOTALPRICE]
,[O_ORDERDATE]
,[O_ORDERPRIORITY]
,[O_CLERK]
,[O_SHIPPRIORITY]
,[O_COMMENT]
FROM [SNOWFLAKE].[CData Snowflake Sys].[Snowflake].[ORDERS]
GOWrite Performance (SQL Gateway 2024 [Build 9165]):
The INSERT query execution time for 15 million rows using the newer SQL Gateway version is 7 minutes and 27 seconds. Please note that we have used the same query to write data as for the older build for testing.
| Overall Write Performance Comparison (both versions) | ||
|---|---|---|
| Build 8957 | Build 9165 | Performance Improvement |
| 9 minutes 3 seconds | 7 minutes 27 seconds | +17.68% |
Conclusion
The newer version of CData SQL Gateway 2024 [Build 9165] offers better querying of large datasets over the older version [Build 8957], processing the largest dataset at least 15-20% faster by making better use of the available client resources.
Get started with a free 30-day trial of CData SQL Gateway ODBC Remoting Services. As always, let us know if you have any questions during your evaluation. Our world-class CData Support Team is always available to help!
