In case you are using Microsoft SQL Server, you may have tried writing a MERGE (...) statement…. did you think it was fun? 🙂
I think MERGE statements are not fun at all, but from time to time it IS necessary to execute a performant UPSERT-type of operation.
Maybe you also know that the fastest way to execute a huge number of upserts is to
- create a custom table data type
- create a stored procedure that executes the
MERGEstatement, using the data type as its input type - STREAM rows to the stored procedure via a table in tempdb
which can be pretty tedious to code.
Which is why the “upsert helper”, Debaser, was made 🙂
With Debaser, you can execute obscenely fast upserts by doing the following things:
Create a class that matches the layout of the table you want
E.g. something like
class SomeDataRow
{
public SomeDataRow(int id, decimal number, string text)
{
Id = id;
Number = number;
Text = text;
}
public int Id { get; }
public decimal Number { get; }
public string Text { get; }
}
will do.
Initialize the upsert helper
This can be done like this:
var helper = new UpsertHelper<SomeDataRow>(connectionString);
and then you might want to
helper.CreateSchema();
when your application starts up. This will create a table, a custom table data type, and an appropriate stored procedure.
Upsert like crazy
Pass very very long sequences of row objects to the upsert helper like this:
await helper.Upsert(longLongSequence);
which is cool, because the upsert helper streams the data to SQL Server.
Pretty cool
If you think this looks cool, you should go to the Debaser wiki and read more about it, and maybe you want to Install-Package Debaser and start punishing your database right away?
Happy upserting!
Happy New Year!