Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    2

    Unanswered: SQL query performance issue

    I'm not new to SQL but am far from an expert. Most of my experience has been with non-DB2 databases so bear with me. I'm trying to run an sql update query against an AS400 and its taking an incredibly long time to run. Here's the query:

    update R35FILES.VCMUDEF set CCF043 = 'Y' where CCCUST in (
    select SBCUST
    from ( SELECT SBCUST, sum(SBPRIC) as DOLLARS FROM VAIMODSDTA.VSADETL where SBDIV = 23 and (SBCLS = 27 or SBCLS = 28) group by SBCUST )
    as shinkoDlrs
    where DOLLARS > 2000
    )

    The VCMUDEF file has approximately 15,000 records and the VSADETL file has 4.7 million records.

    If I run this portion of the statement

    select SBCUST
    from ( SELECT SBCUST, sum(SBPRIC) as DOLLARS FROM VAIMODSDTA.VSADETL where SBDIV = 23 and (SBCLS = 27 or SBCLS = 28) group by SBCUST )
    as shinkoDlrs
    where DOLLARS > 2000
    )

    results are returned in 37 seconds. When I run the statement with the update on it, the query runs for several hours and still does not complete (it never errors out ... I just cancel it). Is there something wrong with my logic?

    Any help would be appreciated.

    Brad

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Which indexes do you have?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2008
    Posts
    2
    VCMUDEF is indexed by CCCUST (Customer #).

    VSADETL is indexed by SBORD (Order #).

    As a general logic question regarding this issue I'm having, does DB2 execute the internal subselects for every record in VCMUDEF? Or does it store the resultant set of Customer Numbers for the update to go against?

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Brad,
    When you say that select takes 37 seconds. Does it take 37 seconds for you to get the entire resultset or until it returns some records to you? A lot of products that allow you to query the database only get so many records at a time, but how long does it take until the last record is fetched is often quite different.
    I would think an index on VSADETL table with the columns SBDIV and SBCLS would be beneficial to you. Also, I would change your sql to something more like:
    update R35FILES.VCMUDEF
    set CCF043 = 'Y'
    where CCCUST in (select SBCUST
    from VAIMODSDTA.VSADETL
    where SBDIV = 23
    and SBCLS IN (27,28)
    group by SBCUST
    having sum(SBPRIC) > 2000)
    Another solution without a new index is there must be some key between these two tables that is indexed so you could try something like:
    update R35FILES.VCMUDEF a
    set CCF043 = 'Y'
    where exists (select SBCUST
    from VAIMODSDTA.VSADETL b
    where a.key = b.key
    and SBDIV = 23
    and SBCLS IN (27,28)
    group by SBCUST
    having sum(SBPRIC) > 2000)
    I'm not too sure about the group by/having clause in an exists/not exists clause, never tried it before. Good luck.

    Dave Nance

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    man thta was a brain fart on my part. your a key and b key were in the query cccust and sbcust
    Dave

Posting Permissions

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