How can we improve Microsoft Azure SQL Data Warehouse ?

Performance with respect to dynamic SQL

In our solutions we load data from csv-files into SQL DW internal tables using polybase/external tables.

Per roll-out we face the challenge that the schema (columns) in the csv may differ for the same table. Therefore we implemented a logic to lookup the column name and data type from a dictionary table and we create the external and internal table schemes dynamically. As we have round about 500 tables with up to 20 columns per table automating this process is the only way to go.

The issue we face is that compared to an on premise SQL Server the dynamic creation of the schemes is very slow. We store the dynamic SQL DDL scripts in varchar variables. Is this performance issue already known? Is there a better way to dynamically/automatically create table schemes?

Best regards,
Alex

16 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Alex C. shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

2 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • Peter Gadsby commented  ·   ·  Flag as inappropriate

    Single row queries have always been poor in ADW, as far as I know there isn't a way around it. For your cache table did you distribute the table or replicate it? Replication writes are extremely slow and initial reads are slow too

Feedback and Knowledge Base