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
MERGE
statement, using the data type as its input typewhich 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:
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.
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.
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.
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!
About the author