Re: optimizing in delete-statement

> Hi,
>
> following statement works very slow:
>
> DELETE FROM tRoute
> WHERE RouteId IN
> (
> SELECT ItemId
> FROM tUserXorganizer
> WHERE Type = :1 AND FKUserId = :2
> )
> ;
>
> It performs a full table scan on tRoute.
> IMHO that's why it has to search all RouteId's and then have a look at
> tUserXOrganizer to find them there.
> Is there a better way for deleting on this table without full table
> scan? This statement is used in a procedure.

Hello

Does tRoute have an index on RouteId?
Does tRoute have a primary key that is referenced by another table whose foreign key is not indexed?

Rene Nyffenegger

-- 
  no sig today