db-04 tuning openedge™ sql: boosting your sql application performance
DESCRIPTION
DB-04 Tuning OpenEdge™ SQL: Boosting Your SQL Application Performance. Steve Pittman Principle Software Engineer, SQL Team. Agenda. OpenEdge SQL Server architecture Configuration and Security Applications and SQL SQL Execution thru the SQL engine Tuning and best practices Questions. - PowerPoint PPT PresentationTRANSCRIPT
DB-04Tuning OpenEdge™ SQL: Boosting Your SQL Application Performance
Steve PittmanPrinciple Software Engineer, SQL Team
2 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Agenda
OpenEdge SQL Server architecture Configuration and Security Applications and SQL SQL Execution thru the SQL engine Tuning and best practices Questions
3 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
D I S C L A I M E R
Under Development
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 I S C L A I M E R
4 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
SQL Servers
OpenEdge SQL System Architecture
SQL & 4GLBroker
SQL client
4GL client
Sh
ared M
emo
ry Database
4GL Servers
4GL client
SQL client
5 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
SQL Architecture
SQL engine– Statement planning– Statement execution - scan, join, sort, etc.– Communications
Database storage manager– Persistent data storage and indexes– Transactions– Locking – Etc.
Components
6 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Agenda
OpenEdge SQL Server architecture Configuration and Security Applications and SQL SQL Execution thru the SQL engine Tuning and best practices
7 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
OpenEdge SQL Server Configuration
Threads are better than processes– Less resource consumption– More sharing - pages, caches– Better load balancing– Faster execution
Startup parameters – -Mi - minimum threads (clients) per server– -Ma - maximum threads (clients) per server– -Mn - maximum server processes
Default settings not the best for SQL!– Better example: -Mi 4 -Ma 8 -Mn 4
Tuning the server: Threads vs. Processes
8 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
SQL Servers
OpenEdge SQL - Startup Parameters
SQL clientS
hared
Mem
ory
4GL Servers
SQL client
-Mi 5 5 threads min
-Ma 1010 threads max
9 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
OpenEdge SQL Server Configuration
Example: Start a 4GL Primary broker
Example: Start a Secondary SQL broker
Separating 4GL and SQL brokers/servers … examples
proserve Sports2000 -S 6000 -H localhost -n 48 -Mn 8 -Mpb 4 -ServerType 4GL-minport 6100 -maxport 6300 -Mi 1 -Ma 5
proserve Sports2000 -S 5000 -H localhost
-m3 –Mpb 3 -ServerType SQL -minport 5100 -maxport 5300 -Mi 4 -Ma 8
10 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
SQL Servers
OpenEdge SQL - Configured
SQL BrokerSQL client
4GL client
Sh
ared M
emo
ry Database
4GL Servers
4GL client
SQL client
4GL Broker
11 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
OpenEdge SQL Security Model
Authentication– Who am I?
Making your data safe and accessible
•aaa•jones•smith
Database
SQL Server
SQL client
User List
12 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
OpenEdge SQL Security Model
Authorization– What can I do?– Closed model (SQL) vs. open model (4GL)– Every action possible must be authorized
Privileges– DBA - can do everything– Table (also column) privileges– Sequence, stored procedure privileges
Common error– “Access Denied (Authorization failed) (7512)”
Enabling safe data access
13 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
OpenEdge SQL Security Model
Require user authentication
Define two, limited-use DBA users– Do not use predefined system DBA userid, please!!
Grant table privileges to all or selected users– GRANT is online
Best practices
14 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Agenda
OpenEdge SQL Server architecture Configuration and Security Applications and SQL SQL Execution thru the SQL engine Tuning and best practices
15 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Applications and OpenEdge SQL
Representative applications– Crystal Reports– Java, WebSphere, and JDBC– Delphi, ODBC, Web server
A look at the relationship
Database
SQL Server
SQL client
Client and server interaction
16 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Statement-oriented
Prepare, execute, fetch cycle Result set
– ODBC: fetch array size Statement may not be visible (Crystal, etc.)
Select SQL statements
Database
SQL Server
SQL client
Select onum, name, cnum …from pub.customer c, pub.orders owhere c.custnum = o.custnum
8765 General Motors 1235143 Toyota Mfg, Inc. 4228123 Chrysler Motors 274… … …
17 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Statement-oriented
Prepare, execute “cycle” Simple execute message exchange No table data flow between client and server
Update sql statements
Database
SQL Server
SQL client
Update pub.ordersset delivery_date = ‘06-30-2005’where delivery_date = ‘06-15-2005’
2 rows updated
18 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Statement-oriented
Similar statement are executed by common prepared statement– Select * from sales where date =‘06/15/05’;– Select * from sales where date =‘01/08/04’;
Automatic optimization for statements on a connection
Patterns of statements
Database
SQL Server
SQL client
Select *from pub.sales where date = ‘06/15/05’
8765 General Motors 1235143 Toyota Mfg, Inc. 4228123 Chrysler Motors 274… … …
Select *from pub.sales wheredate = ‘01/08/04’
3205 Laval Motors 689
19 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Transactions
Read Committed - default Repeatable Read Change via ODBC/JDBC API
ODBC DSN Advanced option
Isolation levels
Database
SQL Server
4GL client 2
SQL client 1
20 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Schemas
PUB schema– Schema for inter-operability with 4GL
Default schema Changing the default
– Set schema ‘pub’ ;
Sets of tables with common owner
SQL Server
SQL client
PUB SMITH
JONES
21 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Agenda
OpenEdge SQL Server architecture Configuration and Security Applications and SQL SQL Execution thru the SQL engine Tuning and best practices
22 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Executing SQL Statements
Build query plan for SQL statement Execute query plan to build result set Stream result set back to client How to choose best query plan?
What the server does
Database
OpenEdge SQL Server
Select * from T1,T2 whereT1.f=T2.g
•Join•Table Scan T1•Index Scan T2
Query Plan
23 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
What’s in a Query Plan
Query plan elements
– Table scan– Index scan– Join – Restrict– Project– Sort
Organization and form What’s useful to know
Building blocks for execution
24 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Building the Query Plan
SQL Optimizer
sql statement schema sql statistics
SQL RuntimeQuery plan
U s e r
25 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Cost-based Optimization
Optimization model– Figure out all feasible ways to do a step– Figure out the costs of each way– Choose way with smallest cost
Optimize from the inside out– Optimize table access– Optimize joins– Optimize result set
What cost is
What does this mean?
26 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
How the Optimizer Knows Cost
When no statistics exist Table and index metadata
– number of key components used– unique and non-unique indexes
Default column selectivity per operator– “=” is .04, “between” is .1, etc.
Heuristics Assume all tables have n K rows Cost =
– cardinality * selectivity * row-cost * k
Rule-based mode
27 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
How Optimizer Knows Cost
Table statistics - cardinality Column statistics - data distribution++ Individual column selectivity per operator
– % of table’s data returned by predicate Combining multiple columns selectivities Best for range operators(“between”, etc.),
especially in Version 9 Cost =
– cardinality * selectivity * row-cost * k
Statistics-based mode - basic statistics
28 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
How Optimizer Knows Cost
select *from pub.customer c, pub.order owhere c.custnum = o.custnum
and o.orderdata between ‘05/01/05’ and ‘05/30/05’
Default statistics
How many rows - table statistics
What percentage of data - column statistics
29 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
More on Column Statistics
Based on sampling table’s data “Histogram” derived from sampled data OpenEdge 10 vs. Version 9
– New: estimated number of distinct values per histogram bucket - an explanation…
– New: extensibility in statistics format in schema
Example - Histogram of 2000 random integers 1..1150
Example - number distinct values for Histogram
110 230 340 424 545 676 757 868 990 1104
95 101 92 81 108 94 102 100 96 104
30 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
How the Optimizer Knows Cost
Counts number of values for components of index
Prefixes of an index key - leading sequence of key components
Gives most precise estimate of number of rows satisfying “=” and “in” operators
Accounts for correlation between components of an index key
Can accurately model very, very low selectivity and very high selectivity
Statistics-based mode - index statistics
31 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
How Optimizer Knows Cost
select *from pub.customer c, pub.order owhere c.custnum = o.custnumand o.orderdata between ‘05/01/05’
and ‘05/30/05’
Index statistics
How many matching rows - index statistics
32 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
More on Index Statistics
Count of number of unique values for each prefix
OpenEdge 10 vs. Version 9– OE 10: counts for all prefixes
Prefix - key components 1 to n– V9: counts for first key component, and
last 3 prefixes– V9 “interpolation” for prefixes without
counts Estimate via “straight line” between first 2
counts
33 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Example - OpenEdge 10 Index Statistics
select …from Sales_Historywhere terr_id = ‘abc’and subt_id = 1and yr = 2004and zip = ‘05601’and demo_cat = ‘xyz’and cust_stat = ‘M’and regn = ‘NE’and countycd = 5 ;
Sample single table query
34 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Example - OpenEdge 10 Index Statistics
Indexes and key components:
5NE
Xmarket_seg
Xconsumer_id
Xsales_terr
Assume cardinality (total number of rows) = 1,000,000
1 2004abc
10K 50K90 100K
1 056abc Mxyz
300K90 1M800K20K10K
755
index statistics
35 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Example - Cost via Index Statistics
Cost = (1M/ 800K )rows * IO cost per row
select …from Sales_Historywhere terr_id = ‘abc’and subt_id = 1and zip = ‘05601’and demo_cat = ‘xyz’and cust_stat = ‘M’;
Index #comps count
Xconsumer_id 1 90
2 10K
3 20K
4 300K
5 800K
6 1M
index statistics
5 components specified
36 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Optimizing Join Execution
Join order Join methods
– index join (= augmented nested loop)– nested loop– dynamic index (looks like index join)
Hash join when low data volume Index join when larger data volume
Or, What gets optimized
37 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
What Optimizer Does for Join Order
Consider many possible join orders– choose least cost order
Use join cardinality as cost metric– Joining small amount of data to larger amount
of data is usually least cost Cost estimation drivers
– Table statistics
– Index statistics OpenEdge 10 and V9.1E employ much
more powerful join order exploration
38 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Planning Join Method - Index Join
Join
Joined data
get 1 row Sales
Or, augmented nested loop
SalesHist scan bracket rows
indexk-OrdL
Select …From Sales s, SalesHist h Where s.city = ‘MyTown’And s.acct = h.acct ;
39 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Example - OpenEdge10 Index Statistics
Indexes and key components:
F11F10
Xmarket_seg
Xconsumer_id
Xsales_terr
F2 F3F1
10K 350K90 700K
F2 F4F1 F6F5
100K90 1M800K
755
index statistics
10k 20k
select …from Sales s, Sales_History hwhere s.terr = h.F1and s.acct = h.F2and s.city = h.F3and s.col01 = h.F4and s.col02 = h.F5and s.regn = h.F10and s.segid = h.F11
40 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Agenda
OpenEdge SQL Server architecture Configuration and Security Applications and SQL SQL Execution thru the SQL engine Tuning and best practices
41 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
What to Tune Tune your SQL server
– SQL Statistics– Releases
Tune your SQL statements– Possible problems– Finding problems– Special situations
Database
OpenEdge SQL Server
SQL client
Tune here
42 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Tuning Your SQL Server
Create, or update, sql statistics Move to newer release
– OpenEdge 10.0B is better than 10.0A– OpenEdge 10.0A is better than Version 9.1E– Version 9.1E is better than 9.1D
Latest Service Pack– OpenEdge 10 service packs– Version 9.1 service packs
Possibly consider adding indexes
43 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Updating SQL Statistics
Default statistics– “update statistics [for <table name>];”
Best statistics– “update table statistics and index statistics and
column statistics [for <table name>];”– reads all of each index for all tables, or for one table.– May be resource intensive– Example - 4.5G customer db, 600 tables 4500 indexes
Index stats runtime = 25 cpu minutes– Index statistics drive best join optimizations
Must be DBA When to do
– relationships between tables or indexes change
44 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Tuning Your SQL Statements
Join relationships not completely expressed in predicates– Remedy - more, better join predicates on sql
statements– Every pair of tables with a relationship
should have a predicate giving that relation “select … from pub.orders O, pub.orderlines L where O.onum = L.onum”
Possible problems and remedies
45 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Tuning Your SQL Statements
Leading keys of indexes not specified– Remedy - give predicates on leading keys
Predicates best for index use not used– Remedy - best are “=”, IN– Almost best - BETWEEN– Good - >, >=, <, <=– Note - OR can disable optimizations
Several similar indexes not distinguished as expected– Remedy: index statistics
Possible problems and remedies - more
46 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Tuning Your SQL Statements
Indexes and key components:
F11F10
Xmarket_seg
Xconsumer_id
Xsales_terr
F2 F3F1
F2 F4F1 F6F5select …from Sales s, Sales_History hwhere s.accno = h.F2and s.city = h.F3and s.col01 = h.F4and s.col02 = h.F5and s.regn = h.F10and s.segid = h.F11
s.terr = h.F1
Possible problems and remedies - example
and
F7
F4
F1
F1
47 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Tuning Your SQL Statements
Time: do simple timing of data access requests Inspect: SQL statement executed Investigate: SQL virtual system table for query
plan– Access query plan for sql statement executed– Query plan data will show:
tables indexes joins predicates order
– Note: only your query plans available (currently)– Must be DBA or have DBA grant privileges
Finding problems
48 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Getting the Query Plan
Basic form
select substring("_Description",1,80)from pub."_Sql_Qplan“where "_Pnumber" = (select max("_Pnumber") from pub."_Sql_Qplan" where "_Ptype" > 0 );
Simplify with views
select * from my_Qplan;
49 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Getting the Query Plan
Get your SQL statement– Crystal Reports:
Database menu– “Show SQL query …”
Copy into a SQL query tool– SQL Explorer, WinSQL, DB Visualizer
Run your statement Run the SQL statement to get query plan
50 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Query Plan - Operations
Order of operations– Top to bottom
Tables Indexes
– Index keys Joins
– Join predicates
What you need to find
51 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Query Plan - OperationsWhat you can skip
Things to ignore– Project, sort
Sometimes useful to check out– Restrict – Dynamic index
52 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Query Plan - What to Look for
Example from simple single table select– “select … from pub.customer where custnum between 1000 and 1100”
Simple indentation to show tree form
Single-table query
SELECT COMMAND. PROJECT [66] (| PROJECT [64] ( | | PUB.CUSTOMER. [0](| | | INDEX SCAN OF ( | | | | CustNum, | | | | | (PUB.CUSTOMER.CustNum) between (1000,1100))
53 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Query Plan - What to Look for
Example with 1 join key
-- 1 key joinselect c.custnum, c.name, o.ordernum, o.orderdate from pub.customer c, pub.order o where custnum between 1000 and 1021 and c.custnum = o.custnum;
Two-table query
54 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Query Plan - What to Look for
Example with 1 join key
| JOIN [13][AUG_NESTED_LOOP-JOIN]| | | PUB.O. [12](| | | | INDEX SCAN OF (| | | | | CustOrder, | | | | | | (PUB.O.CustNum) between (…)| | (PEXPR3) = (PEXPR5)| | -- above defines ANL left side keys <relop> right side keys.| | | PUB.C. [11]( | | | | INDEX SCAN OF ( | | | | | CustNum, | | | | | | (PUB.C.CustNum) = (null))
55 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Query Plan - What to Look for
Example with 2 join keys
-- 2 key joinselect o.ordernum, o.orderdate, l.itemnum from pub.order o, pub.orderline l where o.custnum between 1 and 3 and o.ordernum = l.ordernum and o.custnum = l.linenum;
Two-table query
56 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Query Plan - What to Look for
Example with 2 join keys
JOIN [13][AUG_NESTED_LOOP-JOIN]| | PUB.O. [2]( | | | INDEX SCAN OF ( | | | | CustOrder, | | | | | (PUB.O.CustNum) between (1,3))| (PEXPR1, PEXPR3) = (PEXPR5, PEXPR6) | -- above defines ANL left side keys <relop> right side keys. | | | PUB.L. [3]( | | | | INDEX SCAN OF ( | | | | | orderline, | | | | | |(PUB.L.Ordernum, PUB.L.Linenum) = (null,null))
57 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Tuning Your SQL Statements
Experiment with alternate queries without cost of query execution!
Use NOEXECUTE to experiment– Test, inspect query plan, repeat until done– OpenEdge 10 and Progress Version 9– NOEXECUTE is 1 word– Example:
Special situations
select … from Table1 t1, Table2 t2where t1.key = 5 and t1.key = t2.keyNOEXECUTE;
58 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Tuning Your SQL Statements
Forcing the join order– NO REORDER phrase at end of FROM
clause– When all else fails!– Example:
Index hints– Use carefully - not deterministic
Special situations
select … from Table1, Table2, Table3 {NO REORDER}where … ;
59 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Index Hint
select …
from PUB.ih_hist ih_hist, PUB.pt_mstr pt_mstr with (index(pt_part_type)), PUB.idh_hist idh_hist
where idh_hist.idh_part = pt_mstr.pt_part and ih_hist.ih_inv_nbr = idh_hist.idh_inv_nbrand pt_mstr.pt_part_type = ‘FG’and ih_hist.ih_inv_date <= ’01/24/2005’and ih_hist.ih_inv_date >= to_date(‘01/24/2005’)
Syntax– WITH ( INDEX ( <index name> ) )
Choose index if it is an eligible candidate
60 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
In Summary
OpenEdge SQL Server configuration What the server does for your
application Tuning to make the server do what
you want
61 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Related SQL Exchange Sessions
DB-15: Developing Performance-Oriented ODBC/JDBC OpenEdge Applications– Wednesday, 8 June, 2:15pm - 3:15pm
DB-07: OpenEdge SQL and Sonic - Using JMS with SQL Applications – Tuesday, 7 June, 9:15am - 10:15am
DB-09: Database Roadmap – Tuesday, 7 June, 2:15pm - 3:15pm
62 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Questions?
63 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Thank you for your time!
64 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
65 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Appendix - online resources
White paper on query optimizer– http://psdn.progress.com/library/whitepapers/sql92/docs/
sql92_optimizer.pdf
Collection of white papers– Getting Started with Crystal– ODBC, JDBC Configuration– Locking– Index statistics– Server configuration– Visit:
http://psdn.progress.com/library/white_papers/sql/index
66 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Appendix - online resources
Documentation – http://www.progress.com/products/documentation/index.ssp
Knowledge base articles– 19968, p7843 - secondary broker– 20143 - authorization– 21676, 20007, 20327 - query plan
67 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
OpenEdge SQL Server Configuration
4GL Primary Broker
– -n and -Mn Primary Broker Specific
SQL Secondary Broker
Separating 4GL and SQL brokers/servers … syntax
proserve dbname -S n -H x -n n -Mn n -Mpb n -ServerType 4GL -minport n -maxport n -Mi n -Ma n
proserve dbname -S n -H x -m3 –Mpb n -ServerType SQL -minport n -maxport n -Mi n -Ma n
68 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Appendix - setting default schema for ODBC
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\PD6D1KPM001]"Driver"="c:\\progress-91d\\bin\\PGPRO915.DLL""Description"="""HostName"="cuttyhunk""PortNumber"="2525""DatabaseName"="test1""LogonID"="pdadmin""StaticCursorLongColBuffLen"="4096""DefaultIsolationLevel"="SQL_TXN_REPEATABLE_READ""ArraySize"="50""DefaultLongDataBuffLen"="2048""DefaultSchema"="PUB"
In Windows registry, find entry for– HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
Find sub-entry for your ODBC Data Source Name– …\SOFTWARE\ODBC\ODBC.INI\<DSN NAME>
Create new string value for schema name:– Value name: …\ODBC\ODBC.INI\<DSN NAME>\DefaultSchema– Value data: <your schema name>
Example:
69 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
OpenEdge SQL Security Model
GRANT statement– GRANT is online– Example
“grant select on pub.orders to jones;” “grant all on pub.orders to public;”
REVOKE statement– REVOKE is offline– Example
“revoke all on pub.orders from public;”
Commands
70 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Security Considerations
Database wide (system admin or general creation)
For specified Tables or Views
Where ‘privilege’ is:
{ SELECT | INSERT | DELETE | INDEX | UPDATE [ ( column , column , ... ) ] | REFERENCES [ ( column , column , ... ) ] }
Privileges – Syntax: GRANTing them (2 types)
GRANT { DBA, RESOURCE }TO user_name [, user_name ] , …;
GRANT { privilege [, privilege ], … | ALL }ON table_nameTO { user_name [, user_name ] , … | PUBLIC }[ WITH GRANT OPTION ];
71 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Security Considerations
Database wide (system admin or general creation)
For specified Tables or Views
Where ‘privilege’ is:
{ SELECT | INSERT | DELETE | INDEX | UPDATE [ ( column , column , ... ) ] | REFERENCES [ ( column , column , ... ) ] }
Privileges – Syntax: REVOKEing them (2 types)
REVOKE { DBA, RESOURCE }FROM user_name [, user_name ] , …;
REVOKE [ GRANT OPTION FOR ] { privilege [, privilege ], … | ALL [ PRIVILEGES ] }ON table_nameFROM { user_name [, user_name ] , … | PUBLIC }[ RESTRICT | CASCADE ];
72 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Join method - nested loop
Select …From Order O, OrderLine L Where O.ordnum = 1004And O.ordnum = L.ordnum;
Join and eval
Joined data
OrderLine scan all rowsget 1 row Order
73 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Join method - dynamic index
Join
Joined data
get 1 row Order
Or, index join
Temp table with extracted,
indexed OrderLine data
scan bracket rows
dynamicindex
Select …From Order O, OrderLine L Where O.city = ‘MyTown’And O.shipper = L.shipper;
74 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Updating sql statistics
Table statistics– “update table statistics [for <table name>];”
Column statistics– “update [all] column statistics [for <table name>];”
Index statistics– “update index statistics [for <table name>];”– reads all of each index for 1, or all, tables.
Specific categories of statistics
75 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Appendix - query plan view 1
-- to show all of query plan for most recent statement.create view qplan_full as select * from pub."_Sql_Qplan" where "_Pnumber" = (select max( "_Pnumber" ) from pub."_Sql_Qplan" where "_Ptype" > 0 );
grant select on qplan_full to public;
create public synonym qplan_full for qplan_full ;
commit work;
76 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Appendix - query plan view 2-- try to show just the highlights of query plan, omitting data specifics.create view qplan_no_data as select * from pub."_Sql_Qplan" where "_Pnumber" = (select max( "_Pnumber" ) from pub."_Sql_Qplan" where "_Ptype" > 0 ) and "_Description" not like '% , %' and "_Description" not like '%, PEXPR%' and "_Description" not like '%, substr%' and "_Description" not like '%| )' and "_Description" not like '%| )%' and "_Description" not like '%| ,%' and "_Description" not like '%callback%' and "_Description" not like '%col id# %' and "_Description" not like '%@%' and "_Description" not like '%terminate%' and ("_Description" not like '% )' or "_Description" like '%OJ Predicate%' ) ;
grant select on qplan_no_data to public;
create public synonym qplan_no_data for qplan_no_data ;
commit work;
77 DB-04 Tuning OpenEdge SQL© 2005 Progress Software Corporation
Appendix - query plan “definition”
create table "_Sql_Qplan" ( "_Pnumber" integer not null, -- plan number. "_Ptype" integer not null, -- plan type. "_Dtype" integer not null, -- description type. "_Description" varchar(255) not null, -- description line. "_Dseq" integer not null -- description sequence#. );
Table "_Sql_Qplan" exists as if it had been created by the sql syntax below
The definition of this sql virtual system table is not visible to client tools such as Crystal Reports.