Professional Web Applications Themes

How do I speed this up? - Oracle Server

Are the columns not NULL ? "Tim Shute" <tshutenisoft.com> wrote in message news:90196d5dc21dc37c89d331703b615c99.46509mygate .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" <mhedenbigfoot.com> wrote in message > news:53b3de4.0212060743.56c83fc4posting.google.co ...

  1. #1

    Default Re: How do I speed this up?

    Are the columns not NULL ?
    "Tim Shute" <tshutenisoft.com> wrote in message
    news:90196d5dc21dc37c89d331703b615c99.46509mygate .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" <mhedenbigfoot.com> wrote in message
    > news:53b3de4.0212060743.56c83fc4posting.google.co m
    >
    > > 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
    >
    >
    >
    >
    > --
    > Posted via Mailgate.ORG Server - [url]http://www.Mailgate.ORG[/url]

    Telemachus Guest

  2. #2

    Default Re: How do I speed this up?

    "Mike Heden" <mhedenbigfoot.com> wrote in message
    news:53b3de4.0212060743.56c83fc4posting.google.co m...
    > I have two tables which use a common unique key.
    >
    Why?

    Surely, logically they are the same table?

    Regards,
    Paul


    Paul Brewer Guest

  3. #3

    Default Re: How do I speed this up?

    "Mike Heden" <mhedenbigfoot.com> wrote in message
    news:53b3de4.0212071108.2326c6e4posting.google.co m...
    > Paul,
    >
    > No they're not the same table, although you could be forgiven for
    > thinking that was the case. Both tables contain other columns that
    > are particular to the individual table. It's just the uniquekey
    > column that is common to both. Normally the rows in thses two tables
    > always have a couterpart row with the same uniquekey value in the
    > other table. I need to remove rows where there is no matching entry
    > in the other table.
    I think that what Paul was getting at was that if both tables have the same
    key, and the same set of records should be found in both tables, then from a
    design point of view they should not be two tables but one.

    As far as your question goes you may well find that not exists works better
    than not in.


    --
    Niall Litchfield
    Oracle DBA
    Audit Commission UK
    *****************************************
    Please include version and platform
    and SQL where applicable
    It makes life easier and increases the
    likelihood of a good answer
    ******************************************


    Niall Litchfield Guest

  4. #4

    Default Re: How do I speed this up?


    "Niall Litchfield" <niall.litchfielddial.pipex.com> wrote in message
    news:3df25697$0$236$cc9e4d1fnews.dial.pipex.com.. .
    > "Mike Heden" <mhedenbigfoot.com> wrote in message
    > news:53b3de4.0212071108.2326c6e4posting.google.co m...
    > > Paul,
    > >
    > > No they're not the same table, although you could be forgiven for
    > > thinking that was the case. Both tables contain other columns that
    > > are particular to the individual table. It's just the uniquekey
    > > column that is common to both. Normally the rows in thses two tables
    > > always have a couterpart row with the same uniquekey value in the
    > > other table. I need to remove rows where there is no matching entry
    > > in the other table.
    >
    > I think that what Paul was getting at was that if both tables have the
    same
    > key, and the same set of records should be found in both tables, then from
    a
    > design point of view they should not be two tables but one.
    >
    <snip>

    Absolutely correct, Niall. That's why I said logically.
    OP may need to look again at the physical design.

    Regards,
    Paul



    Paul Brewer Guest

  5. #5

    Default Re: How do I speed this up?


    "Mike Heden" <mhedenbigfoot.com> wrote in message
    news:53b3de4.0212071121.748d46aeposting.google.co m...
    > Paul,
    >
    > Something I forgot to mention in my last reply...
    >
    > Your right - *logically* they could be the same table. Unfortunately,
    > they're not. This is something that is used by a third-party
    > application, and it relies upon this particular data structure, so I
    > cannot change it.
    >
    > Regards,
    >
    > Mike
    >
    Mike,

    I followed up Niall's post (above in my threading), before reading this one.
    Now I see the dreaded mention of "third-party application", and I
    sympathise. Sorry.
    BTW: Not Peoplesoft, by any chance?

    On to the performance issue, have you tried 'delete from tablea where
    pk_tablea in (select pk_tablea minus select pk_tableb from tableb)'?

    Regards,
    Paul



    Paul Brewer Guest

Similar Threads

  1. How to speed up getPageNthWordQuads with VB6
    By andrew_fam@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 4
    Last Post: September 16th, 03:06 AM
  2. Speed
    By DTFX in forum Macromedia Flash Sitedesign
    Replies: 1
    Last Post: February 25th, 08:41 PM
  3. ? about this NG speed.
    By Rodney Wise in forum PERL Beginners
    Replies: 8
    Last Post: September 10th, 11:22 PM
  4. Replies: 1
    Last Post: August 15th, 11:50 AM
  5. ISO speed
    By Ampersand in forum Photography
    Replies: 1
    Last Post: July 15th, 05:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139