0

I have an Azure data factory loop activity that executes stored procedure on every iteration, passing 2 JSON objects as arguments. Stored procedure reads json objects with openJson function using cross apply few times .. and then constructs #temp table and after some sql massaging inserts data from #temp table into a static sql table.

If I imitate this tasks via sqlserver management studio it takes no time at all, so I am able to hit execute button 20=50 times and it would show total time always as 0.

However, in a data factory every execution of an activity calling stored procedure takes a long time .. can take 5-6 seconds, that in a loop amounts to a very long time. Is there any ways to tune these in ? So that every execution takes less time ?

Dmitriy Ryabin
  • 363
  • 3
  • 16

1 Answers1

0

Is there any ways to tune these in ? So that every execution takes less time?

I also tried with sample stored procedure which is executing in 0 seconds in SSMS and taking 4-6 seconds in ADF. If your stored procedure is taking 5-6 seconds, then it's a normal time of execution in Azure data factory stored procedure activity.

You can break up the method into a series of sub-procedures and call the master procedure and call that master procedure from data factory as a workaround to reduce the time it takes.

The above approach will help you to reduce the take taken by procedure in one go by doing the task in sub procedures. If it's still taking more than expected time. Please check link which explains how to raise support ticket.

Pratik Lad
  • 4,343
  • 2
  • 3
  • 11