Enable Ledger on Azure SQL Server, will hang the Azure function execution which add around 65K records

Question

I have an Azure function which read data from Excel sheets and update 3 SQL tables, and it clear 2 SQL tables before re-add the rows. On some scenarios, I will add the data as-is, while on another scenario I need to check if a column named ID exists, if so, to do an update, otherwise to do insert. Here is sample of my Azure Function code, which is based on .NET Core 8. and the Azure function run under premium plan:

private async Task UpsertCallTransferHistoryLogToSqlServer(List<CallTransferLogData> callTransferLogData)

 {

 using (IDbConnection conn = new SqlConnection(connectionString))
 {
     conn.Open();
     await conn.ExecuteAsync(@"Delete FROM CallTransfer", null, null, 3000);
     foreach (var calltransfer in callTransferLogData)
     {
         string sql = @"
         MERGE INTO CallTransfer AS target
         USING (SELECT @SharePointSourceID AS SharePointSourceID) AS source
         ON target.SharePointSourceID = source.SharePointSourceID
         WHEN MATCHED THEN 
             UPDATE SET 
                 SRBrokerName = @SRBrokerName,
                 Log = @Log,
                 SharePointSourceID = @SharePointSourceID,
                 Modified = GETDATE()
         WHEN NOT MATCHED THEN 
             INSERT (SRBrokerName, Log,SharePointSourceID, Created, Modified)
             VALUES (@SRBrokerName, @Log, @SharePointSourceID, GETDATE(), GETDATE());";

         await conn.ExecuteAsync(sql, calltransfer, null, 300);
     }
 }
 await SQLInteraction(new Log { Status = "End SQL Interaction for Call Transfer History Log" }, false);}

And this method:

private async Task UpsertLeadsDataToSqlServer(List<Lead> leads, List<Tactic> tactics)

        {

            using (IDbConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                foreach (var lead in leads)
                {
                    string sql = @"
MERGE INTO LeadsData AS target
USING (SELECT @Id AS Id) AS source
ON target.Id = source.Id
WHEN MATCHED THEN 
    UPDATE SET 
        MSID = @MSID,
        DateAdded = @DateAdded,
        UMSID = @UMSID,
//20 fields goes here
        Modified = GETDATE()
WHEN NOT MATCHED THEN 
    INSERT (
        Id, MSID, DateAdded, UMSID,Created, Modified,AmountFromAction
    )
    VALUES (
        @Id, @MSID, @DateAdded, @UMSID, GETDATE(), GETDATE(),@AmountFromAction
    );
";
                    await conn.ExecuteAsync(sql, lead, null, 300);
                }
            }

            //Duplicate BGG
            using (IDbConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                await conn.ExecuteAsync(@"Delete FROM ByTactic", null, null, 3000);
                foreach (var bgg in bggbytactics)
                {
                    string sql = @"
    INSERT INTO ByTactic (
        MSID, UMSID, Publisher, Created, Modified
    )
    VALUES (
        @MSID, @UMSID, @Publisher, GETDATE(), GETDATE()
    );   
";
                    await conn.ExecuteAsync(sql, bgg, null, 300);
               }
           }

        }

Now I created an SQL Server on Azure and also on-premises DB for testing, and the function was able to complete the work in less than 5 minutes. Then we created a new DB on Azure and we enabled Ledger, but the Azure function execution will hang when working on the DB that has Ledger enabled. For example, on each run the ByTactic table will get around 65,000 rows, but after 30,000 rows the Azure function will stop execution, and the DB will raise timeouts (as per what the database admin told me) ... So, what I can do, do I need to modify my code to support the Ledger?

Thanks

Answer 1

No, the database does not time out. What times out is the client code that get's tired of waiting for the database.

Delete all and re-insert is a pattern that work decently small tables with no particular thrills. However for something like a temporal table or a ledger-enabled table, this pattern is not recommendable. The idea with ledger and temporal is that you can do time travel and see how the table looked at a certain point in time and when rows were modified. For such tables, you will need to determine the exact changes and only perform those.