ADF Stored procedure activity vs Synapse SQL pool stored procedure activity

Question

Hi,

which are the differencies between the Stored procedure activity of Data Factory and the SQL pool stored procedure activity of Synapse SQL pool, specially in performance terms?

I'd like to know them because I'm working with a Synapse dedicated SQL pool.

Thanks

Answer 1

Hi @pmscorca

Welcome to Microsoft Q&A platform and thanks for posting your question here.

The ADF Stored Procedure Activity can call stored procedures in various data stores like Azure SQL Database, Synapse SQL pool, or even on-premises SQL Server databases. It requires a linked service specifying the connection details to the database and is suitable for executing stored procedures within ADF. 

The Synapse SQL Pool Stored Procedure Activity is designed to execute stored procedures within a dedicated Synapse SQL pool. It interacts directly with the co-located SQL pool within the Synapse Workspace, which means that a linked service is not required. This makes it efficient for invoking stored procedures native to your Synapse SQL pool. It also takes advantage of optimized communication within the workspace, which can potentially improve its performance.

Here's a detailed explanation of the differences between the Stored procedure activity of Data Factory and the SQL pool stored procedure activity of Synapse SQL pool in terms of performance.

Data Movement:

• The Stored Procedure activity of Data Factory requires data to be moved from the SQL pool to the Data Factory integration runtime before the stored procedure can be executed. This can be time-consuming and resource-intensive, especially for large datasets.
• On the other hand, the SQL pool stored procedure activity executes the stored procedure directly in the SQL pool, which can be faster and more efficient. This makes the SQL pool stored procedure activity more suitable for executing stored procedures on large datasets.

Input and Output Parameters:

• The SQL pool stored procedure activity supports input and output parameters, which can be used to pass data between the stored procedure and other components in your solution. This can be useful for complex data processing scenarios where you need to perform multiple operations on the data.
• The Stored procedure activity of Data Factory only supports input parameters, which limits its flexibility in this regard. This makes the SQL pool stored procedure activity more suitable for complex data processing scenarios.

Authentication:

• The SQL pool stored procedure activity supports the use of SQL authentication, which can be useful in scenarios where you need to execute stored procedures with different credentials.
• Azure Data Factory V2 now supports Azure Active Directory authentication as an alternative to SQL Server authentication for Azure SQL Database and SQL Data Warehouse.

In summary, the SQL pool stored procedure activity is optimized for performance and is more suitable for executing stored procedures on large datasets, complex data processing scenarios, and scenarios where you need to execute stored procedures with different credentials. The Stored Procedure activity of Data Factory is more suitable for executing stored procedures within ADF.

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.

Answer 2

Hi pmscorca,

Thanks for reaching out to Microsoft Q&A.

differencies between the Stored procedure activity of Data Factory and the SQL pool stored procedure activity of Synapse SQL pool

Both activities serve the purpose of invoking stored procedures, the SQL Pool Stored Procedure Activity in Azure Synapse SQL Pool offers streamlined implementation within the dedicated SQL pool context, but with dynamic compilation overhead.

ADF operates within its broader ecosystem, which is primarily a data integration service for orchestrating and managing data workflows.

Synapse dedicated pool is optimized for large-scale data warehousing and analytics.

specially in performance terms?

Data Factory:

• Performance depends on the underlying data source (e.g., Azure SQL Database).
• Compilation overhead is minimal.
• Suitable for general data transformation scenarios.

Dedicated SQL Pool:

• Optimized for large-scale data warehousing workloads.
• Dynamic compilation at runtime can impact performance, especially for complex queries.
• Provides specialized features for handling massive datasets efficiently.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.