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 ?