The two queries below are taking almost the whole time for my script to execute and I presume its down to the sub-selects. Suggestions as to how to speed them up would be appreciated.


DELETE FROM Contacts WHERE _ID IN (SELECT _fkcontacts FROM CAREER WHERE _fkCompanies = 1000002)

================================================================================
SQL Delete (Executed by ElevateDB 2.03 Build 3)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.

================================================================================


DELETE FROM "Contacts"
WHERE "_ID" IN (SELECT ALL "_fkcontacts" AS "_fkcontacts" FROM "CAREER" WHERE
"_fkCompanies" = 1000002  ORDER BY "_fkcontacts")

Target Table
------------

Contacts: 7905 rows

Filtering
---------

The following filter condition was applied to the Contacts table:

"_ID" IN (SELECT ALL "_fkcontacts" AS "_fkcontacts" FROM "CAREER" WHERE
"_fkCompanies" = 1000002  ORDER BY "_fkcontacts") [Row scan (Contacts): 7905 rows,
7146120 bytes estimated cost]

================================================================================
5 row(s) deleted in 0.889 secs
================================================================================


DELETE FROM Career WHERE _fkContacts NOT IN (SELECT _ID FROM Contacts)

================================================================================
SQL Delete (Executed by ElevateDB 2.03 Build 3)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.

================================================================================


DELETE FROM "Career"
WHERE "_fkContacts" NOT IN (SELECT ALL "_ID" AS "_ID" FROM "Contacts" ORDER BY
"_ID")

Target Table
------------

Career: 8100 rows

Filtering
---------

The following filter condition was applied to the Career table:

"_fkContacts" NOT IN (SELECT ALL "_ID" AS "_ID" FROM "Contacts" ORDER BY "_ID")
[Row scan (Career): 8100 rows, 3564000 bytes estimated cost]

================================================================================
0 row(s) deleted in 1.638 secs
================================================================================



Roy Lambert

Roy,

<< The two queries below are taking almost the whole time for my script to
execute and I presume its down to the sub-selects. Suggestions as to how to
speed them up would be appreciated. >>

Are the times in the query plans what you're seeing when they are executed
in the script ?  If so, then no, you probably won't be able to speed them up
much further.  You might want to try using this form instead:

DELETE FROM Contacts
WHERE EXISTS(SELECT * FROM Career WHERE Career._fkcontacts=Contacts._ID AND
_fkCompanies = 1000002)

DELETE FROM Career
WHERE NOT EXISTS(SELECT * FROM Contacts WHERE
Contacts._ID=Career._fkContacts)

That might be a bit faster since it is the equivalent of an INNER JOIN.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Are the times in the query plans what you're seeing when they are executed
>in the script ? If so, then no, you probably won't be able to speed them up
>much further. You might want to try using this form instead:

As best as I can guess yes. I executed each statement in the script independently as a SELECT and most of the others were 0 with a couple at c0.015

>DELETE FROM Contacts
>WHERE EXISTS(SELECT * FROM Career WHERE Career._fkcontacts=Contacts._ID AND
>_fkCompanies = 1000002)
>
>DELETE FROM Career
>WHERE NOT EXISTS(SELECT * FROM Contacts WHERE
>Contacts._ID=Career._fkContacts)
>
>That might be a bit faster since it is the equivalent of an INNER JOIN.

About a quarter second faster in each case. Thanks.

Roy Lambert