This blog will show the comparison between executing a stored procedure using web activity for API for GraphQL and script activity. A stored procedure called usp_PipelineQueue which will return a list of pipelines for orchestration. The stored procedure is stored in Fabric SQL Database which serves as a metadata database.
Prerequisite: Already configured item of API for GraphQL connecting to the Fabric SQL Database that contains the stored procedure. To learn about how to create an API for GraphQL, click here.
Web Activity using API for GraphQL
In a new data pipeline, add web activity shape to execute the stored procedure usp_PipelineQueue and configure the connection to the GraphQL endpoint and provide the Body and Headers for the query. Below example shows a mutation as stored procedure contains CREATE statements. Also, keep note of the bearer token to be passed as a parameter. An another web activity can be used to fetch access token automatically.
Script Activity
In another data pipeline, we use the native approach of executing stored procedure via Script activity. In Settings, point the connection to the Fabric SQL DB and provide query to execute the stored procedure.
Results
API for GraphQL web activity took 8 secs, i.e. 7 secs for queueing and 1 sec for execution.
Whereas, script activity took 26 secs, i.e. 22 secs for queueing and 4 secs for execution.
Conclusion
As queueing time can be different on every execution, but execution duration shows that API for GraphQL is 3 times faster than the native script activity. API for GraphQL may seem new and fiddly but it does hold the capability to query data faster and provides flexibility to users.
