2

I have enabled the Export to data lake feature in F&O D365 and created external table in Serverless SQL pool database in Synapse to read the CSV. It's working fine since 6 month however now I am facing performance issue due huge amount of data and we are making join with multiple tables(approx. 10 tables) which has millions of data. To get the result it's taking around 30 seconds or sometime 40 seconds it's vary.

I am using logic app and function app to invoke the SQL queries.

My understanding was if it's Synapse serverless SQL Pool then automatically it'll handle the load and I will get result with 3-4 seconds but it's taking 40 seconds.

I checked all the best practices but it doesn't worked.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-serverless-sql-pool

Ajay
  • 247
  • 1
  • 5
  • 15
  • have you tried this - https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-statistics#statistics-in-serverless-sql-pool ? – Utkarsh Pal Jun 21 '22 at 15:41
  • @UtkarshPal-MT Thanks for response! Yes, I checked this but at this moment it support only single column stastistics for external tables. I am using approx 40 columns from 10 tables so should I create 40 stastistics ?? pls suggest. – Ajay Jun 21 '22 at 17:04
  • 1
    Focus on columns participating in WHERE, JOIN, GROUP BY, ORDER BY, and DISTINCT clauses. first try to make statistics of these columns and check the performance – Utkarsh Pal Jun 21 '22 at 17:10
  • @UtkarshPal-MT- I created statistics(38) for columns which are present in different clauses. It improved(2-4 and it vary) but not which I am expecting like I want to get the result in 4-5 seconds. – Ajay Jun 22 '22 at 05:25
  • for CSV there aren't much options in serverless sql pool as parquet is generally used in it. you can also try parallelization and partition of files. – Utkarsh Pal Jun 22 '22 at 06:52
  • @UtkarshPal-MT- Thanks and agree with you. Partitioning I have already done but what is mean parallelization here? do you mean need to create multiple external views for limited join and combine in one view? if so then I have tried but not getting expected result – Ajay Jun 22 '22 at 07:17
  • Facing issue in converting into parquet using ADF as we can't export direct parquet format from F&O – Ajay Jun 22 '22 at 07:18

2 Answers2

2

Finally, I moved the ADLS CSV data into Dedicated SQL Pool database using Synapse pipeline and data flow as we have complex join and above suggested approach did not worked. In dedicated SQL pool, we are able to increase the performance level as well as we can implement the materialized view, statistics and indexes which helped me to meet the API performance expectation.

In serverless, I was able to enhance some level performance if we have Parquet format file instead of CSV. So better I thought to move the data into dedicated SQL.

Thanks everyone for suggestion and time.

Ajay
  • 247
  • 1
  • 5
  • 15
0

The approach needs to be changed. You don't need to use additional services like Logic App and Functions, instead you can directly use Synapse Notebook for this.

You an use any of the approach you read data from Azure Data Lake Storage.

  1. OPENROWSET

The OPENROWSET(BULK...) function allows you to access files in Azure Storage. OPENROWSET function reads content of a remote data source (for example file) and returns the content as a set of rows. Within the serverless SQL pool resource, the OPENROWSET bulk rowset provider is accessed by calling the OPENROWSET function and specifying the BULK option.

Refer below example.

--OPENROWSET syntax for reading delimited text files
OPENROWSET  
( { BULK 'unstructured_data_path' , [DATA_SOURCE = <data source name>, ] 
    FORMAT = 'CSV'
    [ <bulk_options> ]
    [ , <reject_options> ] }  
)  
WITH ( {'column_name' 'column_type' [ 'column_ordinal' | 'json_path'] })  
[AS] table_alias(column_alias,...n)
  1. Read data as External Table

An external table points to data located in Hadoop, Azure Storage blob, or Azure Data Lake Storage. External tables are used to read data from files or write data to files in Azure Storage. With Synapse SQL, you can use external tables to read external data using dedicated SQL pool or serverless SQL pool.

Refer: External tables in dedicated SQL pool and serverless SQL pool

Utkarsh Pal
  • 4,079
  • 1
  • 5
  • 14