BP1220 4GL Programming for Performance

1
BP12204GL Programming for Performance

• Dan Foreman
• BravePoint Inc.
• danf_at_prodb.com

2
Introduction- Dan Foreman

• Progress user since 1984
• Guest speaker at Progress Users Conference from
  1990-1998 and 2002-2003

3
Introduction- Dan Foreman

• Author of
• Progress Performance Tuning Guide
• Progress Database Administration Guide
• Progress Virtual System Tables
• V9 Database Administration Jumpstart
• Online Availability for each Publication
• ProMonitor - Performance Monitoring Tool
• Pro DL - Fast Dump/Load Utility
• Progress DB Admin Resource Kit
• STGEN

4
Who Are You?

• Progress V6, V7, V8, V9
• Production Database OS? Unix, Linux, NT
• Largest Single Database
• Largest Concurrent User Count

5
Goals

• Learn some (hopefully) new Techniques for
  Improving 4GL Performance
• Share with you some Benchmarks that I have
  performed

6
Benchmarks

• All Benchmarks were performed on V9.1B or above
  (most commonly V9.1D)
• For Benchmarks performed in Loops, the Loop
  overhead was factored out
• Run each Benchmark a Minimum of Four Times to
  Guarantee Consistency

7
Transactions

• FOR EACH trx EXCLUSIVE
• DELETE trx.
• END.
• Simple and easy to code
• But very slow
• One Transaction start stop per Record

8
Transactions

• REPEAT TRANSACTION
• FOR EACH trx EXCLUSIVE cnt 1 to 500
• DELETE trx.
• END.
• IF NOT CAN-FIND ( FIRST trx ) THEN LEAVE.
• END.
• One Transaction start stop per 500 Records
• Larger counter bigger BI more record locks

9
Transactions

• Apply the same method to loading records
• main-blk
• REPEAT TRANSACTION
• REPEAT cnt 1 TO 500
• ON ENDKEY UNDO, LEAVE main-blk
• CREATE trx.
• IMPORT trx.
• END.
• END.

10
Counting Records

• DEF QUERY q FOR order SCROLLING.
• OPEN QUERY q PRESELECT
• EACH order NO-LOCK.
• DISPLAY NUM-RESULTS(q).
• But a proutil dbanalys is usually faster and
  gives you more information
• V9 dbanalys can be run by Area (undocumented
  feature)

11
FIND versus FOR

• A FOR can use Multiple Indexes FIND Cannot
• A FOR can use Field Lists FIND Cannot
• A FOR can use a Word Index FIND Cannot
• But on the Other Hand
• FIND can use the NO-WAIT Option
• FIND can use the CURRENT Option

12
FIND versus FOR

• FOR was 25 Slower than FIND with a Single
  Record, Unique Index Retrieval

13
Optional EACH

• Which is Better?
• FOR EACH orderline, item OF orderline
• or
• FOR EACH orderline,
• EACH item OF orderline

14
Optional EACH

• FOR EACH orderline, item OF orderline
• Assumes an item Record MUST Exist for each line
  error if not matching record
• 25 Faster than EACH item OF orderline
• Helps document the one-to-one relationship
• OPEN QUERY requires a EACH, FIRST, or LAST
• Use FIRST item for a 12-14 gain

15
TEMP-TABLEs

• In V9 use EMPTY TEMP-TABLE
• Unfortunately if the DEFINE TEMP-TABLE uses
  NO-UNDO, the EMPTY TEMP-TABLE Statement cannot be
  used inside of a TRANSACTION

16
TEMP-TABLEs

• Starting in V9 TEMP-TABLE performance decreased
  (12-20 in benchmarks)
• This is because the DBI file Block size was
  changed to 8k and the Records per Block to 128
• The change was reversed in V9.1C05

17
Loops

• DO Loop of 1,000,000 Iterations
• W2K V8.3B 11.1 seconds
• W2K V9.1D 7.3 seconds (34 faster)
• Solaris V8.2A 22.7 sec
• Solaris V9.1D 25.1 sec (10 slower)
• V9.1D is slower on HP/UX and Linux also

18
Loops

• Nested DO Loop (1,000 outer 1,000 inner)
• W2K V8.3B 11.0 seconds
• W2K V9.1D 10.8 seconds (2 faster)
• Solaris V8.2A 26.7 sec
• Solaris V9.1D 32.5 sec (18 slower)
• V9.1D is slower on HP/UX and Linux also

19
Loops

• Dont use a DECIMAL Variable for a Loop Counter
• 29 Slower

20
WORKFILE WORK-TABLE

• 100 Memory Resident
• No Indexes FIND FIRST followed by FIND LAST
  requires a record by record scan
• If the record size is increased enough, a new
  record needs to be added pushing all the
  subsequent records down one slot
• Starting in V7 (through V9) you can potentially
  crash a SYSTEM with 3 lines of code do you know
  what those are?

21
Record Copying

• For copying data between records from Fastest to
  Slowest
• RAW-TRANSFER (5.2X Faster than BUFFER-COPY but
  has some restrictions like unique key collisions
  when used in the same table)
• BUFFER-COPY (34 Faster than ASSIGN)
• ASSIGN (slowest)

22
NO-ERROR

• Adding NO-ERROR adds Overhead
• RUN ltprocgt NO-ERROR is 4 Slower
• VALIDATE NO-ERROR is 4 Slower
• FIND NO-ERROR is less than 1 Slower

23
Data Loading

• SET versus IMPORT
• READKEY versus RAW GET-STRING/BYTE
• quoter Utility versus IMPORT UNFORMATTED

24
ASSIGN Statement

• ASSIGN for Multiple Assignments
• a b.
• c d.
• versus
• ASSIGN
• a b
• c d.

25
Character Comparisons

• CAN-DO Function
• LOOKUP Function
• INDEX Function
• OR
• OR is the Worst
• But NOT in a WHERE Clause with an indexed field

26
Arrays

• Array Initialization array-name
• 3.5X slower for a 12 element CHAR Array
• 383X slower for a 365 element INT Array

27
Locking

• NO-LOCK - especially with Remote Clients (-H and
  -S Connection)
• EXCLUSIVE-LOCK versus SHARED-LOCK when Updating a
  Record

28
Other Coding Techniques

• SHARED BUFFER or BUFFER PARAMETER versus passing
  RECID/ROWID
• NO-UNDO
• DEF VARIABLE
• DEF TEMP-TABLE/WORKFILE
• DEF PARAMETER

29
Other Coding Techniques

• CASE versus Nested IF THEN ELSE
• 32 for 2 Comparisons
• 70 for 10 Comparisons
• Database Sequences versus Control Record
• 13-33 Faster
• Remember the Limit is 2,147,483,647

30
Other Coding Techniques

• CAN-FIND versus FIND
• 31-37 Faster on Indexed Field
• No Difference between FIND NO-ERROR on a
  Non-Indexed Search
• Disables Query by Server Functionality

31
Startup Options

• -noshvarfix
• Undocumented (except for PPTG)
• V8.3C and above
• Reverses a bug fix
• Why would we want to do that?
• It wont work for everyone
• "ltproceduregt Shared variable ltvariablenamegt has
  not yet been created. (392)"

32
Startup Options

• Quick Request (-q)
• Especially if the R-Code resides on a different
  computer
• Not for developers
• Important with Trigger Firing
• A FIND Trigger was 42 Faster with -q

33
The Best Way to Find Slow Code

• Use -yx to find slow programs in a session
• Use this to locate which program is slow
• Use the Undocumented Application Profiler
• Use this option to find what statements are slow
• -zprofile in V8
• -profile in V9
• See DLC/src/samples/profiler/readme.doc for
  details

34
Conclusion

• Questions?
• Thank You For Coming!
• Please Dont Forget Your Evaluations