Re: Slow Cursor

Nils Myklebust wrote: > On Tue, 29 Sep 1998 02:31:21 GMT, justabill@school.house.rocks > (justabill) wrote: > > >delete from FirstTable > >where> >FirstTable.PK1 || FirstTable.PK2 not in > >(select secondtable.PK1 || secondtable.PK2) > > > >This is a lot faster, I'm not convinced that it is the fastest way of > >doing it which is what I usually strive for. > > When you do a "not in" using a subquery I believe the engine will > execute that subquery for every row you delete. I don't think so, since it is not a correlated subquery. It should build a temp table for the subquery, and just use the same temp table for each row. If it does re-run the subquery, I'd say that was a bug. > With the above syntax I am also afraid that it will not be able to use > an index, not even the primary key index on PK1,PK2. Yes, this could be true. As Nils said, use SET EXPLAIN to see what it's doing. > select FirstTable.PK1 || FirstTable.PK2 as ftpk, > SecondTable.PK1 as stpk1 > from FirstTable, outer SecondTable > where FirstTable.PK1 = SecondTable.PK1 > and FirstTable.PK2 = SecondTable.PK2 > into temp tt_1; > > delete from FirstTable > where FirstTable.PK1 || FirstTable.PK2 in > (select ftpk from tt_1 where stpk1 is null);> > The above should work, but you will have to test it. > The idea is that the "in (select...)" is much faster than a "not in" > and the difference is large enough in many cases that even with the > initial building of a temporary table like this the combination of the > two statements will still be faster than your single statement. Yes, IN (...) is faster than NOT IN (...), but since I don't think the first query will re-build the temp table on each pass, this will not necessarily be faster. I think the outer join on the first half of this will slow it down. However, as Nils says, you should test for yourself. > I dislike it profoundly though. Concatenating things to create a new > key field on the fly may not allways work exactly as expected and it > may not be particularly fast. Got to agree here. The concatenation thing is inelegant, and prone to problems. June -- june_t@hotmail.com Grounded in Palo Alto, living on Pepperidge Farm Double Chocolate Milano Cookies