Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2010
    Posts
    5

    Question Unanswered: Performance Issue with DB2 Select

    I need to perform the following to determine if a row exists with vendor type of 'E'. I only need one occurance to execute my processing. I have 36,000 records that need to have this 'select' executed. Is there a way to improve the performance of this SQL?

    EXEC SQL
    SELECT COUNT(*)
    INTO :WS-COUNT
    FROM SCE7000
    WHERE VNDRNOTE1KEY LIKE 'SAGE' ||
    :WS-SEL-VNDR-NBR || '%'
    AND (SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E'
    OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E')
    END-EXEC

    IF WS-COUNT > 0
    PERFORM 8500-WRITE-OUTPUT
    END-IF

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    why don't you just select the vender or SELECT 1 into a host variable,
    fetch first row only.

    count, by definition, will go thru the complete table, and since you have substr, you are scanning the table.
    whereas a select something with fetch first row only, will stop when 1 is found,
    or continue scanning until table end if nothing is found.
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This is a query which was first came in my mind.

    Code:
    EXEC SQL
    SELECT COALESCE(yes , no)
      INTO :WS-EXISTS
      FROM (SELECT 'No' FROM sysibm.sysdummy1
           ) AS q(no)
      LEFT OUTER JOIN
           (SELECT 'Yes'
              FROM sysibm.sysdummy1
             WHERE EXISTS
                   (SELECT 0
                      FROM SCE7000 
                     WHERE VNDRNOTE1KEY
                             LIKE 'SAGE' || 
                                  :WS-SEL-VNDR-NBR || '%' 
                       AND
                       (   SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E' 
                        OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E'
                       )
                   )
           ) AS r(yes)
       ON  0=0
    END-EXEC
    or simplify it, like this.
    (It will be neccesary to move 'No' to WS-EXISTS before executing the query.)
    Code:
    SELECT 'Yes'
      INTO :WS-EXISTS
      FROM sysibm.sysdummy1
     WHERE EXISTS
           (SELECT 0
              FROM SCE7000 
             WHERE VNDRNOTE1KEY
                   LIKE 'SAGE' || 
                        :WS-SEL-VNDR-NBR || '%' 
               AND
               (   SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E' 
                OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E'
               )
           )
    Last edited by tonkuma; 07-12-10 at 12:03.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Actually, this scenario is what global temporary tables were designed for. You insert your 36K records into the temp table, then join to your ecisting table based on these predicates you have already described. You could use a left outer join to your existing table, that way you could get all 36K records back with a notation whether they existed or not. Again, as told to you in an earlier post, all these substr functions on muli-part columns are going to kill your performance and you should put heat on your vendor over that.
    Dave

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool Change singleton select by CURSOR

    Quote Originally Posted by Deb Locy View Post
    I need to perform the following to determine if a row exists with vendor type of 'E'. I only need one occurance to execute my processing. I have 36,000 records that need to have this 'select' executed. Is there a way to improve the performance of this SQL?

    EXEC SQL
    SELECT COUNT(*)
    INTO :WS-COUNT
    FROM SCE7000
    WHERE VNDRNOTE1KEY LIKE 'SAGE' ||
    :WS-SEL-VNDR-NBR || '%'
    AND (SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E'
    OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E')
    END-EXEC

    IF WS-COUNT > 0
    PERFORM 8500-WRITE-OUTPUT
    END-IF
    Having 36000 rows in the table you don't need to worry about perfomance.
    But it could be better to change singleton select by CURSOR, if it's a possible.


    Lenny

  6. #6
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    all well and good, but we still do not know the number of rows in SCE7000
    I have 36,000 records that need to have this 'select' executed
    i interpret that statement to mean:
    I have a trigger file of 36,000 records,
    which means I have to execute this query against SCE7000 36,000 times.

    the temp table solution seems to probably be the best,
    I don't know at what point a base table (SCE7000) row quantity would provide a problem
    with a join against the temp table populated with 36,000
    on your toys (grin),
    but we mainframers would be reluctant to do this with a large base table.
    Last edited by dbzTHEdinosaur; 07-12-10 at 14:34. Reason: added a grin, was not making fun of your equipment
    Dick Brenholtz, Ami in Deutschland

  7. #7
    Join Date
    Jul 2010
    Posts
    5
    This is a one time run to remove SS #'s and in the future we will be converting from the mainframe. That is correct...the trigger file has 36,000 records. The table has 380,706 records in it. The 'exists' select worked as a Spufi but abends in my Cobol program. I am trying to debug it.

  8. #8
    Join Date
    Jul 2010
    Posts
    5
    The select with 'exists' is working and does seem to be faster. Thanks so much!!

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by tonkuma View Post
    This is a query which was first came in my mind.

    Code:
    EXEC SQL
    SELECT COALESCE(yes , no)
      INTO :WS-EXISTS
      FROM (SELECT 'No' FROM sysibm.sysdummy1
           ) AS q(no)
      LEFT OUTER JOIN
           (SELECT 'Yes'
              FROM sysibm.sysdummy1
             WHERE EXISTS
                   (SELECT 0
                      FROM SCE7000 
                     WHERE VNDRNOTE1KEY
                             LIKE 'SAGE' || 
                                  :WS-SEL-VNDR-NBR || '%' 
                       AND
                       (   SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E' 
                        OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E'
                       )
                   )
           ) AS r(yes)
       ON  0=0
    END-EXEC
    or simplify it, like this.
    (It will be neccesary to move 'No' to WS-EXISTS before executing the query.)
    Code:
    SELECT 'Yes'
      INTO :WS-EXISTS
      FROM sysibm.sysdummy1
     WHERE EXISTS
           (SELECT 0
              FROM SCE7000 
             WHERE VNDRNOTE1KEY
                   LIKE 'SAGE' || 
                        :WS-SEL-VNDR-NBR || '%' 
               AND
               (   SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E' 
                OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E'
               )
           )
    Which one is better ?
    Code:
    AND
               (   SUBSTR(VNDRNOTE1RSLT002,72,1) = 'E' 
                OR SUBSTR(VNDRNOTE1RSLT002,78,1) = 'E'
               )
    Or

    Code:
    AND NOT
               (     SUBSTR(VNDRNOTE1RSLT002,72,1) not = 'E' 
                 and SUBSTR(VNDRNOTE1RSLT002,78,1) not = 'E'
               )
    Lenny

Posting Permissions

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