HP 3000 Manuals

ALLBASE/SQL Performance Guidelines

Table of Contents

 ALLBASE/SQL Performance Guidelines
   ALLBASE/SQL Performance Guidelines : COPYRIGHT NOTICE

   Ch 1.  Basic Concepts in ALLBASE/SQL Performance
      DBEFile Organization
          Page Organization
          Page Table Pages
          Rows of Data on Pages
          Structure of a Page
          Storage of Table Data on DBEFile Pages
             Slot Table
          Indirect Rows
          Hash Storage
          Page Compression
          Storage of Index Data on DBEFile Pages
          How Indexes are Used
          How PCRs are Stored
          Page Splitting
      Data Buffering
      System Catalog
          Directory Caching
      Log File Organization
      Log Buffering
          No-Log Pages
      Locking and Latching
          Locks
          Latches
          Pins
          Sequence of Events in Locking Data
      Sorting
      Optimization
          How Optimization is Done
             Table Size
             Selectivity
             Index Size
             Cluster Count
          Using GENPLAN
      Sections and Validation
          Section Caching
          Validation

   Ch 2.  Guidelines on Logical and Physical Design
      Logical Data Design
          Normalization Issues
             Denormalizing Tables that are Consistently Joined
             Horizontal Partitioning
             Vertical Partitioning
          Including Calculated Data in Tables
          B-Tree Index Design
             Choosing Keys
             Building Indexes on Large Tables
             Maintaining Indexes
          Clustering Indexes
             Using Clustering Indexes
             Monitoring the Cluster Count
             Reclustering a Table
             Using Hash Structures
          Choosing Appropriate Index Types
             Updating Statistics
          Authorization Design
          Using a Duplicate Database
      Physical Data Design
          Creating DBEFileSets
             Avoiding the SYSTEM DBEFileSet for User Data
             Placing Large Tables in Separate DBEFileSets
             Gathering Small Tables into DBEFileSets
          Creating DBEFiles
             Avoiding Extra DBEFile Space
          Creating Tables
             Avoiding NULL and Variable Length Data
             Using INTEGER Rather than SMALLINT Data
          Initial Table Loads
          Unloading Data
          Unloading and Reloading to Remove Indirect Rows
          Unloading and Reloading to Remove Overflow Pages
          Tips on Deletions from Tables

   Ch 3.  Guidelines on Query Design
      Avoiding Serial Scans Through Query Design
          Arithmetic Expressions
          Columns from One Table on Both Sides of the Relational Operator
          Data Conversions
          Predicates with INTEGER = DECIMAL(n,0) Factors
      Using Subqueries
      Using UNION
          Avoiding Conversions
          Defining Indexes for UNION Queries
      Using MIN/MAX Functions in Predicates
      Using OR Predicates
          How OR Predicates are Optimized
          Choosing an Index for OR Factors
      Using Predicates with LIKE
      Using Predicates with BETWEEN
      Using Fetch Unique Scans
      Updating Key Columns
      Avoiding User Propagation of Filters
      Using TID Scans
      Using the BULK Option
      Analyzing Queries with GENPLAN

   Ch 4.  Guidelines on Transaction Design
      General Tips on Managing Transactions
          Using Short Transactions and Savepoints
      Controlling Locking
      Using CS, RC, and RU Isolation Levels
      Using Row Level Locking
          Benefits of Row Level Locking
          Shared Memory Considerations
          Page Locking on PUBLICROW Tables
      Using KEEP CURSOR
      Removing Non-Database Processing from Transactions
      Using Procedures and Rules
      Tuning Performance of Dynamic Statements
          Using Dynamic Parameters Within a Transaction

   Ch 5.  Guidelines on System Administration
      DBA Guidelines
          Avoiding Runtime Revalidation of Sections
          Developing Application Programs
          Load Balancing
             Placing Concurrently Used Objects on Different Drives
          Calculating Shared Memory Allocation
          Choosing a Number of Data Buffer Pages
             Basic Example
             First Threshold for Performance Gain
             Second Threshold for Performance Gain
             Cautions
          Choosing a Number of Log Buffer Pages
          Choosing the Number and Size of Log Files
             Nonarchive Log Guidelines
             Archive Log Guidelines
          Sorting Operations
             Using TempSpaces
             Tips for TempSpace Usage
             Disk Space for Sorting
             Controlling the Use of Temporary Space
             Memory Utilization in Sorting
             Performance Hints for Large Sorts
             Use of the Sort/Merge Join Algorithm
             Temporary Space in the SYSTEM DBEFileSet
             Section Caching and Directory Caching
          Setting Limits for Section Caching
          Using Multi-connect Functionality
          Using Timeouts to Tune Performance
      Network Guidelines
      HP-UX System Guidelines
          Using HP-UX Raw Files for DBEFiles and Logs
      MPE/iX System Guidelines
          Using Pseudo-Mapped DBEFiles in MPE/iX
          Using Memory-Resident Data Buffers

   Ap A. Design for a High-Performance Interactive Table Editor
      Example Table
      User Interface
      Internal Algorithms
          SELECT
          DELETE
          UPDATE
   INDEX
       Index

MPE/iX 5.0 Documentation