ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQLRPGLE Procedure or SQL View?

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

  • SQLRPGLE Procedure or SQL View?

    Our customer database does not store year-to-date sales in a physical file. However, this is something that we use quite frequently. So I'm considering two solutions.
    1. Add a procedure to an existing SQLRPGLE service program to sum the figure based on customer number passed in, OR
    2. Create an SQL view that sums for all customers

    Running the interactive SQL statement seems nearly instantaneous... I think I would have more flexibility with the service program. I could have gross sales & net sales (less returns) in the same service pgm.

    Just looking for opinions on the different approaches.
    Greg

  • #2
    If it were me - I'd create a view as that would be easily used at any point with no need to call a special routine - keep the KISS approach.

    Comment


    • #3
      (Keep It Short and Simple)

      Comment


      • #4
        Originally posted by Rocky View Post
        (Keep It Short and Simple)
        I always thought that was "Keep It Simple, Stupid"

        Comment


        • #5
          I'll agree with Rocky. The reason, views are consumable by all methods. HLL, SQL, mostly query, etc..

          Jim

          Comment


          • #6
            Originally posted by gwilburn View Post

            I always thought that was "Keep It Simple, Stupid"
            Well - I didn't really want to call the poster stupid because that wouldn't be true...

            It can also be "Keep It Short and Sweet" among others...

            Comment


            • #7
              Thanks for the input... I think I may actually do both.

              The service pgm gives me the ability to have a multi-use "function" that I can use in the many RPG pgms we have.
              The view (for the reasons above)...

              From my limited understanding, it's quite possible that the SQE will actually "find" the view - so the service program might end up using it as well.

              Comment


              • #8
                The view does not confer any SQL efficiency, it just takes a complex SQL statement and represents it like a single table to make your code neater, and to make it easier to reuse. Indexes are what make lookup more efficient. So the view's existence does nothing unless you explicitly use the view in your code.
                So you would be creating an RPG function that uses the view

                Comment


                • #9
                  Originally posted by gwilburn View Post
                  Thanks for the input... I think I may actually do both.

                  The service pgm gives me the ability to have a multi-use "function" that I can use in the many RPG pgms we have.
                  The view (for the reasons above)...

                  From my limited understanding, it's quite possible that the SQE will actually "find" the view - so the service program might end up using it as well.
                  As Vector points out - the SQE doesn't look for or find any view unless you specifically use it. Indexes are what you are thinking of. Indexes are used to provide efficient access to the data for a given purpose. The SQE does look to see if there are any indexes that give the desired access, if not creates a temporary index. If you use a temporary index a lot it usually benefits you to create a permanent index because relatively speaking creating temporary index is is expensive in terms of performance. If the file contains 5 records - who cares, if it's 1,000,000 it can be very significant.

                  Comment

                  Working...
                  X