Derby
  1. Derby
  2. DERBY-3259

Tuning manual needs performance tip on compressing tables

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.3.1.4
    • Fix Version/s: 10.6.1.0
    • Component/s: Documentation
    • Labels:
      None

      Description

      The "Performance Tips and Tricks" section of the Tuning Derby manual needs a topic that recommends the use of the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure to resolve performance problems having to do with indexes and complex table joins. (Please rephrase this if I haven't put it correctly.)

      For details, see the discussion at http://www.nabble.com/Re%3A-FW%3A-Advice-on-*very*-badly-performing-query-tf4939807.html.

      1. docs.diff
        6 kB
        Bryan Pendleton
      2. ctunstats849505.html
        4 kB
        Bryan Pendleton
      3. ctunperfstatistics.html
        6 kB
        Bryan Pendleton
      4. docs.diff
        6 kB
        Bryan Pendleton
      5. ctunstats849505.html
        4 kB
        Bryan Pendleton
      6. ctunperf25864.html
        7 kB
        Bryan Pendleton
      7. ctunperfstatistics.html
        5 kB
        Bryan Pendleton

        Activity

        Hide
        Bryan Pendleton added a comment -

        The email thread mentioned in the description contains lots of good information that
        I can edit into the new doc topic.

        It seems like we also need a few notes regarding SYSCS_UPDATE_STATISTICS, and
        possibly also note that, negatively, SYSCS_INPLACE_COMPRESS_TABLE does not
        update the statistics. See DERBY-4289, DERBY-4121, and DERBY-1964 for related thoughts.

        I'll take a stab at creating a new "Ensure statistics are accurate" topic for the Performance Tips
        section of the Tuning guide, and post the results when I've got something ready to read.

        Show
        Bryan Pendleton added a comment - The email thread mentioned in the description contains lots of good information that I can edit into the new doc topic. It seems like we also need a few notes regarding SYSCS_UPDATE_STATISTICS, and possibly also note that, negatively, SYSCS_INPLACE_COMPRESS_TABLE does not update the statistics. See DERBY-4289 , DERBY-4121 , and DERBY-1964 for related thoughts. I'll take a stab at creating a new "Ensure statistics are accurate" topic for the Performance Tips section of the Tuning guide, and post the results when I've got something ready to read.
        Hide
        Bryan Pendleton added a comment -

        Attached is a proposed docs patch, and HTML formatted versions of
        the 3 modified files: "The Tips", "Ensure table statistics are accurate",
        and "When cardinality statistics go stale".

        I think that the information elsewhere in the tuning and ref guides is
        pretty complete; I think this new 'tips' page basically serves to alert
        users to this potential situation and to direct their attention to the
        relevant documentation.

        Please let me know what you think of the changes.

        Show
        Bryan Pendleton added a comment - Attached is a proposed docs patch, and HTML formatted versions of the 3 modified files: "The Tips", "Ensure table statistics are accurate", and "When cardinality statistics go stale". I think that the information elsewhere in the tuning and ref guides is pretty complete; I think this new 'tips' page basically serves to alert users to this potential situation and to direct their attention to the relevant documentation. Please let me know what you think of the changes.
        Hide
        Kim Haase added a comment -

        Thanks very much for picking this up, Bryan. The new topic is very clear and well-written. I have only a few minor non-technical suggestions.

        In the new text in "When cardinality statistics go stale", I'd suggest changing "info" to "information" where it occurs, to be consistent. Also, at the end of the second new paragraph, "procedures" should be changed to "procedure", since you're advising them to run one or the other procedure, not both.

        In the new topic, you might want to add an indexterm or two, just in case we are ever able to generate an index. Also, it seems polite for links to other topics to include the title of the topic in the link, just so that users know where exactly they are going. It's a matter of taste, though.

        Show
        Kim Haase added a comment - Thanks very much for picking this up, Bryan. The new topic is very clear and well-written. I have only a few minor non-technical suggestions. In the new text in "When cardinality statistics go stale", I'd suggest changing "info" to "information" where it occurs, to be consistent. Also, at the end of the second new paragraph, "procedures" should be changed to "procedure", since you're advising them to run one or the other procedure, not both. In the new topic, you might want to add an indexterm or two, just in case we are ever able to generate an index. Also, it seems polite for links to other topics to include the title of the topic in the link, just so that users know where exactly they are going. It's a matter of taste, though.
        Hide
        Bryan Pendleton added a comment -

        Hi Kim, thanks for the review and good suggestions. I think I've incorporated
        them into the latest attached patch proposal, and have also attached the
        updated HTML versions of the "Ensure statistics are accurate" and
        "When statistics go stale" topics.

        Show
        Bryan Pendleton added a comment - Hi Kim, thanks for the review and good suggestions. I think I've incorporated them into the latest attached patch proposal, and have also attached the updated HTML versions of the "Ensure statistics are accurate" and "When statistics go stale" topics.
        Hide
        Dag H. Wanvik added a comment -

        Thanks for making these improvements, Bryan. +1

        Show
        Dag H. Wanvik added a comment - Thanks for making these improvements, Bryan. +1
        Hide
        Kim Haase added a comment -

        Thanks a lot, Bryan! +1 from the doc end too.

        Show
        Kim Haase added a comment - Thanks a lot, Bryan! +1 from the doc end too.
        Hide
        Bryan Pendleton added a comment -

        Thanks Kim and Dag for the timely reviews. Committed to the documentation trunk as revision 898944.

        Show
        Bryan Pendleton added a comment - Thanks Kim and Dag for the timely reviews. Committed to the documentation trunk as revision 898944.
        Hide
        Kim Haase added a comment -

        Fixes appear in Latest Alpha Manuals, so closing.

        Show
        Kim Haase added a comment - Fixes appear in Latest Alpha Manuals, so closing.

          People

          • Assignee:
            Bryan Pendleton
            Reporter:
            Kim Haase
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development