Post reply

Merge Statement performance Vs Custom Insert/Update/Delete Statement Vs Temporary Tables

  • August 3, 2015 at 3:35 pm

    #302969

    I'm in the process of rewriting a stored procedure at my new job. Currently, it's running another stored proc to generate data, using a function to customize that data, and then inserting it into a table for each customer, one at a time. The amount of data for each customer is about 6k rows (no blobs). It's not a lot of data but it can be slow (5 to 10 minutes) once you include the fact that the stored proc is also doing emails, ftping, etc. It's a mess.

    So, unfortunately, I'm not going to be able to do anything about calling the secondary stored procedure and using the function to get data. Unfortunately, it has to be done that way. However, my thought was that because the data changes sporadically it might be better to keep a third table with indexing online, and only making changes when there's a delete, update, new record. So, instead of generating a new table with 6k rows 100 times, several times a day, I use either a merge or custom inset/update/delete to make changes only to the ones that need them. I then pull the data from that third table.

    I don't use merge statements a lot and I'm curious in the background workings of the statement. My guess is that it's running something similar to a select to pull data that needs changes and then determining if there are other inserts/deletes that need to be made. Is this going to be quite a bit faster than the current process? Is it also going to be faster than if I do my own customer script for checking existing records?

    Thanks

  • August 4, 2015 at 4:42 am

    #1817061

    You're better off doing custom scripts. MERGE can, and does, function. But, it's not very tuneable. You get a single execution plan for the INSERT/UPDATE/DELETE regardless of which is used to compile the plan. It makes it very inflexible. If you need the ability to tune the query, you'll have to customize, so why not start there. It's not much more work, about the same really.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 4, 2015 at 7:35 am

    #1817110

    There have also been many issues with using MERGE, up to and including constraint violations. DBCC can even report errors after using MERGE. A quick Google Search will show you some of the issues.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 4, 2015 at 10:46 am

    #1817236

    Grant Fritchey (8/4/2015)

    You're better off doing custom scripts. MERGE can, and does, function. But, it's not very tuneable. You get a single execution plan for the INSERT/UPDATE/DELETE regardless of which is used to compile the plan. It makes it very inflexible. If you need the ability to tune the query, you'll have to customize, so why not start there. It's not much more work, about the same really.

    Makes sense. I was going to test it out anyway but was curious if anyone had experience. Is it best practice in general to not use Merge?

  • August 4, 2015 at 10:51 am

    #1817237

    JoshDBGuy (8/4/2015)

    Grant Fritchey (8/4/2015)

    You're better off doing custom scripts. MERGE can, and does, function. But, it's not very tuneable. You get a single execution plan for the INSERT/UPDATE/DELETE regardless of which is used to compile the plan. It makes it very inflexible. If you need the ability to tune the query, you'll have to customize, so why not start there. It's not much more work, about the same really.

    Makes sense. I was going to test it out anyway but was curious if anyone had experience. Is it best practice in general to not use Merge?

    I don't know that I'd go that far. I'm less than pleased with the flexibility and the ability to tune MERGE. But, it can be functional. On the other hand, I haven't hit some of the issues that others have and, especially when I see Jason and others saying not to, I tend to shy away. I'm not prepared to lump it in with multi-statement table-valued user-defined functions as an unclean process yet.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply