1 complex insert or multiple updates

ajayjainajayjain Posts: 104Member
Hi,

I need to consolidate G/L entry table for 16 companies
records are in millions
I have written Insert into Select query with lots of Join statement where I am calling G/L account name, etc from other tables into 1 new consolidated table

It works fine but to speed up
should I simply insert only g/l entry fields first and then update with g/l account name, dimensions,period etc
or
1 big insert will be better


Thanks
Ajay Jain
UK

Comments

  • KishormKishorm Posts: 908Member
    I suspect the 2nd option would be faster - shouldn't take you long to test this out seeing as you already have the 1st option working. Give it a go and post back and let us know the results.
  • bbrownbbrown Posts: 3,097Member
    Also to help speed things up:

    1. Export original table object
    2. Disable SQL maintenance of all secondary keys and Sumindexs
    3. Do your import. Not having to update the above for each record will help speed it up.
    4. Then re-import the original table object to recreate the keys etc.

    There are no bugs - only undocumented features.
  • bbrownbbrown Posts: 3,097Member
    Note: If your joins depend on some of the secondary keys, then leave only those enabled. But disable any SumIndexes they have.
    There are no bugs - only undocumented features.
  • ajayjainajayjain Posts: 104Member
    Thank you KishorM and bbrown :smile:
    Ajay Jain
    UK
Sign In or Register to comment.