Transcript END. Best Practice

B4: DataServer Best Practices
Revised & Revisited
Noel Shannon
Senior Solution Consultant
Under Development


D I S C L A I M E R
This talk includes information about potential
future products and/or product enhancements.
What I am going to say reflects our current
thinking, but the information contained herein is
preliminary and subject to change. Any future
products we ultimately deliver may be materially
different from what is described here.
D
2
I
S
C
L
A
I
© 2008 Progress Software Corporation
M
E
R
Agenda
The path to successful DataServer development & deployment
 DataServers
&
• Why ?
• When to use ?
• What are they ?
3
Best Practices
• Why ?
• When to use ?
• What are they ?
© 2008 Progress Software Corporation
DataServers: WHY ?
 Has a customer, your partners or your
company ever asked you:
“Does your OpenEdge Application run against”
MS SQL Server ?
Oracle® ?
Some other data source ?
4
© 2008 Progress Software Corporation
DataServers WHEN ?
 When “yes” on the WHY question would be
compelling
 “Yes” is “compelling”
when there’s a willingness to:
• “Invest” mind share in the foreign data source
• Re-examine parts of your OpenEdge application
• Compromise performance for conformance
… in light of a “compelling” business case,
corporate decision or market opportunity.
5
© 2008 Progress Software Corporation
What is a DataServer ?
 A “redirection” layer for database requests in
an OpenEdge client or agent.
 OpenEdge database request translation
 Interface layer communication with a foreign
data source
6
© 2008 Progress Software Corporation
What is a DataServer ?
OpenEdge
Database
7
© 2008 Progress Software Corporation
What is a DataServer ?
MS SQL
Server
8
© 2008 Progress Software Corporation
INT
VARCHAR
3
B
4
Gates
What is a DataServer ?
MS SQL
Server
INT
VARCHAR
3
B
4
Gates
Oracle
SQL Server
9
© 2008 Progress Software Corporation
INT
VARCHAR2
5
L
6
Ellison
What is a DataServer ?
MS SQL
Server
INT
VARCHAR
3
B
4
Gates
Oracle
SQL Server
10
© 2008 Progress Software Corporation
INT
VARCHAR2
5
L
6
Ellison
What is a DataServer ?
MS SQL
Server
(process)
INT
VARCHAR
3
B
4
Gates
Oracle
SQL Server
(process)
11
© 2008 Progress Software Corporation
INT
VARCHAR2
5
L
6
Ellison
What is a DataServer ?
12
OpenEdge
OpenEdge
Database
Database
© 2008 Progress Software Corporation
What is a DataServer ?
MS SQL
Server
INT
13
INT
VARCHAR
3
B
4
Gates
OpenEdge
Oracle
Database
SQL Server
CHAR
© 2008 Progress Software Corporation
INT
VARCHAR2
5
L
6
Ellison
Agenda
The path to successful DataServer development & deployment
 DataServers
&
• Why √
• When to use √
• What are they √
14
Best Practices
• Why ?
• When to use ?
• What are they ?
© 2008 Progress Software Corporation
WHY Use DataServer Best Practices ?
 We’re twice as likely to avoid pain than to
pursue gain
 Why aren’t DataServers just painless
“load and go” ?
15
© 2008 Progress Software Corporation
WHY Use DataServer Best Practices ?

Appreciate:
You are replacing the strengths of your OpenEdge
Database with those of another data source:
• Deference is given to the foreign data source on:
–
–
–
–
–
–
–
16
Set-based SQL design
Cursor Consistency
Security
Transaction Control
Lock Management
Query Optimization & Indexing
Constraints
© 2008 Progress Software Corporation
WHY Use DataServer Best Practices ?

Appreciate:
You are replacing the features of your OpenEdge
Database with those of another data source:
• “Context” resides in the foreign data source for:
–
–
–
–
–
17
Data Types: Fixed versus variable length strings
Sequences, Triggers, Constraint implementations
Schema, Naming, Qualifying, Networking Conventions
NULLs versus UNKNOWNs
PL/SQL or T-SQL versus ABL
© 2008 Progress Software Corporation
WHY Use DataServer Best Practices ?
 Because you need a map !
18
© 2008 Progress Software Corporation
WHEN To Use DataServer Best Practices ?
 Always !
 The “Flavors” of pain relief:
• Minimize loss of performance
• Maximize conformance
• Reduce time to market
19
© 2008 Progress Software Corporation
WHEN To Use DataServer Best Practices ?
 Conformance: Example
• Picking an Index
FOR EACH customer:
END.
FOR EACH employee USE-INDEX department:
END.
FOR EACH invoice BY zip-code:
END.
20
© 2008 Progress Software Corporation
WHEN To Use DataServer Best Practices ?
 Performance: Example
• Who should do the join ?
FOR EACH customer, EACH order OF customer
QUERY-TUNING (NO-JOIN-BY-SQLDB):
DISPLAY customer.name customer.cust-num
customer.postal-code order.order-date.
END.
21
© 2008 Progress Software Corporation
WHEN To Use DataServer Best Practices ?
 Performance: Example
• What will be joined ?
FOR EACH customer, EACH order OF customer
WHERE LENGTH(RIGHT-TRIM(customer.name)) > 5:
DISPLAY customer.name customer.cust-num
customer.postal-code order.order-date.
END.
22
© 2008 Progress Software Corporation
WHEN To Use DataServer Best Practices ?
 Performance: Example
• Where is the join done ?
FOR EACH customer,
FIRST order OUTER-JOIN OF customer
WHERE order.order-num < 50:
DISPLAY customer.name customer.cust-num
customer.postal-code order.order-date.
END.
23
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
The Best Practices White Paper
“Steps for Successful DataServer Development
and Deployment”

http://www.psdn.com/library/entry.jspa?externalID=1320
(Located on PSDN)
DataServer Guides

24
Chapter(s) on “Programming Considerations”
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
In the Best Practices White Paper …
STEP ONE: Eliminate DBMS differences
STEP TWO: Optimize the Application, the
DataServer, and the DBMS.
25
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 First get it to work
• Compile incompatibilities are minimal
– SETUSERID
– COUNT-OF
– CONTAINS
– RECID **
** If ROWID index is not a unique integer
26
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Then there are the nuances …
• FIND cust WHERE name BEGINS “SI”.
• FOR EACH cust WHERE name <= CHR(126).
• FOR EACH cust WHERE name <> “Bob”
27
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Record Scoping & Availability
DEFINE BUFFER xcust FOR cust.
CREATE cust.
ASSIGN cust-num = 111.
FIND xcust WHERE cust.cust-num = 111.
28
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Record Scoping & Availability
DEFINE BUFFER xcust FOR cust.
CREATE cust.
ASSIGN cust-num = 111.
VALIDATE cust.
FIND xcust WHERE cust.cust-num = 111.
29
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Record Scoping & capturing validation
FOR FIRST cust EXCLUSIVE-LOCK:
ASSIGN name = FILL(“a”,35) NO-ERROR.
VALIDATE NO-ERROR.
IF error-status:error THEN DO:
<some error processing>
UNDO, LEAVE.
END.
END.
30
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Record Scoping & Transactions
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.
31
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Record Scoping & Transactions
DO TRANSACTION:
FIND FIRST cust EXCLUSIVE-LOCK.
END.
RELEASE cust.
DO TRANSACTION:
FIND FIRST cust EXCLUSIVE-LOCK.
END.
32
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Record Scoping & Transactions
REPEAT:
FIND FIRST cust.
ASSIGN cust-num = 1.
do-blk:
DO ON ERROR UNDO do-blk, RETRY do-blk:
FIND state WHERE st.st = cust.st.
SET state.
END.
END.
DISPLAY state.
33
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Record Scoping & Transactions
REPEAT:
FIND FIRST cust.
ASSIGN cust-num = 1.
do-blk:
DO ON ERROR UNDO do-blk, RETRY do-blk:
FIND state WHERE st.st = cust.st.
SET state.
VALIDATE state.
END.
END.
DISPLAY state.
34
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Record Scoping & Visibility
FOR FIRST cust NO-LOCK BY cust-num:
END.
IF AVAILABLE cust THEN
MESSAGE “cust found” VIEW-AS ALERT-BOX.
35
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Record Scoping & Locking
FOR
FOR
FOR
FOR
36
FIRST
FIRST
FIRST
FIRST
cust:
cust NO-LOCK:
cust SHARE-LOCK:
cust EXCLUSIVE-LOCK:
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Record Scoping & Cursor Positioning
FIND cust WHERE cust-num = 123.
< Do some other stuff >
FIND cust WHERE name = “Bob”.
37
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Just code around certain nuances
&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
38
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Be explicit about:
• Scope of your records
Timing of your write operations
VALIDATE
RELEASE
RECID
39
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Code knowing that for DataServers:
• Transactions narrow record scoping
• Transactions release locks and should
release buffers
DO TRANSACTION:
ASSIGN cust.name = “Bob”.
END.
RELEASE cust.
40
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Avoid SHARE-LOCK
 Adopt a “pessimistic“ locking model:
– NO-LOCK
– EXCLUSIVE-LOCK
FOR EACH cust EXCLUSIVE-LOCK:
ASSIGN name = “Bob”.
END.
41
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences

DataServers only reposition FIND statement “cursors”.
Set-based result statements are “contained”.
• FOR EACH
• QUERY
• DYNAMIC FIND

RELEASE set-based buffers that should be out of scope after these,
FOR EACH cust:
DISPLAY cust.
END.
RELEASE cust.
42
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
43
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
Consider the difference
FIND customer.
versus
FOR EACH customer.
44
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
Consider the difference
FIND customer USE-INDEX cust-num.
versus
FOR EACH customer BY cust-num.
45
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
 Code with a set-based mind set !
•
•
•
•
FOR EACH
QUERY
DYNAMIC FIND
INDEXED-REPOSITION
 Avoid OLTP-biased FIND statements
46
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
47
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP ONE: Eliminate DBMS differences
STEP TWO: Optimize the Application, the
DataServer, and the DBMS.
On PSDN, see “Performance Study” at the
end of “DB-16: In Any Case, the Devil’s in
the DataServer Details”:
http://www.psdn.com/library/entry.jspa?externalID=3355
48
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP TWO: Optimize Application, DataServer, and DBMS.



ROWID optimizes OpenEdge database access
DataServers map ROWID to a unique key
Uniqueness in DataServers enables:
•
•
•
•
•
•
49
Locking
Deletions & Updates
FINDs, Queries & Browsers
All cursor positioning such as INDEXED-REPOSITION
RECID/ROWID functions
random record access in a non-unique set
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP TWO: Optimize Application, DataServer, and DBMS.

Set-based design enhances ROWID reconciliation
•
•
•
•
50
FOR EACH
QUERY
DYNAMIC FIND
INDEXED-REPOSITION
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP TWO: Optimize Application, DataServer, and DBMS.
#1. Eliminate FIND statements
51
FIND FIRST cust
FOR FIRST cust:END.
FIND LAST cust
FOR LAST cust:END.
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP TWO: Optimize Application, DataServer, and DBMS.
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.
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.
52
© 2008 Progress Software Corporation
WHAT are the DataServer Best Practices ?
STEP TWO: Optimize Application, DataServer, and DBMS.
#2: Stored Procedures
#3: Avoid SHARE-LOCK, Consider NO-LOCK
#4: Tune your database, especially indexes
#5: Resolve queries/joins on the server
#6: Use field lists and/or views
#7: Use QUERY-TUNING where appropriate
#8: Use –Dsrv connect options
#9: Client startup options
53
© 2008 Progress Software Corporation
In Summary
STEP TWO: Optimize Application, DataServer, and DBMS.
#10. STEP ONE: Eliminate DBMS differences
STEP ONE resonates with
54
STEP TWO
© 2008 Progress Software Corporation
For More Information, go to…
Redevelopment Tools
Basic Tools:





Manual Refactoring (and 10.1C unsupported switch “-clw”) ???
Performance logs
Progress® Debugger
Compile XREF
Progress Profiler Tool (unsupported but ships w/OpenEdge)
Other Resources:




Proparse: http://www.joanju.com
Prolint-http: http://www.prolint.org
ProRefactor: http://www.prorefactor.org
PSDN
• OpenEdge DataServers category (Best Practices paper, Performance
Study)
• Forum: OpenEdge RDBMS & Data Management
55
© 2008 Progress Software Corporation
?
Questions
56
© 2008 Progress Software Corporation
Thank You
57
© 2008 Progress Software Corporation
58
© 2008 Progress Software Corporation