Are you a new DBA looking to optimize the performance of your SQL Server database? In this article, we will discuss a technique called UPSERT that can help you achieve better performance by reducing unnecessary reads on your tables.

When working with a high number of transactions per second, every additional read performed by the database can have a significant impact on performance. To address this issue, we can use UPSERTs, which combine an Update and an Insert operation into a single statement.

Traditionally, programmers would use an IF EXISTS statement to determine whether an Update or an Insert is necessary. However, this approach requires an additional read to check for existence, resulting in two I/O operations for each call of the stored procedure.

Instead, we can use UPSERTs to directly perform the necessary operation without the need for a separate existence check. This reduces the number of reads and improves performance. Let’s take a look at an example:

UPDATE dbo.tbTicketing
SET DiscrepancyFlag = @Parm2,
    Status = @Parm1
WHERE TicketNumber = @Parm3

IF @@rowcount = 0
    INSERT INTO dbo.tbTicketing (TicketNumber, Customer, TicketDate, TicketFrom, Comments, DiscrepancyFlag, Status)
    VALUES (@parm3, 'Test1', GETDATE(), 'Marketing', 'Testing', @parm2, @parm1)

In the above example, we first attempt to update the row based on the given parameters. If no rows are affected by the update, we then insert a new row with the provided values. This approach ensures that only one I/O operation is required in most cases, as the row will typically already exist.

To compare the performance of UPSERTs with the traditional IF EXISTS approach, we can use the SQL Server Management Studio’s “Set statistics io on” command to view the number of reads performed on the table. In our tests, UPSERTs consistently resulted in fewer reads compared to the IF EXISTS approach.

By utilizing UPSERTs, you can significantly improve the performance of your SQL Server database by reducing unnecessary reads. This technique is especially useful when dealing with high transaction volumes. Remember, the key to optimizing performance is to minimize the number of reads on the database.

We hope you found this article helpful in your journey as a DBA. Stay tuned for more tips and tricks to optimize your SQL Server database performance. If you have any questions or suggestions, feel free to leave a comment below.

Happy coding!