3

I'm working on a legacy application that uses SQL Server as a backing store. There's a portion of the app that does a ridiculous amount of inserts to a single table. Re-architecting this portion of the app represents a large time investment, probably 2 weeks for one developer. It works as is so we're, well happy isn't the right word, I guess we grudgingly accept it.

However, that portion of the app performs terribly when connected to an Azure database. I'm talking a sudden 4x increase in time that it takes to insert records. Each cycle inserts about 4000 rows into this table via a call to a stored proc (4000 calls; it's awful, I know). On a local SQL Server it will take about 5 minutes but on Azure that spikes up to >15 minutes and our users find that unacceptable.

The table has about 20 columns, mostly bits and ints. It has one primary key (int) and no other indexes. It has 2 foreign keys. Including the full CREATE script here seems excessive, but I can if needed.

What I'm wondering is if there's any way to "tune" an Azure database to handle large numbers of inserts better. Altering the code isn't a great option right now, but it is the right choice down the road.

Azure v12. We use S2 or S3 instances and don't see any high CPU or high DTU usage other than spikes for a few seconds here and there. Databases are all under 1GB.

Improve this question

2 Answers 2

Reset to default
2

Table compression is now supported in V12. You can compress the table to reduce the IO (as you mentioned CPU is not a problem). Try to batch the requests as each commit has overhead. You are just taling about 20 * 4 = 80 bytes per row and 80 * 4k = 320k data which is 0.5MB. This should be doable if tune the app in both S2 & S3. As you mentioned you are not hitting DTU limits, concurrent connections help too.

Improve this answer
1
  • 1
    I don't know if this actually solved the problem -- the company that I was working with went bankrupt shortly after this answer was submitted. But it's the only one with upvotes, so it gets the check.
    – jcollum
    Commented Jan 9, 2016 at 20:08
1

Perform some more analysis on the bottle neck

Cannot imagine 4000 simple inserts is the problem
As you stated you don't see any high CPU or high DTU usage

From local to Azure is a much longer network round trip

Create a sp where you insert 4000 rows one at a time in loop One network call with 4000 inserts
That should be like in the one minute range (67 / second)
Test that sp both local and Azure

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.