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?
Is this the looping execution time or the actual time of creating the tables?
Peter Gadsby commented
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
Chris Mendyk commented
Is it better now ?