SQL optimization

[ccampbell]

Just a question for all of you gurus out there. I am currently running insert and update SQL statements similar to the following:

PRINT 'UPDATING Table1
  UPDATE Table1
  SET val1 = X.val1,
      val2 = X.val2
  FROM XML_Table1 X 
  WHERE Table1.VIN=X.VIN

PRINT 'INSERTING INTO Table1
  INSERT Table1 (val1,
    		 val2)
  SELECT DISTINCT val1,
    	          val2
  FROM XML_Table1 X 
  WHERE NOT EXISTS
  (SELECT seq_num FROM Table1 Y 
  WHERE Y.VIN=X.VIN)

rather than go through the tables twice (in some cases I have to perform several joins to update and insert which is making my tempdb grow quickly) in order to insert and update, is there a way I can search through the table once and perform either an insert or an update? I know in the Oracle world you can do a merge statement, is there an equivellant in the SQL world? I am essentially moving data from working tables to production tables but have keys and constraints to worry about when updating and inserting.

Thanks in advance

 

[vongrunt]

> is there a way I can search through the table once and perform either an insert or an update?

Nope. No REPLACE/UPSERT/MERGE in T-SQL world...

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[ccampbell]

Is there a more efficient way to do things aside from the way that I am currently doing them?

 

[vongrunt]

Nope again. UPDATE then INSERT is the best order of actions.

If speed is the problem, you can try DML in smaller batches (say, 50000 rows at once)... or eventually drop unnecessary indexes and create 'em back after all inserts and updates.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[MDXer]

Replacing you Where not exists in the insert with a left outer join where y.vin is null, might possibly speed up the insert porting.

Working in data warehousing I am always doing these types of functions to move data into fact or dimension tables and have found the Left Outer Join to be faster. as for the update I have built a system that applies a binnary checksum to the record which I carry as a CRC column then the addition of where x.crc <> y.crc when joining the 2 tables ensures that only the records that have actually been modified are updated. the combination of these 2 have lead to an average of 60% increase in performance and sometimes as high as 80% increase.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck