0

I have merge query like below

MERGE [dbo].[tablename] AS tpr
USING @Tabletype AS spr
ON tpr.[ee_typeid] = spr.[ee_typeid]
WHEN MATCHED THEN 
UPDATE SET tpr.[name]=spr.[name],
           tpr.[modifiedon]=spr.[modifiedon],
           tpr.[statecode]=spr.[statecode],
           tpr.[statecodename]=spr.[statecodename]
WHEN NOT MATCHED THEN 
INSERT ([ee_engagementtypeid],[ee_name],[modifiedon],[statecode],[statecodename])
values (spr.[ee_typeid],spr.[ee_name],spr.[modifiedon],spr.[statecode],spr.[statecodename]);

So when this procedure is executed it checks whether the record exist if yes then update or insert, since I don't know which column is modified so I 'm not putting any conditions instead I use update statement directly.

Could you please let me know what is the good practice? 1. To put condition before update statement to check each column which one is updated and update only that column. 2. Or To update directly all columns without checking which one is modified.

Performance wise which one is better 1st or 2nd ? I feel 2nd will be slower, let me know please if you have best query to handle such conditions.

gofr1
  • 15,741
  • 11
  • 42
  • 52
harshu288
  • 141
  • 1
  • 12
  • Tag the dbms you're using. (Seems to be a pretty product specific statement.) – jarlh Oct 12 '16 at 08:45
  • Re-tagged with `sql-server` based on the syntax used. –  Oct 12 '16 at 08:46
  • Try both, measure it for a sensible test load, and see. Alternatively, compare the length/complexity of the two query statements and choose the shorter/simpler one. (p.s. that's the unconditionally no-op update one) – Caleth Oct 12 '16 at 08:58
  • well as I said the 2nd one seems to be better that's the reason I need experts help on this. – harshu288 Oct 12 '16 at 09:51
  • Turn on statistics IO and statistics Time and compare those when running either version. It'll give you better data. – Allan S. Hansen Oct 12 '16 at 11:08

0 Answers0