Are there any bandwidth limitations or throttling for Azure SQL Data Warehouse data extracts? Are there any connection string parameters that optimize how quickly we can retrieve data using a SELECT query?
From SSIS in a virtual machine in the same Azure area as SQL DW, if I run a SELECT * query to retrieve through millions of OLEDB rows with the default connection string (default packet size), I see that it uses a bandwidth of about 55 Mbps If I add Packet Size = 32767, I see that it uses a bandwidth of 125 Mbps. Is there any way to do this faster? Are there any other connection string settings I need to know about?
By the way, I was able to get about 500 Mbps of bandwidth coming from SQL DW if I run several excerpts in parallel. But I cannot split one request into several parallel requests. Sometimes I only need one query to retrieve data faster.
Of course, Polybase CETAS (CREATE EXTERNAL TABLE AS SELECT) is much more efficient when retrieving data. But this is not well suited for all extraction scenarios. For example, if I want to install Analysis Services on top of Azure SQL DW, I cannot really use the CETAS statement during cube processing, so Polybase does not help me there.
source
share