Good practice to call a complex stored procedure

pmscorca 792 Reputation points
2024-03-11T17:36:20.76+00:00

Hi,

I need to read a Synapse table having more thousands of data rows and for each row I need to call a complex stored procedure.

I'm trying to implement and test an ADF pipeline with a For each activity, but the related execution seems very slow.

Could I implement a SQL cursor or another SQL method? I remember that a SQL cursor is not a very performant object.

Any suggests to me, please? Thanks

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,346 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Vahid Ghafarpour 17,700 Reputation points
    2024-03-11T19:04:15.1033333+00:00

    In Azure Data Factory, you can adjust settings such as the number of parallel executions to optimize performance for your specific scenario.

    https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-performance#performance-tuning-steps

    ** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **


  2. Smaran Thoomu 9,045 Reputation points Microsoft Vendor
    2024-03-13T10:58:24.37+00:00

    Hi @pmscorca ,

    Thank you for reaching out to the community forum with your query.

    Yes, increasing the degree of parallelism can improve the performance of your pipeline. However, it's important to note that the optimal degree of parallelism depends on various factors such as the size of the data, the complexity of the stored procedure, and the available resources.

    You can try experimenting with different degrees of parallelism to find the optimal setting for your pipeline. Additionally, you can also consider using the Azure Synapse Analytics dedicated SQL pool to execute the stored procedure, which can provide better performance for complex data processing tasks.

    Hope this helps. Do let us know if you any further queries.


    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.