DataServer Best Practices Thinking Like a SQL Engine David Moloney DataServer Architect, OpenEdge Division of Progress Software Corp. Session 107

© 2009 Progress Software Corporation DataServer Best Practices Are Your Map Goals Maximize ABL conformance Minimize loss in performance 2

© 2009 Progress Software Corporation SQL Strengths and Features 3 SQL StrengthsSQL Features Cost Analysis and query optimization Data Types: fixed and variable length Cursor consistency and isolation levels Procedures and Sequences Native securityTriggers and constraints Transaction Control Schemas, naming conventions, qualifiers Lock ManagementNetworking Protocols Prepared statements and indexingNULL capability Set-based DesignThe SQL language

© 2009 Progress Software Corporation The Landscape: OpenEdge DB vs. SQL DB Environment Architectural Comparison Common Schema ABL instructions are dispatched 3 rd Party access layer added OLTP-based ABL set-based SQL 4 OpenEdge Client OpenEdge Database For Each Customer: Display name. END. 4GL For Each Customer: Display name. END. 4GL OpenEdge Database Oracle SQL Server ABL SQL DataServer DatabaseSchema Holder ODBC OCI Database Customer Gates Ellison

© 2009 Progress Software Corporation Agenda Best Practice Part 1: Conformance ABL for OpenEdge ABL for OpenEdge DataServers Best Practice Part 2: Performance Thinking like a SQL engine 5

© 2009 Progress Software Corporation Read Programming Considerations in your DataServer guide carefully Best Practice: Conformance 6 1. FIND cust WHERE name BEGINS DO. 2. FOR EACH cust WHERE name < CHR(126). 3. FOR EACH cust WHERE name Bob. Name Client SortServer Sort CHR(52) CHR(53) CHR(126) CHR(52) CHR(53) Name OpenEdgeForeign DB ?NULL ? BOB Name DO DOG DONE The Data: Name DO Name DO DOG DONE OpenEdge Results: DataServer Results: The Data: Name ? BOB Name BOB OpenEdge Results: DataServer Results: The Data: OpenEdge Results: Name CHR(52 CHR(53) CHR(126) DataServer Results: Name

© 2009 Progress Software Corporation Be explicit when you want to write a record Best Practice: Conformance 7 DO: DEFINE BUFFER xcust FOR cust. CREATE cust. ASSIGN cust-num = 111. FIND xcust WHERE cust.cust-num = 111. END.

© 2009 Progress Software Corporation Best Practice: Conformance Be explicit when you want to write a record VALIDATE RELEASE RECID/ROWID END (TRANSACTION) COPY-LOB 8 DO: DEFINE BUFFER xcust FOR cust. CREATE cust. ASSIGN cust-num = 111. VALIDATE cust. FIND xcust WHERE cust.cust-num = 111. END.

© 2009 Progress Software Corporation Be explicit when you want to write a record Watch out for record scoping & Transaction subtleties Best Practice: Conformance 9 REPEAT: FIND FIRST cust. ASSIGN cust-num = 1. sub-trans-block: DO ON ERROR UNDO do-blk, RETRY do-blk: UPDATE state. END. END. DISPLAY state.

© 2009 Progress Software Corporation Conformance: control activity between write and commit Performance: Limit lock exposure Best Practice: Conformance & Performance 10 DO TRANSACTION: DEFINE BUFFER xcust FOR cust. CREATE cust. ASSIGN cust-num = 111. VALIDATE cust. FIND xcust WHERE cust.cust-num = 111. /* Exposure to contention !!! */ UPDATE order. UPDATE order-line. CALL STORED-PROC verylongproc(). /* Exposure to contention !!! */ END.

© 2009 Progress Software Corporation Best Practice: Conformance Have a strategy for SQL Width 11 FOR FIRST cust EXCLUSIVE-LOCK: ASSIGN name = FILL(a,35) NO-ERROR. VALIDATE NO-ERROR. IF error-status:error THEN DO: UNDO, LEAVE. END. END. If OE CHAR name SQL VARCHAR(30) name

© 2009 Progress Software Corporation Code for lock and cursor release at transaction boundaries Best Practice: Conformance 12 DEF VAR num AS INT INIT 103. DO TRANSACTION: FIND cust WHERE cust = num EXCLUSIVE-LOCK. ASSIGN name = Bob. END. FIND cust WHERE cust-num = num. DISPLAY name.

© 2009 Progress Software Corporation Dont assume buffers reflect accurate record state RELEASE set-based buffers that should be out of scope Best Practice: Conformance 13 DO TRANSACTION: FIND cust 5 EXCLUSIVE-LOCK. ASSIGN name = Bob. END. FOR EACH cust: /* IF cust-num = 5 THEN LEAVE. */ END. /* DISPLAY cust */ IF AVAILABLE cust THEN MESSAGE found it. RELEASE cust.

© 2009 Progress Software Corporation Performance: Optimize ABL, DataServer, Driver, DBMS Architectural Comparison 14 OpenEdge Client OpenEdge Database For Each Customer: Display name. END. 4GL For Each Customer: Display name. END. 4GL OpenEdge Database Oracle SQL Server OLTP SQL DataServer DatabaseSchema Holder ODBC OCI Database Customer Gates Ellison

© 2009 Progress Software Corporation Best Practice: Performance Avoid FIND statements Classic OLTP tool, but not SQL friendly 15 FIND FIRST cust WHERE cust-num = 5 DEF VAR Bufhdl AS HANDLE. Bufhdl = BUFFER cust:HANDLE. Bufhdl:FIND-FIRST(WHERE cust-num = 5, NO-LOCK). FIND cust FOR EACH cust: END. FIND LAST cust FOR LAST cust:END. FIND FIRST cust FOR FIRST cust:END.

© 2009 Progress Software Corporation 16 Best Practice: Performance OPEN QUERY q FOR EACH Customer NO-LOCK. REPEAT: GET NEXT q. IF NOT AVAILABLE Customer THEN LEAVE. Cnt = Cnt + 1. END. CLOSE QUERY q. Seek set-based FIND alternatives FIND FIRST Customer NO-LOCK NO-ERROR. IF AVAILABLE Customer THEN Cnt = 1. REPEAT: FIND NEXT Customer NO-ERROR. IF NOT AVAILABLE (Customer) THEN LEAVE. Cnt = Cnt + 1. END. Alternatives FOR EACH QUERY Dynamic FIND INDEXED- REPOSITION

© 2009 Progress Software Corporation 17 Best Practice: Conformance Avoid FIND statements 1. FIND cust WHERE cust-num = 123. Records: Customer Cust-numName 123Ben 124Bill 125Bob 2. FIND cust WHERE name = Bob FIND cust WHERE name = Burt. 1.

© 2009 Progress Software Corporation Performance: Avoid SHARE locks Conformance: Be explicit about locking Best Practice: Conformance & Performance 18 FOR FIRST cust: /* implicit SHARE-LOCK */ FOR FIRST cust SHARE-LOCK : FOR FIRST cust NO-LOCK: FOR FIRST cust EXCLUSIVE-LOCK:

© 2009 Progress Software Corporation Conformance: Always pick an Index if order matters Performance: Dont pick an index if order doesnt matter Best Practice: Conformance & Performance 19 FOR EACH customer: END. FOR EACH employee USE-INDEX department: END. FOR EACH invoice BY zip-code: END.

© 2009 Progress Software Corporation Avoid un-resolvable server references 1. Forces client selection 2. Forces client sorting 3. Forces client join Best Practice: Performance 20 FOR EACH customer BY mnth_sales[x[1]] BY cust-num: FOR EACH customer, EACH order OF customer WHERE LENGTH(RIGHT-TRIM(customer.name)) > 5: FOR EACH customer, FIRST order OUTER-JOIN OF customer:

© 2009 Progress Software Corporation Best Practice: Performance Select an efficient ROWID ROWID can optimize database access ROWID is involved in most database operations: -Locking -Deletions & Updates -FINDs, Queries & Browsers -All cursor positioning such as INDEXED-REPOSITION -RECID/ROWID functions -random record access in a non-unique set 21

© 2009 Progress Software Corporation Leverage your SQL engine e.g., Use stored procedures Best Practice: Performance 22 UPDATE mytab1 x SET x.col1 = ( SELECT y.col1 FROM mytab2 y WHERE UPPER(y.col1) = UPPER(x.col1) );

© 2009 Progress Software Corporation Performance: Use field lists Conformance: Include all the fields you reference Best Practice: Conformance & Performance 23 FOR EACH customer FIELDS(cust-num name): FOR EACH customer EXCEPT(big-blob-field): FIND FIRST customer FIELDS(cust-num) WHERE CAN-FIND(FIRST order WHERE order.st=cust.st).

© 2009 Progress Software Corporation Best Practice: Performance Tune your environment Your application -Use QUERY-TUNING where appropriate -Use –Dsrv connect options -Use client startup options Your database -SQL Engine -I/O capacities -Application Interfaces 24

© 2009 Progress Software Corporation Best Practice: Conformance Code around un-reconcilable differences 25 &GLOBAL-DEFINE DB-TYPE ORACLE &IF NOT DEFINED ({&MSSQLS}) &THEN … IF DBTYPE(dbname) = PROGRESS THEN RUN OpenEdge-database-code ELSE RUN DataServer-foreign-interface-code

© 2009 Progress Software Corporation Best Practices Summary Walk before you run First obtain conformance: Eliminate/Mitigate differences in application behavior Then optimize for performance Think like a SQL engine 26

DataServer Best Practices Thinking Like a SQL Engine David Moloney DataServer Architect, OpenEdge Division of Progress Software Corp. Session 107