Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

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

  • Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

    Establishing logical files for on-line processes is more efficient, simply because the path already exists, and does not have to be built. Using OPNQRYF or SQL should not matter in terms of CPU efficiency, because both call the QQQUERY API. The manner in which either option is used, will make a distinct difference in overall efficiency. Dave

  • #2
    Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

    If you are running models 7xx or before, you will improve your machines performance drastically by avoiding using SQL in QINTER. Performance falls apart when the CWP governor kicks in. We have had enough hassles on our 730, we don't use SQL for anything more than we absolutely have to in QINTER. On our single CPU processor it trashes interactive performance, as it has on many systems I have worked on. Around here the watchword is run all queries in the batch Queue and make as many interactive I-O use LF. Opnqryf is old technology and much more cryptic so we are using SQL instead of it to process queries. Dwight

    Comment


    • #3
      Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

      Even SQL and OPNQRYF run much faster if you have a LF preexisting with the same keys as your QRY. QRY400 does it's own thing no matter what exists. Dwight

      Comment


      • #4
        Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

        Yes, I have heard that. Thanks for the reminder! We have noticed that if you're joining several files together in an OPNQRYF, and one of them happens to be a LF, the performance of the query is quite horrible. When we've encountered this, we replace the LF(s) with the PF(s) and the prefomance improves tremendously. Thanks for your help Dwight.

        Comment


        • #5
          Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

          Bird, The discussion about SQL vs. LF vs OPNQRYF is trivial compared to the HUGE boost in performance you'll get by using Encoded Vector Indexes (EVI) on your files. Once you create an EVI over a field, which can only be done in SQL, you will see a significant change in response time when processing files no matter which way you access the files. We've seen access times reduced from 45 seconds to less than 1/2 a second! One of our weekly jobs that literally took around 40 hours to process was reduced to under an hour! EVIs are simply magic. If you are all interested in reducing processing time learn as much as you can about creating and using EVIs. BTW, once you create an EVI all methods of access in this discussion will use the EVI for quick access. Why this is kept such a secret is a mystery to me. I can only conclude that IBM wants it to be a secret because using EVIs will stall the upgrade process. chuck Opinions expressed are not necessarily those of my employer. "birdschmitt" wrote in message news:6b2522ff.-1@WebX.WawyahGHajS... > Here in our shop, there has been much discussion of the processing speeds > of SQL versus OPNQRYF. Has anybody ever run any tests, or can refer me to > any articles where this has been discussed? Currently, we utilize OPNQRYF > quite extensively, mostly in CL programs, and wonder if replacing them > with SQL or perhaps Logical Files can result in significant gains in > processing speed. Can processing times depend on or vary for jobs running > in Batch or Interactive modes? > > We believe that it used to be true that Logical Files carry some pretty > significant system overhead baggage. Is this still true? I seem to recall > hearing that enhancements have been made to them over the years that this > is really no longer as great a concern. We're currently running V5R2. > > Thanks for any help that you may have to offer, and I apologize if this > topic is discussed elsewhere.

          Comment


          • #6
            Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

            This is a post from Mike Cravitz a few years ago. It's still accurate except now the SQE (SQL Query Engine) is faster than the CQE (Classic Query Engine) (V5R2?). ----Snip---- For Embedded SQL -vs- OPNQRYF, SQL Cursors win hands down!
            1. Both use the same database engine, so neither has an advantage over the other performance-wise. (see note above about SQE/CQE -CR).
            2. OpnQryF pretty much requires a CL program (or a very determined set of calls to something like QCMDEXC), SQL cursors don't.
            3. The Declare Cursor statement when coded properly allowing things to line up properly is far easier to decipher than staring for hours at a particular OpnQryF command.
            4. Here's a big one. SQL cursors do not require you to do the hokey pokey with that silly format file whenever you are using mapped fields or whatever.
            5. SQL cursors are a cross-industry standard technique. Ask somebody in a UNIX shop if they've heard of OPNQRYF.
            ----End-Snip---- So... even if performance is the same, SQL makes more sense than OPNQRYF. And if you use SQL, typically specify the PF, not a LF name. Let the SQE figure out the best access path to use. A pre-existing access path or EVI as Chuck suggests may definitely speed things up. If you put your program in debug, the job log will be littered with lots of useful suggestions for what pre-existing AP's would speed the query up. Chris

            Comment


            • #7
              Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

              Dwight HoganCamp wrote: > If you are running models 7xx or before, you will improve your > machines performance drastically by avoiding using SQL in QINTER. Is this because the Enterprise machines come with full Interactive CPW? I believe even in this instance attention should be considered as to a batch job's Run Priority and Timeslice, the amount of memory allocated to the subsystem would be a factor as well. > Performance falls apart when the CWP governor kicks in. We have had > enough hassles on our 730, we don't use SQL for anything more than we > absolutely have to in QINTER. On our single CPU processor it trashes > interactive performance, as it has on many systems I have worked on. > Around here the watchword is run all queries in the batch Queue and > make as many interactive I-O use LF. To singularly mention SQL and not OpnQryF is hopefully just an oversight because they would impact the system equally. Bill

              Comment


              • #8
                Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

                I thought EVIs were useful over relatively static data. Is this true or are they worthwhile even for files with a lot of adds, deletes? Tom D.

                Comment


                • #9
                  Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

                  Tom Daly wrote: > I thought EVIs were useful over relatively static data. Is this true > or are they worthwhile even for files with a lot of adds, deletes? The way I remember it is that it's most beneficial where there are a low number of unique values. Things like warehouse numbers, states and the like are great candidates for EVI, but order numbers might not be. Bill

                  Comment


                  • #10
                    Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

                    Bill, That's my understanding too. For example, a native timestamp field would be a terrible candidate for an EVI. Chris

                    Comment


                    • #11
                      Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

                      EVIs are useful everywhere, not just static values. We use them heavily over our transaction files ( >10 million records) and it does wonders. It'd most beneficial to create EVIs over a single field, but it's also can be beneficial over multiple fields but not as dramatic. We create separate EVIs over different fields in the same file. You should create some EVIs to see how your apps respond. Your applications need do nothing to take advantage of EVIs. When a file is opened the system looks to see if an EVI is present. chuck Opinions expressed are not necessarily those of my employer. "Tom Daly" wrote in message news:6b2522ff.7@WebX.WawyahGHajS... >I thought EVIs were useful over relatively static data. Is this true or are >they worthwhile even for files with a lot of adds, deletes? > > Tom D.

                      Comment


                      • #12
                        Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

                        Tom Daly wrote: > I thought EVIs were useful over relatively static data. Is this true > or are they worthwhile even for files with a lot of adds, deletes? Here's what the V5R2 manual says about beneficial conditions for EVI indexes: "Encoded vector indexes should be considered when you want to gather statistics, when full table scan is selected, selectivity of the query is 20%-70% and using skip sequential access with dynamic bitmaps will speed up the scan, or when a star schema join is expected to be used for star schema join queries. Encoded vector indexes should be created with: a.. Single key columns with a low number of distinct values expected b.. Keys columns with a low volatililty (they don't change often) c.. Maximum number of distinct values expected using the WITH n DISTINCT VALUES clause d.. Single key over foreign key columns for a star schema model " Bill

                        Comment


                        • #13
                          Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

                          When EVI's were first released, there was a potential performance problem with them. IBM recommended using them sparingly and on relatively static data. However, a couple of releases back this issue was largely solved. This would have been in V4R5 or V5R1, I believe. There is another possible performance hit. If you underestimate the number of distinct values in the column you are indexing, you could trigger a complete EVI rebuild during runtime since the EVI needs to be recreated with a larger field width. This is a one-time deal however, and so it's difficult to seriously mess up on this one. Finally, as with all indexes, you should be reasonable. Indexes require resources to maintain. Generally they are a very good thing, but some people get the idea that more is always better, so they index every column of every table. This is an extremely bad idea!!

                          Comment


                          • #14
                            Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

                            I have not worked with this type of index before. I am really excited about what you have said here. Can you tell me, does the system just use them in SQL and OPNQRY, or does it also work with queries created in WRKQRY?

                            Comment


                            • #15
                              Processing efficiency comparisons SQL vs OPNQRYF vs Logical files

                              Khodge, The system will use an EVI whenever it can when a file is opened. RPG, SQL, Query, etc. To create one and give it a try go into STRSQL press F4, take the Create an Index option and choose type of 4 for encoded vector index. chuck Opinions expressed are not necessarily those of my employer. "Khodge" wrote in message news:6b2522ff.13@WebX.WawyahGHajS... >I have not worked with this type of index before. I am really excited about >what you have said here. Can you tell me, does the system just use them in >SQL and OPNQRY, or does it also work with queries created in WRKQRY?

                              Comment

                              Working...
                              X