SQL Server Bulk Upsert using Table Valued Parameters

-
Not much background to this post. Had an interest in improving a part of some work code that did multiple upsert operations, and came up with a bulk insert stored procedure using the pattern described by Aaron Bertand. Uploading an example that uses the StackOverflow2010 database and Users table. Performance is greatly improved by using this over individual upsert statements.

Users table valued parameter: 

USE [StackOverflow2010]
GO

/****** Object:  Table Valued Parameter [dbo].[UsersTVP] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TYPE [dbo].[UsersTVP] AS TABLE (
[Id] [int] NULL,
[AboutMe] [nvarchar](max) NULL,
[Age] [int] NULL,
[CreationDate] [datetime] NOT NULL DEFAULT GETDATE(),
[DisplayName] [nvarchar](40) NOT NULL DEFAULT '',
[DownVotes] [int] NOT NULL DEFAULT 0,
[EmailHash] [nvarchar](40) NULL,
[LastAccessDate] [datetime] NOT NULL DEFAULT GETDATE(),
[Location] [nvarchar](100) NULL,
[Reputation] [int] NOT NULL DEFAULT 1,
[UpVotes] [int] NOT NULL DEFAULT 0,
[Views] [int] NOT NULL DEFAULT 0,
[WebsiteUrl] [nvarchar](200) NULL,
[AccountId] [int] NULL
)
GO

UpsertUsers stored procedure: 

USE [StackOverflow2010]
GO

/****** Object:  StoredProcedure [dbo].[UpsertUsers] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Tom Barrett
-- Create date: 2022-12-09
-- Description: Upserts a list of Users using table valued parameter
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[UpsertUsers]
-- Add the parameters for the stored procedure here
@Users UsersTVP READONLY
AS
BEGIN
/* SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. */
SET NOCOUNT ON;
/* SET XACT_ABORT ON for proper rollback on errors */
SET XACT_ABORT ON;

IF NOT EXISTS (SELECT TOP 1 * FROM @Users)
/*THROW 51000, '@Users can not be empty', 1;*/
RETURN

BEGIN TRY
BEGIN TRANSACTION;

UPDATE [dbo].[Users] WITH (UPDLOCK, SERIALIZABLE) 
SET [AboutMe] = [tvp].[AboutMe]
, [Age] = [tvp].[Age]
, [CreationDate] = [tvp].[CreationDate]
, [DisplayName] = [tvp].[DisplayName]
, [DownVotes] = [tvp].[DownVotes]
, [EmailHash] = [tvp].[EmailHash]
, [LastAccessDate] = [tvp].[LastAccessDate]
, [Location] = [tvp].[Location]
, [Reputation] = [tvp].[Reputation]
, [UpVotes] = [tvp].[UpVotes]
, [Views] = [tvp].[Views]
, [WebsiteUrl] = [tvp].[WebsiteUrl]
, [AccountId] = [tvp].[AccountId]
FROM [dbo].[Users] AS [u]
INNER JOIN @Users AS [tvp]
ON [u].[Id] = [tvp].[Id];

INSERT [dbo].[Users]
           ( [AboutMe]
           , [Age]
           , [CreationDate]
           , [DisplayName]
           , [DownVotes]
           , [EmailHash]
           , [LastAccessDate]
           , [Location]
           , [Reputation]
           , [UpVotes]
           , [Views]
           , [WebsiteUrl]
           , [AccountId])
SELECT [AboutMe]
           , [Age]
           , [CreationDate]
           , [DisplayName]
           , [DownVotes]
           , [EmailHash]
           , [LastAccessDate]
           , [Location]
           , [Reputation]
           , [UpVotes]
           , [Views]
           , [WebsiteUrl]
           , [AccountId]
FROM @Users as [tvp]
WHERE NOT EXISTS (SELECT 1 FROM [dbo].[Users] WHERE [Id] = [tvp].[Id]);
 
COMMIT TRANSACTION;
END TRY

BEGIN CATCH
ROLLBACK;
END CATCH

IF @@TRANCOUNT > 0 ROLLBACK;
END
GO



Comments

Post a Comment

Popular posts from this blog

Adding Font Awesome to .NET MAUI App

-
First, download the desktop version of the Font Awesome fonts. https://fontawesome.com/download Extract the .OTF files to the project's Resources/Fonts folder. You can rename the files to something easier to type/read, if desired.  After copying, check the properties on the files and make sure the Build Action = "MauiFont". Open the MauiProgram.cs file and add the new fonts to the app builder. public static class MauiProgram {   public static MauiApp CreateMauiApp() {     var builder = MauiApp.CreateBuilder();     builder       .UseMauiApp<App>()       .ConfigureFonts(fonts => {         fonts.AddFont("OpenSans-Regular.ttf", "OpenSansRegular");         fonts.AddFont("OpenSans-Semibold.ttf", "OpenSansSemibold");         fonts.AddFont("FontAwesome6Brands.otf", "FontAwesomeBrands");         fonts.AddFont("FontAwesome6Regular.otf", "FontAwesomeR...
Read more

Comparing SELECT Performance of Joining Integers vs GUIDs in SQL Server

-
Image
Overview There are plenty of online conversations and articles asking which makes a better primary key: INT or GUID? There are pros and cons to both, but from my perspective the winner is  INT  by a small margin. As shown in this article , as long as the indexes are maintained to keep fragmentation down the performance is similar, with  INT  slightly ahead in both index size and the speed of CRUD operations. The key is storing the GUID as a UNIQUEIDENTIFIER datatype and never using VARCHAR. Not only is SQL Server less efficient at comparing strings, there's also a chance to run into collation issues comparing across databases. In these discussions, one thing I haven't seen is a comparison of SELECT performance when joining different datatypes. After all, it's rare to find queries that don't stretch across multiple tables in a relational database. I used the StackOverflow2013 database (83 GB size) for testing. Download a copy for yourself from Brent Ozar's  fanta...
Read more

Performance: SQL Server vs MongoDB (JSON data)

-
Anyone reading the title of this post may immediately start to question my sanity and/or competency. After all, SQL is a relational datastore and MongoDB is a document datastore, and wouldn't it just be obvious that a document datastore would be more effective at storing unstructured JSON documents? I have been learning more about NoSQL databases and that's the common wisdom, but I've been around long enough to doubt any hype over any technology. I want to know: how much better? Do the new JSON features in SQL Server 2019 compare well? I wanted to see how both systems perform when the rubber meets the road. Setup To do the comparisons, I'm using DICOM files as the data. The protocol details are too deep to get into here, but they're used in health care to transfer information and images between systems. They are distinct documents, with information stored in tags and sequences, and readily translate to a JSON format . Here's a full example . Updates are not rea...
Read more