Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Location
    Omaha, NE
    Posts
    25

    Unanswered: Which is better, Load/Delete/Insert or Update?

    Currently, we load our files into TEMP tables, delete the records from PROD that exist in TEMP, then INSERT the records from TEMP to PROD.

    Is that as efficient, more efficient or less efficient than an UPDATE statement to update the rows in PROD with the changed information in TEMP?

    Thanks for the information.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Which is better, Load/Delete/Insert or Update?

    Assuming your PROD tables have primary keys and the primary keys are not updated , you can use IMPORT with INSERT_UPDATE option ... That is an easy way of doing your stuff ....

    Cheers

    Sathyaram

    Originally posted by djschmitt
    Currently, we load our files into TEMP tables, delete the records from PROD that exist in TEMP, then INSERT the records from TEMP to PROD.

    Is that as efficient, more efficient or less efficient than an UPDATE statement to update the rows in PROD with the changed information in TEMP?

    Thanks for the information.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Which is better, Load/Delete/Insert or Update?

    You will have to realize that other applications, if any, in the environment will decrease in performance and decrease the performance of IMPORT ..

    Well, if you have other applications running during this job, have a low commitcount in the IMPORT to ensure that IMPORT doesn't escalate to tablelevel locking and prevent other applications from accessing the data ....

    HTH

    Cheers

    Sathyaram

    Originally posted by sathyaram_s
    Assuming your PROD tables have primary keys and the primary keys are not updated , you can use IMPORT with INSERT_UPDATE option ... That is an easy way of doing your stuff ....

    Cheers

    Sathyaram

  4. #4
    Join Date
    Nov 2002
    Location
    Omaha, NE
    Posts
    25
    There will not be any other applications running at the same time - at least in our database - as this will be on the data warehouse at night.

    Thanks for the suggestion, I'm testing this now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •