ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Impact of LF's on performance - Dept discussion

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

  • Impact of LF's on performance - Dept discussion

    I searched this morning for an answer but could not find it. My question is lengthy, due to explanation.

    I had a heated conversation the other day with another programmer and a developer who knows almost nothing about DB2, Lf's or almost anything IBM i related. But uses CA PLEX to generate applications and their PF's are on the IBM i.

    We are running an IBM i at V7R3, model is a 41a.

    The issue was, a rogue LF in my user libe pointing back to a "Production" PF. The LF has not been touched by anything in 7 years, based on the Days used count from the obj desc. Granted, a file in a user libe pointing back to production was an oversight on my part, maybe due to a bad library list, but it was there none the less. The PF is used on a daily bases, but isnt large. About 100 thou records or so.

    The argument was, this one lone LF was having a negative impact on something she (the newby) was doing, simply by means of the fact its a logical file and it has to be maintained by the system.
    The access path maint parm is default of "*IMMED" and she thought the LF is re-indexed everytime a record in the file is added, changed or deleted.

    She asked me to "FIX IT", fix what, the the LF? pointing to the wrong place, delete the LF....WHAT ????

    So, when I tried to explain that this LF has no impact to her process or any process currently running, she called out to the other programmer, who said I was wrong and that the LF has to be re-indexed everytime a record is added, changed or deleted. My response was "Not exactly true" due to the access path maint parm of *IMMED and that the system maintains those changes "on the fly" unless the parm is not *immed, like *DLY, *REBLD. And another thing, there are maybe 10-15 LF's over this on PF, due to how CA/PLEX generates them.

    Am I absolutely wrong on my understanding of how LF's are maintained and the impact they could have on a system ????

  • #2
    The LF is maintained - but not "re-indexed". Many many many many years ago they did impact performance and the suggestion was to have no more than 10-15 LF's against a PF. That recommendation has since been lifted as the paradigm has shifted with the advent of using files created by SQL rather than DDS and indexes (essentially LF) are used to improve performance.

    The only "fix" would be to change the PF and subsequent views/index over to SQL rather than DDS - this would make the most use of the performance enhancements of the database engine...

    However, thee isn't any significant performance issues with your LF - she'll never be able to measure any difference in database performance with or without the LF - with the very real possibility she might see a DEGREDATION of performance if one of her SQL statements utilizes that LF in the background... in which case deleting it will cause a performance hit as the system will then have to create a path before processing her request.

    Comment


    • #3
      Originally posted by Rocky View Post
      The LF is maintained - but not "re-indexed". Many many many many years ago they did impact performance and the suggestion was to have no more than 10-15 LF's against a PF. That recommendation has since been lifted as the paradigm has shifted with the advent of using files created by SQL rather than DDS and indexes (essentially LF) are used to improve performance.

      The only "fix" would be to change the PF and subsequent views/index over to SQL rather than DDS - this would make the most use of the performance enhancements of the database engine...

      However, thee isn't any significant performance issues with your LF - she'll never be able to measure any difference in database performance with or without the LF - with the very real possibility she might see a DEGREDATION of performance if one of her SQL statements utilizes that LF in the background... in which case deleting it will cause a performance hit as the system will then have to create a path before processing her request.
      It simply was a disagreement "HEATED" for no reason, because one programmer told her that there would be issues. And those issues were simply tied to the fact that there is a LF pointing back to "HER" PF or a PF she was updating... Clearly I thought I had a good understanding, not as much as you explained, but that this one file in no way was causing her issues, but because someone else disagreed with me, Im wrong and they must be correct.

      And since it wasnt touched by anything in 7 years, I deleted it......

      BTW - Thank you !!!

      Comment


      • #4
        I will chime in here and also agree with Rocky...the LF would likely have an "unmeasurable" impact. If you drop the logical and her SQL statements degrade because the DB advisor was using that LF to retrieve data...then I guess she is SOL...LOL

        Comment


        • #5
          And since it wasnt touched by anything in 7 years, I deleted it......
          Well - you don't really know that. The date last used,etc is only referencing if it's been used by user code. That is - was it opened by an RPG/COBOL/JAVA program directly. What you don't know is whether the underlying database engine ever used it for performance reasons. Now - you might know if the same person comes back and says that it's running noticeably slower.

          The paradigm with SQL shifted the dynamics. The system behind the scenes will determine what you're doing, determine what type of access path gives it the quickest implementation, seeks out an LF with an access path that qualifies - if it's found it uses that LF to perform it's task. If it does not it has to essentially create a LF in temporary storage... and then uses the one it just created. How if it's 5 records... who cares? If it's 500,000 then that's a whole different story.

          Comment


          • #6
            My response is true if the job is using SQL to perform it's function. If it's opened via RPG/COBOL/JAVA it would have o bearing unless it opens that LF directly.

            Comment


            • #7
              I'll guarantee you that she won't see ANY difference in performance - 100 records is nothing

              Comment

              Working...
              X