Re: Puzzled with SQL performance

On Thu, 19 Mar 1998 20:49:45 -0500, Alton Ayers <alton.ayers_at_ditw.com> wrote:

>I'm puzzled. I have a table, ach_details, containing 366,534 rows.
>It's defined with tran_rec_id as the first column in the primary key.
>The second table, today_transactions, has 0 rows. It has
>today_tran_rec_id as the first column in its primary key.
>
>This query takes 80 seconds to execute:
>
>SQL> DELETE FROM ach_details
> 2 WHERE tran_rec_id IN
> 3 (SELECT today_tran_rec_id FROM today_transactions
> 4 WHERE org_id = 150);
>

Since today_transactions is empty SELECT today_tran_rec_id FROM today_transactions WHERE org_id = 150 returns nothing which might be the problem.

>

>0 rows deleted.
>
>
>This query takes 1.5 seconds to execute:
>
>SQL> DELETE FROM ach_details
> 2 WHERE tran_rec_id IN
> 3 (SELECT NVL(today_tran_rec_id, 0) FROM today_transactions
> 4 WHERE org_id = 150);
>
>0 rows deleted.
>
>This third query takes long enough (hours) that I haven't let it finish:
>
>SQL> DELETE FROM ach_details
> 2 WHERE tran_rec_id IN
> 3 (SELECT tran_rec_id FROM today_transactions
> 4 WHERE org_id = 150);
>
>Notice that there is no "tran_rec_id" defined in today_transactions.

I think third query will delete all your records in ach_details.
>
>Does anyone have an explanation for this behavior?

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de