Performance: Subquery or Joining

Question

I got a little question about performance of a subquery / joining another table

INSERT
INTO Original.Person
  (
    PID, Name, Surname, SID
  )
  (
    SELECT ma.PID_new , TBL.Name , ma.Surname, TBL.SID 
    FROM Copy.Person TBL , original.MATabelle MA
    WHERE TBL.PID         = p_PID_old
      AND TBL.PID         = MA.PID_old
  );

This is my SQL, now this thing runs around 1 million times or more. My question is what would be faster?

• If I change TBL.SID to (Select new from helptable where old = tbl.sid)

OR

• If I add the 'HelpTable' to the from and do the joining in the where?

edit1
Well, this script runs only as much as there r persons.

My program has 2 modules one that populates MaTabelle and one that transfers data. This program does merge 2 databases together and coz of this, sometimes the same Key is used.
Now I'm working on a solution that no duplicate Keys exists.

My solution is to make a 'HelpTable'. The owner of the key(SID) generates a new key and writes it into a 'HelpTable'. All other tables that use this key can read it from the 'HelpTable'.

edit2
Just got something in my mind:
if a table as a Key that can be null(foreignkey that is not linked) then this won't work with the from or?

Answer 1

Modern RDBMs, including Oracle, optimize most joins and sub queries down to the same execution plan.

Therefore, I would go ahead and write your query in the way that is simplest for you and focus on ensuring that you've fully optimized your indexes.

If you provide your final query and your database schema, we might be able to offer detailed suggestions, including information regarding potential locking issues.

Edit

Here are some general tips that apply to your query:

• For joins, ensure that you have an index on the columns that you are joining on. Be sure to apply an index to the joined columns in both tables. You might think you only need the index in one direction, but you should index both, since sometimes the database determines that it's better to join in the opposite direction.
• For WHERE clauses, ensure that you have indexes on the columns mentioned in the WHERE.
• For inserting many rows, it's best if you can insert them all in a single query.
• For inserting on a table with a clustered index, it's best if you insert with incremental values for the clustered index so that the new rows are appended to the end of the data. This avoids rebuilding the index and often avoids locks on the existing records, which would slow down SELECT queries against existing rows. Basically, inserts become less painful to other users of the system.

Answer 2

Joining would be much faster than a subquery

Answer 3

The main difference betwen subquery and join is subquery is faster when we have to retrieve data from large number of tables.Because it becomes tedious to join more tables. join is faster to retrieve data from database when we have less number of tables.

Also, this joins vs subquery can give you some more info

Answer 4

Instead of focussing on whether to use join or subquery, I would focus on the necessity of doing 1,000,000 executions of that particular insert statement. Especially as Oracle's optimizer -as Marcus Adams already pointed out- will optimize and rewrite your statements under the covers to its most optimal form.

Are you populating MaTabelle 1,000,000 times with only a few rows and issue that statement? If yes, then the answer is to do it in one shot. Can you provide some more information on your process that is executing this statement so many times?

EDIT: You indicate that this insert statement is executed for every person. In that case the advice is to populate MATabelle first and then execute once:

INSERT 
INTO Original.Person 
  ( 
    PID, Name, Surname, SID 
  ) 
  ( 
    SELECT ma.PID_new , TBL.Name , ma.Surname, TBL.SID  
    FROM Copy.Person TBL , original.MATabelle MA 
    WHERE TBL.PID         = MA.PID_old 
  );

Regards, Rob.