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
There is a lot wrong here:
TRUNCATE TABLE, as it's much faster and less pressure on the transaction log.MERGE? You might as well do INSERT. And if you are only inserting then SqlBulkCopy will be faster.conn.Open should be async. But assuming you are using Dapper, which it looks like, then you don't need to open the connection as Dapper does that for you.So for bulk copy, insert into a DataTable, and send it in one big batch. Best practice is to set the ColumnMappings, and to add CheckConstraints and FireTriggers options (also TableLock if you can).
private async Task InsertCallTransferHistoryLog(List<CallTransferLogData> callTransferLogData)
{
using var conn = new SqlConnection(connectionString);
await conn.ExecuteAsync(@"TRUNCATE TABLE CallTransfer;", commandTimeout: 3000);
using var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.CheckConstraints);
bulk.DestinationTableName = "CallTransfer";
bulk.ColumnMappings.Add("SRBrokerName", "SRBrokerName");
bulk.ColumnMappings.Add("Log", "Log");
bulk.ColumnMappings.Add("SharePointSourceID", "SharePointSourceID");
bulk.ColumnMappings.Add("Created", "Created");
bulk.ColumnMappings.Add("Modified", "Modified");
var table = new DataTable { Columns = {
{ "SRBrokerName", typeof(string) },
{ "Log", typeof(string) },
{ "SharePointSourceID", typeof(int) },
{ "Created", typeof(DateTime) },
{ "Modified", typeof(DateTime) },
} };
var date = DateTime.Now;
foreach (var calltransfer in callTransferLogData)
table.Add(calltransfer.SRBrokerName, calltransfer.Log, calltransfer.SharePointSourceID, date, date);
await bulk.WriteToServerAsync();
}
In the case of Leads, you can use a Table Valued Parameter to do a MERGE in bulk.
First, create a Table Type. A clustering primary key is essential for good performance.
CREATE TYPE LeadType AS TABLE (
Id int PRIMARY KEY,
MSID int NOT NULL,
DateAdded datetime NOT NULL,
UMSID varchar(30) NOT NULL,
AmountFromAction decimal(19,6) NOT NULL
);
Then, again use a DataTable, but this time pass it to Dapper using AsTableValuedParameter with the table type name.
private async Task UpsertLeadsDataToSqlServer(List<Lead> leads)
{
using var conn = new SqlConnection(connectionString);
var table = new DataTable { Columns = {
{ "Id", typeof(int) },
{ "MSID", typeof(int) },
{ "DateAdded", typeof(DateTime) },
{ "UMSID", typeof(string) },
{ "AmountFromAction", typeof(decimal) },
} };
foreach (var lead in leads)
table.Add(lead.Id, lead.MSID, lead.DateAdded, lead.UMSID, lead.AmountFromAction);
const string sql = @"
MERGE INTO LeadsData (WITH SERIALIZABLE) AS t
USING @tvp AS s
ON t.Id = s.Id
WHEN MATCHED THEN
UPDATE SET
MSID = s.MSID,
DateAdded = s.DateAdded,
UMSID = s.UMSID,
//20 fields goes here
Modified = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
Id, MSID, DateAdded, UMSID, Created, Modified,AmountFromAction
)
VALUES (
s.Id, s.MSID, s.DateAdded, s.UMSID, GETDATE(), GETDATE(), s.AmountFromAction
);
";
await conn.ExecuteAsync(sql, new { tvp = table.AsTableValuedParameter("dbo.LeadType") });
}
You can also replace MERGE with separate UPDATE and INSERT, although if your table is correctly indexed then MERGE will be faster, and most bugs have been fixed now.