Re: Insert statement taking a very long time to complete

On Mar 20, 7:58 am, "gor..._at_panix.com" <gor..._at_panix.com> wrote:
> I'm having some trouble with an SQL insert statement taking waaay too
> long
> to run. It's been suggested that maybe I'm using inefficient SQL, and
> that there might be a faster way to achieve the results.
>
> I have two tables with identical structure, and I want to copy all
> records
> that exist in table A but not in table B, into table B.
>
> Assuming that the structure is primary key "pk" and data fields "f1",
> "f2"
> and "f3", here is the SQL statement I'm using:
>
> insert into tableb(pk, f1, f2, f3)
> (select * from tablea where tablea.pk not in (select pk from
> tableb))
>
> This statement has worked well for small amounts of data, but when I
> tried
> on a table with approximately 200k records, it took forever to finish.
>
> A coworker with some SQL experience thinks that the "where" clause is
> being executed for *every* insertion. If so, this could certainly
> account
> for much of the slowdown.
>
> Anyone have any suggestions?

1. Use explain plan to check your cow-orkers truthiness.
2. Insert the difference between the two tables, see the MINUS operator in the fine manual.

jg

--
@home.com is bogus.
It ain't the backup, it's the restore:
http://www.signonsandiego.com/news/computing/20070320-0509-lostdata.html