Topic
12 replies Latest Post - ‏2013-02-04T14:53:57Z by PWConner
PWConner
PWConner
20 Posts
ACCEPTED ANSWER

Pinned topic embedded sql efficiencies - declaring a cursor in a loop

‏2013-01-24T20:40:05Z |
I know there is a better way to write the following code. I'm focused on the 'declare cursor' inside the doW loop because I believe I'm re-creating the same index per record fetched in the eofPOCostC() routine except the key value is different. In short, I am trying to simulate a SETLL/READE.

Advice on any of the code is welcome.

exec sql declare POCostC cursor 

for select warehouse, product, ... from qTemp/PPV 

for update ;   exec sql open POCostC;   doW not eofPOCostC(); exec sql declare WhTransC cursor 

for select Div, Ord, Line from WhTrans where CdKe1 = :POCostR.cdKe1 and Annul = 
' ' and CdTra = 
'51' order by CdKe1, nDtTra desc;   exec sql open WhTransC;   exec sql fetch WhTransC into :WhTran;   exec sql close WhTransC;   ... Do some RPG stuff ...   exec sql update qTemp/PPV set PO = :POCostR.PO, POLine = :POCostR.POLine, POCost = :POCostR.POCost, avgPODiff = :POCostR.avgPODiff, extPOCost = :POCostR.extPOCost, extDiff = :POCostR.extDiff where current of POCostC; endDo;   exec sql close POCostC;
Updated on 2013-02-04T14:53:57Z at 2013-02-04T14:53:57Z by PWConner
  • SystemAdmin
    SystemAdmin
    535 Posts
    ACCEPTED ANSWER

    Re: embedded sql efficiencies - declaring a cursor in a loop

    ‏2013-01-28T18:54:47Z  in response to PWConner
    I am far from an SQL expert but I think the code would be cleaner and more representative of what you are doing if you did a 'SELECT INTO' statement instead of a cursor. Don't know if it would make any difference efficiency wise.
    • PWConner
      PWConner
      20 Posts
      ACCEPTED ANSWER

      Re: embedded sql efficiencies - declaring a cursor in a loop

      ‏2013-01-28T19:15:56Z  in response to SystemAdmin
      Wish I could, but the results may include more than one record and I only want the most recent.
      • SystemAdmin
        SystemAdmin
        535 Posts
        ACCEPTED ANSWER

        Re: embedded sql efficiencies - declaring a cursor in a loop

        ‏2013-01-29T13:46:38Z  in response to PWConner
        If you only want the most recent record is there a way to do that in your select so that you only get that record?

        Maybe using a group by clause?

        Maybe a date in the record?

        Maybe a group by clause and Max(RRN(filename))?
        • PWConner
          PWConner
          20 Posts
          ACCEPTED ANSWER

          Re: embedded sql efficiencies - declaring a cursor in a loop

          ‏2013-01-30T13:57:26Z  in response to SystemAdmin
          Thanks for the advice and the questions.

          I probably could use some grouping and/or max function to get down to one record. Will changing the code to use a select-into instead of declaring and opening a cursor make the program more efficient?
          • SystemAdmin
            SystemAdmin
            535 Posts
            ACCEPTED ANSWER

            Re: embedded sql efficiencies - declaring a cursor in a loop

            ‏2013-01-30T14:31:22Z  in response to PWConner
            You only need a cursor if your select will be returning a group of records that you need to read through. If your select will only return a single row then a cursor is not needed.

            I was looking at your code again and I'm wondering why the cursor for qtemp/ppv? Can you join PPV to WHTRANS in a select? Then you can use a simple update statement to update the PPV record.

            Something like this maybe:
            -- Note addition of extra fields in select will need to be added to WhTran
            select t1.CdKe1, Div, Ord, Line, PO, POLine, POCost, AvgPODiff,
            extPOCost, extDiff
            from WhTrans t1 join qtemp/ppv t2 on CdKe1 = t2.cdKe1
            where Annul = ' ' and CdTra = '51'
            order by CdKe1, nDtTra desc into :WhTran;

            Update qTemp/PPV
            set PO = :WhTran.PO,
            POLine = :WhTran.POLine,
            POCost = :WhTran.POCost,
            avgPODiff = :WhTran.avgPODiff,
            extPOCost = :WhTran.extPOCost,
            extDiff = :WhTran.extDiff
            where CdKe1 = :WhTran:CdKe1;
            • PWConner
              PWConner
              20 Posts
              ACCEPTED ANSWER

              Re: embedded sql efficiencies - declaring a cursor in a loop

              ‏2013-01-30T16:56:26Z  in response to SystemAdmin
              I need to process all the records in PPV. Each record holds costing information from inventory. I am updating each record with the latest purchase order receipt costs and looking for variances.

              Once I fetch a PPV record I declare a cursor for warehouse transactions based on the PPV record just read. The warehouse transactions are limited to purchase order receipts for a particular product in date descending order. In order to clean up the code I suppose I could use some grouping/max functions to retrieve one record. By retrieving one record I could change the declare/open/fetch/close to a select-into. However, cleaning up the code is not my goal; run-time efficiency is.

              In RPG I would read a PPV record, use a setGT/setLL and readPE/readE combination. In order to use the setGT/setLL I would have a logical over the warehouse transaction file limited to purchase order receipts and sorted by product.

              I am curious if the SQE is smart enough to realize I need an index for purchase order receipts by product and build it for me. Or should I create the index and hope the SQE is smart enough to identify and use the index (this may be happening already). Otherwise, I am afraid an index is being built for every declare/open/fetch/close processed in the loop.
              • tcj2001
                tcj2001
                7 Posts
                ACCEPTED ANSWER

                Re: embedded sql efficiencies - declaring a cursor in a loop

                ‏2013-01-30T17:24:08Z  in response to PWConner
                May be I am not understanding, but here's what i have in my mind

                Lets say we have a 2 tables
                CREATE TABLE QTEMP/TMP1 (
                FLD1 CHAR ( 10) NOT NULL WITH DEFAULT,
                FLD2 CHAR ( 10) NOT NULL WITH DEFAULT,
                AMT1 DEC ( 9, 2) NOT NULL WITH DEFAULT,
                AMT2 DEC ( 9, 2) NOT NULL WITH DEFAULT,
                AMT3 DEC (9 , 2) NOT NULL WITH DEFAULT
                )

                CREATE TABLE QTEMP/TMP2 (
                FLD1 CHAR ( 10) NOT NULL WITH DEFAULT,
                FLD2 CHAR ( 10) NOT NULL WITH DEFAULT,
                AMTX DEC ( 9, 2) NOT NULL , 2) NOT NULL WITH DEFAULT,
                AMTZ DEC (9 , 2) NOT NULL WITH DEFAULT,
                DATE1 DATE NOT NULL WITH DEFAULT
                )

                TMP1 has
                FLD1 FLD2 AMT1 AMT2 AMT3
                AA BB 00.00 00.00 00.00
                CC DD 00.00 00.00 00.00

                TMP2 has data for the key in TMP1 for multiple dates
                FLD1 FLD2 AMTX AMTY AMTZ DATE1
                AA BB 100.00 200.00 300.00 05/20/12
                AA BB 600.00 600.00 600.00 06/21/12
                CC DD 20.00 30.00 40.00 07/19/11
                CC DD 60.00 70.00 80.00 07/21/11

                now we want to update TMP1 with TMP2 first record that come when TMP2 is sorted in descending order for the FLD1 and FLD2 combination

                use this SQL to update TMP1
                update qtemp/tmp1 a
                set (amt1,amt2,amt3)=
                (
                select amtx,amty,amtz
                from qtemp/tmp2 b
                where a.fld1=b.fld1 and a.fld2=b.fld2
                order by date1 desc
                fetch first 1 row only
                )

                you will have this result in TMP1
                FLD1 FLD2 AMT1 AMT2 AMT3
                AA BB 600.00 600.00 600.00
                CC DD 60.00 70.00 80.00
                Hope I am acheving the same result what you need using a single update statment instead of a cursor loop.
              • SystemAdmin
                SystemAdmin
                535 Posts
                ACCEPTED ANSWER

                Re: embedded sql efficiencies - declaring a cursor in a loop

                ‏2013-01-31T14:32:14Z  in response to PWConner
                Let me answer your last question first. Yes, SQE will create a temporary index on the fly if it is necessary. At a very high level, if it thinks your query will select most of the records in a table it will do a table scan. If the expected number is small it will create an index. That's over simplistic but you get the idea.

                I think I better understand your requirements now. Let me restate to see if I'm correct.

                You want the cost information (PO cost) from the most recent warehouse record for each PPV record with a matching CDKE1 value.

                On the assumption that is a correct understanding take a look at this:

                // Locate the most recent WhTrans record for a given cdke1 value
                // and filter by selection criteria
                with Whse_Data as (
                select cdke1, nDtTra
                from WhTrans
                where Annul = ' ' and CdTra = '51'
                group by cdke1, nDtTra
                having nDtTra = Max(nDtTra))

                // Join PPV records to CTE table above to get the date
                // Join to specific WhTrans record for warehouse data
                // End result should be PPV record information along with warehouse (cost)
                // information from most recent associated record
                select warehouse, product, ..., Div, Ord, Line
                from qTemp/PPV t1
                join Whse_Data t2 on t1.cdke1 = t2.cdke1
                join WhTrans t3 on t1.cdke1 = t3.cdke1 and t2.nDtTra = t3.nDtTra

                Now you just need to read through the returned result set (cursor), execute your RPG logic and update the PPV record directly using an update statement (no cursor).
          • tcj2001
            tcj2001
            7 Posts
            ACCEPTED ANSWER

            Re: embedded sql efficiencies - declaring a cursor in a loop

            ‏2013-01-30T15:03:51Z  in response to PWConner
            may be you can use a insert into statement with the select clause having a fetch first 1 row only clause as shown below

            insert into qtemp/tmp
            select * from libxxx/yyyyyyy where compny=020 and
            acctyr=2012 and acctmo = 1 order by gen desc
            fetch first 1 row only
  • jtaylor@lcc
    jtaylor@lcc
    4 Posts
    ACCEPTED ANSWER

    Re: embedded sql efficiencies - declaring a cursor in a loop

    ‏2013-01-28T19:41:29Z  in response to PWConner
    AFAIK, you could declare your cursor above the loop using a placeholder (?) for the variable and then do a
    open WhTransC using :POCostR.cdKe1
    inside the loop. I'm not sure that will do much for you efficiency-wise. I think the declare of a static SQL statement is mostly a compile-time thing. I believe the opening of the cursor is where the work happens during run-time.
    • B.Hauser
      B.Hauser
      56 Posts
      ACCEPTED ANSWER

      Re: embedded sql efficiencies - declaring a cursor in a loop

      ‏2013-02-01T06:42:49Z  in response to jtaylor@lcc
      1. temporary tables are not needed and are an unneccessary effort (if temporary objects must be built let it be done by the query optimizer)
      2. dynamic SQL i.e. using plache holders that are passed with the open statement is not needed. (Static SQL performs normally better. For dynamic SQL statements additional steps, for example syntax checking and converting the SQL statement prepared in a string into an executable SQL statement must be performed. ... and for dynamic SQL statement access plans are NOT stored within the (Service) Program object.
      3. the most time consuming step is not the declare but the open statement
      4. A SELECT into Statemente executes (under the cover) an OPEN/FETCH and CLOSE statement.
      5. If an index is adviced it should be created and tested whether it is used. The SQE will create temporary indexes (MTI = Maintained Temporary Index) under some circumstances that are "quasi" permanent, i.e. they will exist until the next IPL (or as long as an access plan that uses that MTI is stored within the SQE Plan Cache). Those MTIs can be used by all queries in any job (like an permanent index/access path).

      How (embedded) SQL works:
      1. For each SQL statement a data path must be created and opened (ODP). Before the data path can be opened an access plan is needed. Within the access plan the indexes to be used, the methods to access the data (index access, table scan ...), the temporary objects to be built for executing the SQL statement (hash tables, RRN lists ....) and the steps to be executed are described.
      --> An access plan is the receipt but does no cooking!
      2. Access plans are stored in the SQE plan cache (if the SQL statement can be executed by the SQE) and for static SQL statement within the embedded SQL (Service-)Program.
      3. When executing an SQL statement an access plan must be created or in case an access plan for the SQL statement already exists, it will be validated, i.e. the data composition (gathered by the statics manager) is checked as well as if any indexes were created or deleted since the last execution. Based on this information the access plan is either created or updated.
      4. After having created an access plan, the ODP is Opened, i.e. the temporary objects described within the access path are created and filled with data (FULL OPEN). The open process is the most time consuming step when executing an SQL statement. (--> SQL OPEN statement)
      5. With the SQL CLOSE statement after the 1st open of the SQL statement/cursor the ODP will be deleted(HARD CLOSE), i.e. the temporary objects are removed.
      6. If the same SQL statement is executed again within the same program/procedure, the existing access plan will be validated again and the ODP is reopened. (Should be slightly faster than the 1st execution)
      7. After the second execution the ODP stays open (if it is reusable) (PSEUDO or SOFT CLOSE). For all subsequent calls only the data within the (existing) temporary objects of the ODP are updated. (--> should be faster than the appropriate RPG SETLL/READE statements!)

      Birgitta
      • PWConner
        PWConner
        20 Posts
        ACCEPTED ANSWER

        Re: embedded sql efficiencies - declaring a cursor in a loop

        ‏2013-02-04T14:53:57Z  in response to B.Hauser
        Thanks for the discussion. I appreciate everyone's help.