Unanswered

Extremely Slow SQL inserts

(0) Share

Report

Posted on by Community Power Pla...

Hi everyone,

I have a flow that takes rows from an Excel table and inserts it into a SQL database in Azure. The inserts into the SQL database are happening but at an extremely slow rate. For 256 rows to be inserted it took 14 minutes to complete. I have a column that sets the time when the record is created and there you can see well that the inserts are very slow. Is there anything that can be done to speed this up?

Id CreatedTime

22947 2020-04-14 12:30:33.503

22946 2020-04-14 12:30:32.587

22945 2020-04-14 12:30:31.527

22944 2020-04-14 12:30:30.120

22943 2020-04-14 12:30:29.167

22942 2020-04-14 12:30:27.917

22941 2020-04-14 12:30:26.790

22940 2020-04-14 12:30:25.723

22939 2020-04-14 12:30:24.440

22938 2020-04-14 12:30:23.493

Categories:

Building flows

I have the same question (0)

All responses (19)

Answers (1)

ChristianAbata 8,951 Most Valuable Professional on at

Like (1)

Report

hi @Anonymous please try to do the same insert but in your Azure SQL query console, if the insert takes the same time, is becouse your server probably has beed created in a location that is away from your real location in that case your latency is slow and you need to create again your sql data base. In the case that you have the data base into a VM is the same please see if the vm in a correct location for you.

Use this page to see what is the reagion with low latency for you. https://www.azurespeed.com/Azure/Latency

Was this reply helpful? Yes No
v-bacao-msft on at

Like (1)

Report

Hi @Anonymous ,

Follow up @ChristianAbata 's suggestions, you could try to set Apply to each to speed this up.

Image reference:

Hope this helps.

Best Regards,

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@v-bacao-msft Thank you for the help. I did change the setting and it was a bit faster it took 09:39 to run 256 rows. Still not breaking any speed records. Especially at the end it became very slow...

ID CreatedTime
23203 2020-04-15 09:06:42.103
23202 2020-04-15 09:06:41.010
23201 2020-04-15 09:06:39.590
23200 2020-04-15 09:05:26.903
23199 2020-04-15 09:05:25.483
23198 2020-04-15 09:05:08.123
23197 2020-04-15 09:04:09.747
23196 2020-04-15 09:04:08.437
23195 2020-04-15 09:04:07.323
23194 2020-04-15 09:04:06.130

I will move the DB to another region this afternoon hopefully that helps.
Eventually, I need to insert 25k records os this won't do it. Any other tips to improve the performance?

Was this reply helpful? Yes No
ChristianAbata 8,951 Most Valuable Professional on at

Like (0)

Report

@Anonymous sometimes the performance changes, if you create a Elastic DB vs Static db, and stactic db has low performance as elastic.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@ChristianAbata Thank you for your help. I think the real problem here is that it creates a connection per insert, that is why it is getting really slow. That is my only reasonable explanation for it, I don't have any hard proof, but it would make the most sense.

On my search to do bulk inserts I found this article: https://garrytrinder.github.io/2019/03/bulk-insert-array-of-json-objects-into-azure-sql-database-using-microsoft-flow

He uses a json object to insert with a stored proc.

The search continues....

Was this reply helpful? Yes No
Verified answer

Community Power Pla... on at

Like (5)

Report
For anybody who is having a similar issue here is my solution to the problem.

Use a select data operation to select from the excel output
Use compose to capture the output from the select
Use the output from compose to insert into SQL.
It took 6 seconds to insert 256 records seconds

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi JoostPielage,
Can you advise on how to later build input for Compose and SQL Query, please? I tried several different approached, and your looks promising :).

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi @Anonymous

The output of the compose is a pure JSON object. SQL can actually work with JSON. Here you can find any example.

https://stackoverflow.com/questions/46323946/how-to-insert-json-object-to-sql-server-2016-as-nvarchar

If you have any questions let me know.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

HI @Anonymous,
Many thanks! I figured it out and actually, it is my first experience with mixed SQL and JSON, so it was good learning. The only problem now is to go beyond 5000 rows limitation :).
Thanks again.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report

Hi @Anonymous if you go to the settings of you excel step. You can enable pagination and go past the initial 5000 row limit. The ultimate limit is 100k. I have never tested that. I have gone up to 52k without a big issue. Good luck on your flow!

Was this reply helpful? Yes No