ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

VIEW with GROUP BY

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • VIEW with GROUP BY

    I created a VIEW which JOINS 6 files. I do a GROUP BY so that I can SUM a few columns. It's pretty sluggish the first time in, which is expected, and then runs quicker each call after that. Would I be better off removing the GROUP BY in my VIEW and moving it into the RPG code where I use the VIEW or will this make no difference?

    It's just a bit of a drag since it's a web application and I've time out a few times. I do send the user a message that they've time and to search again, which comes back almost instantly, but I don't think it's a great user experience!

    Thanks in advance as usual.
    Your friends list is empty!

  • #2
    Did you use the index advisor to see if it recommends creating some indexes? Based on what I've experienced, if you do what it recommends, it has a huge positive effect.

    Comment


    • #3
      I started looking at that today. I'm not great at deciphering what it's trying to tell me! I'm also re-writing the whole mess with a bunch of WITH statements to try and at the very least organize the whole thing!

      One question, I thought I read somewhere you should only use physical files in VIEWS?

      Thanks for the advice.
      Last edited by mjhaston; October 10, 2017, 12:23 PM.
      Your friends list is empty!

      Comment


      • #4
        Originally posted by mjhaston View Post
        I started looking at that today. I'm not great at deciphering what it's trying to tell me! I'm also re-writing the whole mess with a bunch of WITH statements to try and at the very least organize the whole thing!

        One question, I thought I read somewhere you should only use physical files in VIEWS?

        Thanks for the advice.
        Yes - you can only use tables / physical files in a view. The index however builds index paths that, depending on what you're doing, can tremendously improve the performance on the querying of your view.

        Comment


        • #5
          First step: Run your SQL Statement (or view) through visual explain and check whether there are index advices.
          Without having seen your SELECT statement and without knowing anything about your tables and data composition, we only can guess.

          An Index includes the access path (as well as a logical file) that can be used by the SQL optimizer. But an index cannot be specified within a SELECT statement. (Contrary to a DDS described logical file, but the SQL optimizer will have to rewrite the statement based on the underlying physical files and tables).
          An SQL View is nothing else than a stored SELECT statement (without any access path). An SQL View can be used whereever a physical file or table can be used.

          Birgitta

          Comment


          • #6
            I went through the Visual Explain and created the Indexes as it suggest. Did I need to recreate the VIEW after the new Indexes were created? I did, but didn't think it necessary.

            If I'm reading this Visual Explain correctly, the "Total Estimated Run Time" has now gone from 69k to 204k (ms).

            I've attached my big ugly VIEW. Don't hate!

            Attached Files
            Last edited by mjhaston; October 11, 2017, 09:21 AM.
            Your friends list is empty!

            Comment


            • #7
              The only issue with using common table expressions like you've shown, is that all the records in the file selection must be processed first before getting to the final select for processing.

              Try this SQL statement and see how it performs.

              Code:
              select 
                coalesce(orders.ohstat, ' ') as ohstat,
                coalesce(orders.ohorno, 0) as ohorno,
                coalesce(orders.ohcuno, ' ') as ohcuno,
                coalesce(orders.ohcope, ' ') as ohcope,
                coalesce(orders.ohoref, ' ') as ohoref,
                coalesce(orders.ohordt, ' ') as ohordt,
                coalesce(orders.ohords, 0) as ohords,
                coalesce(orders.ohroco, ' ') as ohroco,
                coalesce(orders.ohodat, 0) as ohodat,
                coalesce(orders.ohgdsm, ' ') as ohgdsm,
                coalesce(orders.ohdano, 0) as ohdano,
                coalesce(orders.ohiano, 0) as ohiano,
                coalesce(orders.ohhand, ' ') as ohhand,
                coalesce(invoices.ihcuno, ' ') as ihcuno,
                coalesce(invoices.ihorno, 0) as ihorno,
                coalesce(invoices.ihinvn, 0) as ihinvn,
                coalesce(invoices.ihidat, 0) as ihidat,
                coalesce(invoices.ihiait, 0) as ihiait,
                coalesce(invoices.ihitta, 0) as ihitta,  
                coalesce(salesOrders.oswatp, ' ') as oswatp,
                coalesce(salesOrders.osprdc, ' ') as osprdc,
                coalesce(salesOrders.osseno, ' ') as osseno,
                coalesce(salesOrders.ostecn, ' ') as ostecn,
                coalesce(orderType.type, ' ') as type,
                sum(case when invoiceDetails.idpagr = 'PART' then coalesce(invoiceDetails.idamou, 0) else 0 end) as parts,
                sum(case when invoiceDetails.idpagr = 'LABO' then coalesce(invoiceDetails.idamou, 0) else 0 end) as labor,
                sum(case when invoiceDetails.idpagr = 'FREI' then coalesce(invoiceDetails.idamou, 0) else 0 end) as freight
              From mylibrary.orderfile as orders
              left outer join mylibrary.invoicefile as invoices
              on orders.ohcuno = invoices.ihcuno
              and orders.ohorno = invoices.ihorno
              left outer join mylibrary.invoicedetail as invoiceDetails
              on invoices.ihcuno = invoiceDetails.idcuno
              and invoices.ihorno = invoiceDetails.idorno
              and invoices.ihinvn = invoiceDetails.idinvn
              and invoiceDetails.idfocc = 'N'
              left outer join mylibrary.salesorders as salesOrders
              on orders.ohorno = salesOrders.osorno
              left outer join
              (select distinct
                other2.ctotyp as ctotyp,
                case when other2.ctsvcc = 'N' then other1.ctopty else 'S' end as type
              from mylibrary.other1 other1
              inner join mylibrary.other2 other2 on other2.ctotyp = other1.ctotyp) as orderType
              on orders.ohordt = orderType.ctotyp
              where orders.ohstat <> 'D' and orders.ohodat is not null and orders.ohodat > int(replace(char(current date - 24 months, iso),'-','')) 
              group by
                coalesce(orders.ohstat, ' '),
                coalesce(orders.ohorno, 0),
                coalesce(orders.ohcuno, ' '),
                coalesce(orders.ohcope, ' '),
                coalesce(orders.ohoref, ' '),
                coalesce(orders.ohordt, ' '),
                coalesce(orders.ohords, 0),
                coalesce(orders.ohroco, ' '),
                coalesce(orders.ohodat, 0),
                coalesce(orders.ohgdsm, ' '),
                coalesce(orders.ohdano, 0),
                coalesce(orders.ohiano, 0),
                coalesce(orders.ohhand, ' '),
                coalesce(invoices.ihcuno, ' '),
                coalesce(invoices.ihorno, 0),
                coalesce(invoices.ihinvn, 0),
                coalesce(invoices.ihidat, 0),
                coalesce(invoices.ihiait, 0),
                coalesce(invoices.ihitta, 0),  
                coalesce(salesOrders.oswatp, ' '),
                coalesce(salesOrders.osprdc, ' '),
                coalesce(salesOrders.osseno, ' '),
                coalesce(salesOrders.ostecn, ' '),
                coalesce(orderType.type, ' ')
              Jim

              Comment


              • #8
                Thanks Jim.

                This is pretty much where I came from before using the common table expressions and coalesce which I'm pretty much using for the first time. I've always done CASE statements instead of COALESCE. I was hoping the common tables would speed things up as well as organize my code.

                The performs somewhere in between my original effort and the one I've post. I think I'm going to simplify the SQL and not try to go after so much information at once. I'll get the basics for orders and invoices in the SQL and then use RPG to CHAIN other files from there.
                Last edited by mjhaston; October 12, 2017, 06:59 AM.
                Your friends list is empty!

                Comment


                • #9
                  I'm probably just a crappy SQL coder, but I'm becoming less enchanted with VIEWS. I'm using this for a web application, so timing out is becoming an issue on the initial load. After the first load it's usually much faster, but if I switch to a different customer it seems to go back to the initial load time.

                  I switched back to just a simple READE and it was at least 5x faster.
                  Your friends list is empty!

                  Comment

                  Working...
                  X