CODEWITHSUNDEEP
sqlperformanceinsert
Mark
Mark

Reputation: 69

Strategy for optimizing performance with SQL Insert or Update

I use the following stored procedure to update a record, or if the record didn't exist, write a new one. I'm experiencing performance issues as the table grows, and wondering what's a better approach to achieve this. 

CREATE PROCEDURE [dbo].[spCheckSheet_QuestionRecordsInsertOrUpdatePrimary]
    @QuestionPrimaryValue varchar(50),  
    @QuestionID int,
    @CSRecordID int
AS
    UPDATE tbCheckSheet_QuestionRecords
    SET [QuestionPrimaryValue] = @QuestionPrimaryValue
    WHERE [CSRecordID] = @CSRecordID AND [QuestionID] = @QuestionID

    IF @@ROWCOUNT = 0
       INSERT INTO tbCheckSheet_QuestionRecords ([CSRecordID], [QuestionID], [QuestionPrimaryValue], [QuestionSecondaryValue])
       VALUES (@CSRecordID, @QuestionID, @QuestionPrimaryValue, '')

I'm wondering if I should do a straight insert into a minimally indexed table, then move the data into the permanent table behind the scenes? My research doesn't support this strategy, but I'm not sure what else will work.

Thanks for your help.

Mark

Upvotes: 2

Views: 62

Answers (2)

uh_big_mike_boi
uh_big_mike_boi

Reputation: 3470

I think MERGE would be faster but probably not that much faster since its one record.

CREATE PROCEDURE [dbo].[spCheckSheet_QuestionRecordsInsertOrUpdatePrimary] 
@QuestionPrimaryValue varchar(50),  
@QuestionID int,
@CSRecordID int
AS
MERGE tbCheckSheet_QuestionRecords
AS TARGET
    USING (
           SELECT @QuestionPrimaryValue
                , @QuestionID
                , @CSRecordID
          ) AS SOURCE (QuestionPrimaryValue, QuestionID, CSRecordID)
    ON (TARGET.QuestionID = SOURCE.QuestionID
        AND TARGET.CSRecordID = SOURCE.CSRecordID)
    WHEN MATCHED THEN 
        UPDATE SET QuestionPrimaryValue = SOURCE.CSRecordID
                 , QuestionSecondaryValue = ''
    WHEN NOT MATCHED THEN
        INSERT (CSRecordID, QuestionID, QuestionPrimaryValue, QuestionSecondaryValue)
        VALUES (SOURCE.CSRecordID, SOURCE.QuestionID, SOURCE.QuestionPrimaryValue, '' )

Upvotes: 2

Holmes IV
Holmes IV

Reputation: 1739

Although this does not have your code, I believe this is exactly what you are trying to do: Using SQL Merge or UPDATE / INSERT

Upvotes: 1

Related Questions