Re: How do I speed this up?

Tim,

Thanks for your help. Your suggestion made a dramatic difference, reducing execution time to a couple of seconds for tables containing around 17000 rows.

Thanks again.

Mike

"Tim Shute" <tshute_at_nisoft.com> wrote in message news:<90196d5dc21dc37c89d331703b615c99.46509_at_mygate.mailgate.org>...
> Hi Mike
>
> The problem with what you're doing is that the 'NOT IN ( ...)' builds
> a comma-delimited list of uniquekeys that match. Once this gets over a
> few hundred it slows down.
>
> Try
> delete from tablea
> where not exists( select * from tableb where tablea.uniquekey =
> tableb.uniquekey)
>
> Or something similar to that.
>
> HTH
>
> Tim.
> ==
> tableb.uniquekey from tableb)
> "Mike Heden" <mheden_at_bigfoot.com> wrote in message
> news:53b3de4.0212060743.56c83fc4_at_posting.google.com
>
> > I have two tables which use a common unique key.
> >
> > I want to delete a number of rows from table a where the unique key
> > tablea.uniquekey is not present in table b as tableb.uniquekey.
> >
> > In most cases the number of rows in tablea and tableb is roughly
> > equal.
> >
> > I've used:-
> >
> > delete from tablea where tablea.uniquekey not in (select
> > tableb.uniquekey from tableb)
> >
> > This works OK for small numbers of rows, but the time to execute the
> > query is a function of the product of the numbers of rows in the two
> > tables. In practice if tablea and b copntain a couple of thousand
> > rows the execution time is 5 seconds. However this rises to 240
> > seconds where the rowcount is around 17,000. For larger rowcounts the
> > execution time quickly becomes unacceptable.
> >
> > What's the quickest alternative way to achieve this? I'm using Oracle
> > 8.1.7
> >
> > Thanks,
> >
> > Mike
Received on Wed Dec 11 2002 - 02:32:04 CST