Slow down execution of bulk inserts

Question

We have an Azure SQL database which is on the S1 pricing tier. Our site is extremely heavily cached, so database hits are absolutely minimal. Average DTU usage is only ~1.5%, this is great as our DB costs are a fraction of what they used to be on our old website (£20p/m vs £400 p/m!)

On the site however, we do have small scripts that require insertion of ~100k records or so (user notifications for when someone performs an action such as creates a new tutorial).

When this is triggered, DTU's spike at 100% for around 3-5 minutes.

The script is simply a loop which calls an insert:

using(var db = new DBContext())
{
    foreach(var userID in userIDs)
    {
        db.ExecuteCommand(
        "INSERT INTO UserNotifications " +
        "(ForUserID, Date, ForObjectTypeID, ForObjectID, TypeID, Count, MetaData1) 
        VALUES ({0}, {1}, NULL, {2}, {3}, {4}, {5}, {6})",
        userID, DateTime.Now.ToUniversalTime(), forObjectID, (byte)type, 1, metaData1.Value
        );
    }
}

Is there a faster way to do inserts than this?
Additionally, what would be the best way to slow down execution of this script so DTU usage doesn't choke everything up?

I am confused. You want a faster way to do inserts but want to slow down execution? — Nkosi, May 15 '18 at 12:27
I am not sure ,if your query runs in parallel,if your query runs in parallel, you may end up using more resources you can try changing mdop to 1 for he duration of insert to see if your DTU consumption is lowered `ALTER DATABASE SCOPED CONFIGURATION ` — TheGameiswar, May 15 '18 at 12:29
See this answer as well :https://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework — TheGameiswar, May 15 '18 at 12:30
Sorry, to clarify I'm wondering if there's a way to speed up insert performance AND also what the best way to slow down execution speed is to ensure not all DTU's are consumed — Tom Gullen, May 15 '18 at 13:40
Writing in a queue and using a web job to write from the queue to the database. — Óscar Andreu, May 15 '18 at 14:14

paparazzo · Accepted Answer · 2018-05-16T10:49:53.247

You are doing one row per insert - that is not efficient.

A TVP is a like a reverse datareader and is efficient.

Lower tech is to insert 900 rows at a time (1000 is the max). This alone is probably 400x more efficient.

StringBuilder sb = new StringBuilder();
string insert = "INSERT INTO UserNotifications " +
                "(ForUserID, Date, ForObjectTypeID, ForObjectID, TypeID, Count, MetaData1) " +  
                "VALUES ";
sb.AppendLine(insert);
int count = 0;
using(var db = new DBContext())
{        
    foreach(var userID in userIDs)
    {
        sb.AppendLine(string.Format(({0}, {1}, NULL, {2}, {3}, {4}, {5}, {6}), ",
                      userID, DateTime.Now.ToUniversalTime(), forObjectID, (byte)type, 1, metaData1.Value);
        count++;
        if (count = 990) 
        {
            db.ExecuteCommand(sb.ToString());
            count = 0;
            sb.Clear();
            sb.AppendLine(insert); 
            //can sleep here to throttle down cpu 
        }            
    }
    if (count > 0) 
    {
        db.ExecuteCommand(sb.ToString());
    }
}

Óscar Andreu · Answer 2 · 2018-05-15T14:13:04.710

Instead of insert entity by entity you can make an insert of 100 entities at the same time packing the entities in a JSON and writing a store procedure that use it like in this example:

INSERT INTO [dbo].[AISecurityLogs]
    ([IpAddress], [TimeRange], [Requests], [LogId])
    SELECT *, LogId = @logId
    FROM OPENJSON ( @json )  
    WITH (   
        IpAddress varchar(15) '$.IpAddress',  
        TimeRange DATETIME '$.TimeRange',  
        Requests int '$.Requests'
     )

To slow down execution and doesn't lost anything you can put the logs in a queue, and then read this information with an azure Job, that allow you to configure the read interval, and insert in the database as I wrote before. This approach allows a big load (I have some several in production environments) and if something goes wrong with the agent or with the database, the messages are stored in the queue until you move them to the database.

I would use a table-type parameter rather than JSON; at least it would be strongly typed. See here: http://www.sommarskog.se/arrays-in-sql-2008.html — wBob, May 15 '18 at 12:54
If you generate the JSON with an strongly typed language there is no issue. — Óscar Andreu, May 15 '18 at 14:06

Slow down execution of bulk inserts

2 Answers2