Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    57

    Question Unanswered: Possible Performance Improvement

    The following select statement works for me but it takes a lot of process time and is rather slow. I'd like to know if there's another way of doing the same query only faster and less process time intensive.

    The following script contains 2 queries that use 2 tables. One query is used only to exclude a certain range of data.

    Code:
    select distinct pkvk.CUST_JOB_NR
    from pkvk,pkvp
        where pkvk.JOB_STATUS != 9
        and pkvp.CUST_JOB_NR = pkvk.CUST_JOB_NR
        and pkvp.CUST_JOB_TYPE = pkvk.CUST_JOB_TYPE
        and pkvk.CUST_JOB_NR
    
    not in (select  distinct pkvk.CUST_JOB_NR
    from   pkvp,pkvk
          where  pkvk.JOB_STATUS != 9
          and    pkvp.CUST_JOB_NR = pkvk.CUST_JOB_NR
          and    pkvp.CUST_JOB_TYPE = pkvk.CUST_JOB_TYPE
          and    pkvp.POSITIONS_STATUS != 9)

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    logically would this do the job?

    select distinct pkvk.CUST_JOB_NR
    from pkvk,pkvp
    where pkvk.JOB_STATUS != 9
    and pkvp.CUST_JOB_NR = pkvk.CUST_JOB_NR
    and pkvp.CUST_JOB_TYPE = pkvk.CUST_JOB_TYPE
    and pkvp.POSITIONS_STATUS = 9

  3. #3
    Join Date
    Nov 2004
    Posts
    57
    A first glance it would but I tried that already. Let me explain my query a little bit more in detail.

    We have Customer Orders, a Customer Order contains n Work Orders. Each Customer Order and Work Order has a status "9" for complete and all other numbers for "work in progress"

    This query is for house keeping because there are Customer Orders that are in "work in progress" with all their Work Orders completed.

    A quick example of this is a order for a car. Every car need 4 wheels. So a car order will contain 4 Work Orders under it (one for each wheel). If all the Work Orders are complete then the Customer Order for the car should also be complete.

    But I have car orders that are in "not complete" status with all the work orders for the wheels as complete.

    Car Order = "not complete"
    Wheel Installation = "complete"
    Wheel Installation = "complete"
    Wheel Installation = "complete"
    Wheel Installation = "complete"

    When I run the suggested query:

    Code:
    select distinct pkvk.CUST_JOB_NR
    from pkvk,pkvp
    where pkvk.JOB_STATUS != 9
    and pkvp.CUST_JOB_NR = pkvk.CUST_JOB_NR
    and pkvp.CUST_JOB_TYPE = pkvk.CUST_JOB_TYPE
    and pkvp.POSITIONS_STATUS = 9 
    From just this query I get like 11,000 returns of Customer Orders not completed and with at least 1 Work Order completed.

    Back to my example for better explaination the query would return all car orders "not complete" that have one or more wheel installed.

    Car Order = "not complete"
    Wheel Installation = "not complete"
    Wheel Installation = "not complete"
    Wheel Installation = "complete"
    Wheel Installation = "not complete"

    Because 1 of the wheel installations is complete it's true for the select statement even though it's not what I really want.

    The correct query returns "12" while the other returns "11,000". The problem to get down to the "12" it takes a long time. I'm looking for a possible quicker way to do this.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    OK you can attack this in a number of different ways but it is best to try out the various different solutions and look at the execution stats and plan using production data and probably on your production database.

    1) Have your tables been analyzed recently
    2) An index on pkvp(POSITIONS_STATUS, CUST_JOB_NR , CUST_JOB_TYPE ). You may find ordering the columns differently can make a big difference. Analyze the index after creation.
    3) Rewrite the query using joins, not exists, group by etc. i.e.

    select distinct pkvk.CUST_JOB_NR
    from pkvk,pkvp,
    (select distinct pkvk.CUST_JOB_NR
    from pkvp,pkvk
    where pkvk.JOB_STATUS != 9
    and pkvp.CUST_JOB_NR = pkvk.CUST_JOB_NR
    and pkvp.CUST_JOB_TYPE = pkvk.CUST_JOB_TYPE
    and pkvp.POSITIONS_STATUS != 9) x
    where pkvk.JOB_STATUS != 9
    and pkvp.CUST_JOB_NR = pkvk.CUST_JOB_NR
    and pkvp.CUST_JOB_TYPE = pkvk.CUST_JOB_TYPE
    and pkvk.CUST_JOB_NR=x.CUST_JOB_NR(+)
    and x.CUST_JOB_NR is null

    Alan

  5. #5
    Join Date
    Nov 2004
    Posts
    57
    Thanks Alan,

    Now the query runs signifigantly faster. The query runs in minutes rather than an hour.

    I just have a couple questions about the code that you've written. What do the "x" signify in your code:

    Code:
    and    pkvp.POSITIONS_STATUS != 9) x
    where pkvk.JOB_STATUS != 9
        and pkvp.CUST_JOB_NR = pkvk.CUST_JOB_NR
        and pkvp.CUST_JOB_TYPE = pkvk.CUST_JOB_TYPE
        and pkvk.CUST_JOB_NR=x.CUST_JOB_NR(+)
        and x.CUST_JOB_NR is null

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    When you specify tables in the from clause especially when they have long names you can give them whats called an alias, or shortcut name.

    Now what I've done is to move your subquery into the from clause so in effect its as if it created a temp table from the subquery and then it joins to it in the where clause. Ofcourse since its a temp table I've had to make up a name or alias for it which in this case was x, then any columns from the subquery are referenced as x.<column name>.
    i.e. think of it as creating a temp table called x from your subquery.

    By the way did you need to add the index or was the rewritten query sufficient?

    Alan

  7. #7
    Join Date
    Nov 2004
    Posts
    57

    Thumbs up

    Good to know thanks Alan. I didn't attempt the index option the rewritten query will suffice.

Posting Permissions

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