I'm doing a major sort out of my database, there'll be c4500 companies and associated contacts etc being removed. Most of it is fairly simple but this little beastie

DELETE FROM Contacts WHERE _ID IN (SELECT _fkContacts FROM Career WHERE Career._fkCompanies = :xx AND Career._Left IS NULL)

is going to take over two seconds a time. A simple SELECT using the same subselect takes around 3 seconds. Using a JOIN its not quite zero.

The execution plan is below my sig. I think its telling me that the problem is that it has to compare every contact id with the list generated by the subselect which is why it can't use an index scan.

In this particular case the subselect will return 1 roe but there many be many rows.

Any thoughts on how to achieve a reasonable speed appreciated.

Roy Lambert

================================================================================
SQL Delete (Executed by ElevateDB 2.17 Build 1)

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
"Career"."_fkCompanies" = 1000001



AND

"Career"."_Left" IS NULL

ORDER BY "_fkContacts")



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

Contacts: 17606 rows

Filtering
---------

The following filter condition was applied to the Contacts table:

"_ID" IN (SELECT ALL "_fkContacts" AS "_fkContacts" FROM "Career" WHERE
"Career"."_fkCompanies" = 1000001

Index scan (Career.Company): 1 keys, 4KB estimated cost

AND

"Career"."_Left" IS NULL

Row scan with one or more index scans eliminated due to cost (Career): 1 rows,
448B estimated cost ORDER BY "_fkContacts")

Row scan (Contacts): 17606 rows, 15.04MB estimated cost

================================================================================
1 row(s) deleted in 2.325 secs
================================================================================

I see no response, have you found a solution?

This takes 0 seconds but cant be turned into a delete:
Select * from Itemvalues
Join Test
  ON Test.ID = ItemValues.TestID
and Test.TestDate < DATE '2011-11-11'

this takes 20 seconds, but can be turned into a delete:
Select * from Itemvalues
where TestID in (Select Distinct TestID from Test where Test.TestDate < DATE '2011-11-11')

Never mind using Exists solves this:

Delete from ItemValues
where Exists (
Select ItemID from Itemvalues
Join Test
  ON Test.ID = ItemValues.TestID
and Test.TestDate < DATE '2015-11-11')

Ignore that, it simply deletes all

Fred


I think I accept your last instruction


Roy Lambert

Roy,

maybe this link can help you:

http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_sql&page=1&msg=794#794

Uli

Uli


I presume you're referring to the use of EXISTS and yes that is much much faster.

Roy Lambert

Roy,

> I presume you're referring to the use of EXISTS and yes that is much much faster.

Right - but I was too lazy to work it out with your data

Uli

>Uli Becker wrote:
>maybe this link can help you:
>http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_sql&page=1&msg=794#794

Hi Uli,

I solved this by adding a Generated UniqueID column to the table.
Once I sat back I remembered we added a UID to each table when working with SQL-Server.
If your tables are small and won't run for decades then an integer will do otherwise we used a GUID which can be generated using CURRENT_GUID.

Now the Delete works like this:

Delete from table where UniqueID IN (Select UniqueID from table join other table on x=x and y=y);
That 20 second fetch now deletes in <2 seconds.

From what I remember the standard use for replicating dbs on SQL-Server was two GUIDs..

Fred

>I solved this by adding a Generated UniqueID column to the table.
>Once I sat back I remembered we added a UID to each table when working with SQL-Server.
>If your tables are small and won't run for decades then an integer will do otherwise we used a GUID which can be generated using CURRENT_GUID.
>
>Now the Delete works like this:
>
>Delete from table where UniqueID IN (Select UniqueID from table join other table on x=x and y=y);
>That 20 second fetch now deletes in <2 seconds.

Try

Delete from table where EXISTS (Select UniqueID from table join other table on x=x and y=y);

and see what that does to your speed. If its the same as mine it will drop well below 2 seconds.

Roy