Good practice to call a complex stored procedure

Question

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

Answer 1

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 **

Answer 2

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.