if you can use xBase have an index on MainID, you could do it much faster with a:
do while seek([12345],[Resource],[MainId])
delete in Resource
enddo

>Having performance issues with this command:
>
>DELETE FROM Resource WHERE TaskId IN (SELECT id FROM Task WHERE MainId = 12345)
>
>It takes about 7 sec to run when in "multi-user environment", which is emulated by simply opening the tables in question from 5 VFP instances with no real traffic.
>Tables have about 20K records each.
>All index tags are present, however SYS(3054) reports that TaskId tag on Resource table is not used.
>
>Tried this in VFP9 with the same results:
>
>DELETE Resource FROM Task WHERE Resource.Taskid = Task.Id AND Task.MainId = 12345
>
>
>Meanwhile this works REAL fast:
>SELECT id FROM Task WHERE MainId = 12345 INTO ARRAY aTemp
>FOR EACH iTaskId IN aTemp
> DELETE FROM Resource WHERE TaskId=m.iTaskId
>ENDFOR
>
>So I figured it must be the "2nd join" issue, like in
> Select * from A Join B ... Join C ...
>where VFP can optimize only the first Join but not the 2nd one because it has to deal with an intermediary cursor. Or something like that. BTW what about this and all the improvements in VFP9?
>
>But contrary to my "figuring out" this also works fast:
>SELECT * FROM Resource WHERE TaskId IN (SELECT id FROM Task WHERE MainId = 12345)
>despite the fact that SYS(3054) reports no optimization on Resource table.
>
>My next guess is it has something to do with how vfp performs updates in the "multi-user" environment, but I don't know where to start...
>
>--Andrew