Db 04 tuning openedge sql boosting your sql application performance
This presentation is the property of its rightful owner.
Sponsored Links
1 / 77

DB-04 Tuning OpenEdge™ SQL: Boosting Your SQL Application Performance PowerPoint PPT Presentation


  • 201 Views
  • Uploaded on
  • Presentation posted in: General

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.

Download Presentation

DB-04 Tuning OpenEdge™ SQL: Boosting Your SQL Application Performance

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Db 04 tuning openedge sql boosting your sql application performance

DB-04Tuning OpenEdge™ SQL: Boosting Your SQL Application Performance

Steve Pittman

Principle Software Engineer, SQL Team


Agenda

Agenda

  • OpenEdge SQL Server architecture

  • Configuration and Security

  • Applications and SQL

  • SQL Execution thru the SQL engine

  • Tuning and best practices

  • Questions

DB-04 Tuning OpenEdge SQL


Under development

Under Development

D I S C L A I M E R

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.

DB-04 Tuning OpenEdge SQL


Openedge sql system architecture

OpenEdge SQL System Architecture

SQL

Servers

Shared Memory

SQL client

SQL client

SQL & 4GL

Broker

Database

4GL client

4GL client

4GL

Servers

DB-04 Tuning OpenEdge SQL


Sql architecture

SQL Architecture

Components

  • SQL engine

    • Statement planning

    • Statement execution - scan, join, sort, etc.

    • Communications

  • Database storage manager

    • Persistent data storage and indexes

    • Transactions

    • Locking

    • Etc.

DB-04 Tuning OpenEdge SQL


Agenda1

Agenda

  • OpenEdge SQL Server architecture

  • Configuration and Security

  • Applications and SQL

  • SQL Execution thru the SQL engine

  • Tuning and best practices

DB-04 Tuning OpenEdge SQL


Openedge sql server configuration

OpenEdge SQL Server Configuration

Tuning the server: Threads vs. Processes

  • 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

DB-04 Tuning OpenEdge SQL


Openedge sql startup parameters

OpenEdge SQL - Startup Parameters

SQL

Servers

Shared Memory

SQL client

SQL client

-Mi 5 5 threads min

-Ma 1010 threads max

4GL

Servers

DB-04 Tuning OpenEdge SQL


Openedge sql server configuration1

OpenEdge SQL Server Configuration

Separating 4GL and SQL brokers/servers … examples

  • Example: Start a 4GL Primary broker

  • Example: Start a Secondary SQL broker

proserve Sports2000 -S 6000 -H localhost

-n 48 -Mn 8 -Mpb 4 -ServerType4GL

-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

DB-04 Tuning OpenEdge SQL


Openedge sql configured

OpenEdge SQL - Configured

SQL

Servers

Shared Memory

SQL Broker

SQL client

SQL client

Database

4GL client

4GL client

4GL

Servers

4GL Broker

DB-04 Tuning OpenEdge SQL


Openedge sql security model

OpenEdge SQL Security Model

SQL Server

User List

SQL client

Database

Making your data safe and accessible

  • Authentication

    • Who am I?

  • aaa

  • jones

  • smith

DB-04 Tuning OpenEdge SQL


Openedge sql security model1

OpenEdge SQL Security Model

Enabling safe data access

  • 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)”

DB-04 Tuning OpenEdge SQL


Openedge sql security model2

OpenEdge SQL Security Model

Best practices

  • 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

DB-04 Tuning OpenEdge SQL


Agenda2

Agenda

  • OpenEdge SQL Server architecture

  • Configuration and Security

  • Applications and SQL

  • SQL Execution thru the SQL engine

  • Tuning and best practices

DB-04 Tuning OpenEdge SQL


Applications and openedge sql

Applications and OpenEdge SQL

SQL Server

Database

SQL client

Client and server interaction

A look at the relationship

  • Representative applications

    • Crystal Reports

    • Java, WebSphere, and JDBC

    • Delphi, ODBC, Web server

DB-04 Tuning OpenEdge SQL


Statement oriented

Statement-oriented

Select SQL statements

  • Prepare, execute, fetch cycle

  • Result set

    • ODBC: fetch array size

  • Statement may not be visible (Crystal, etc.)

Select onum, name, cnum …from pub.customer c, pub.orders owhere c.custnum = o.custnum

SQL Server

Database

SQL client

8765General Motors1235143Toyota Mfg, Inc.4228123Chrysler Motors274………

DB-04 Tuning OpenEdge SQL


Statement oriented1

Statement-oriented

Update sql statements

  • Prepare, execute “cycle”

  • Simple execute message exchange

  • No table data flow between client and server

Update pub.ordersset delivery_date = ‘06-30-2005’where delivery_date = ‘06-15-2005’

SQL Server

Database

SQL client

2 rows updated

DB-04 Tuning OpenEdge SQL


Statement oriented2

Statement-oriented

Patterns of statements

  • 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

Select *from pub.sales where date = ‘06/15/05’

Select *from pub.sales wheredate = ‘01/08/04’

SQL Server

Database

SQL client

8765General Motors1235143Toyota Mfg, Inc.4228123Chrysler Motors274………

3205Laval Motors689

DB-04 Tuning OpenEdge SQL


Transactions

Transactions

Isolation levels

  • Read Committed - default

  • Repeatable Read

  • Change via ODBC/JDBC API

    • ODBC DSN Advanced option

SQL Server

SQL client 1

Database

4GL client 2

DB-04 Tuning OpenEdge SQL


Schemas

Schemas

Sets of tables with common owner

  • PUB schema

    • Schema for inter-operability with 4GL

  • Default schema

  • Changing the default

    • Set schema ‘pub’ ;

SQL Server

PUB

SMITH

SQL client

JONES

DB-04 Tuning OpenEdge SQL


Agenda3

Agenda

  • OpenEdge SQL Server architecture

  • Configuration and Security

  • Applications and SQL

  • SQL Execution thru the SQL engine

  • Tuning and best practices

DB-04 Tuning OpenEdge SQL


Executing sql statements

Executing SQL Statements

What the server does

  • 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?

OpenEdge SQL Server

Query Plan

Select *

from T1,T2 where

T1.f=T2.g

  • Join

    • Table Scan T1

    • Index Scan T2

Database

DB-04 Tuning OpenEdge SQL


What s in a query plan

What’s in a Query Plan

Building blocks for execution

  • Query plan elements

    • Table scan

    • Index scan

    • Join

    • Restrict

    • Project

    • Sort

  • Organization and form

  • What’s useful to know

DB-04 Tuning OpenEdge SQL


Building the query plan

Building the Query Plan

schema

sql statistics

sql statement

SQL Optimizer

U s e r

SQL Runtime

Query plan

DB-04 Tuning OpenEdge SQL


Cost based optimization

Cost-based Optimization

What does this mean?

  • 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

DB-04 Tuning OpenEdge SQL


How the optimizer knows cost

How the Optimizer Knows Cost

Rule-based mode

  • 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

DB-04 Tuning OpenEdge SQL


How optimizer knows cost

How Optimizer Knows Cost

Statistics-based mode - basic statistics

  • 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

DB-04 Tuning OpenEdge SQL


How optimizer knows cost1

How Optimizer Knows Cost

Default statistics

select *

from pub.customer c, pub.order o

where c.custnum = o.custnum

and o.orderdata between ‘05/01/05’

and ‘05/30/05’

What percentage of data - column statistics

How many rows - table statistics

DB-04 Tuning OpenEdge SQL


More on column statistics

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

DB-04 Tuning OpenEdge SQL


How the optimizer knows cost1

How the Optimizer Knows Cost

Statistics-based mode - index statistics

  • 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

DB-04 Tuning OpenEdge SQL


How optimizer knows cost2

How Optimizer Knows Cost

Index statistics

select *

from pub.customer c, pub.order o

where c.custnum = o.custnum

and o.orderdata between ‘05/01/05’

and ‘05/30/05’

How many matching rows - index statistics

DB-04 Tuning OpenEdge SQL


More on index statistics

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

DB-04 Tuning OpenEdge SQL


Example openedge 10 index statistics

Example - OpenEdge 10 Index Statistics

  • Sample single table query

select …

from Sales_History

where terr_id = ‘abc’

and subt_id = 1

and yr = 2004

and zip = ‘05601’

and demo_cat = ‘xyz’

and cust_stat = ‘M’

and regn = ‘NE’

and countycd = 5 ;

DB-04 Tuning OpenEdge SQL


Example openedge 10 index statistics1

Example - OpenEdge 10 Index Statistics

abc

2004

1

50K

100K

90

10K

abc

056

xyz

M

1

NE

5

5

75

Indexes and key components:

Xsales_terr

index statistics

300K

800K

1M

90

10K

20K

Xconsumer_id

Assume cardinality (total number of rows) = 1,000,000

Xmarket_seg

DB-04 Tuning OpenEdge SQL


Example cost via index statistics

Example - Cost via Index Statistics

5 components specified

index statistics

Cost = (1M/ 800K )rows * IO cost per row

select …

from Sales_History

where terr_id = ‘abc’

and subt_id = 1

and zip = ‘05601’

and demo_cat = ‘xyz’

and cust_stat = ‘M’;

DB-04 Tuning OpenEdge SQL


Optimizing join execution

Optimizing Join Execution

Or, What gets optimized

  • 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

DB-04 Tuning OpenEdge SQL


What optimizer does for join order

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

DB-04 Tuning OpenEdge SQL


Planning join method index join

Planning Join Method - Index Join

indexk-OrdL

Sales

scan bracket rows

get 1 row

SalesHist

Or, augmented nested loop

Joined data

Select …

From Sales s, SalesHist hWhere s.city = ‘MyTown’

And s.acct = h.acct ;

Join

DB-04 Tuning OpenEdge SQL


Example openedge10 index statistics

Example - OpenEdge10 Index Statistics

F1

F3

F2

350K

700K

90

10K

F1

F4

F5

F6

F2

F10

F11

5

75

Indexes and key components:

index statistics

Xsales_terr

select …

from Sales s, Sales_History h

where s.terr = h.F1

and s.acct = h.F2

and s.city = h.F3

and s.col01 = h.F4

and s.col02 = h.F5

and s.regn = h.F10

and s.segid = h.F11

100K

800K

1M

90

10k

20k

Xconsumer_id

Xmarket_seg

DB-04 Tuning OpenEdge SQL


Agenda4

Agenda

  • OpenEdge SQL Server architecture

  • Configuration and Security

  • Applications and SQL

  • SQL Execution thru the SQL engine

  • Tuning and best practices

DB-04 Tuning OpenEdge SQL


What to tune

What to Tune

  • Tune your SQL server

    • SQL Statistics

    • Releases

  • Tune your SQL statements

    • Possible problems

    • Finding problems

    • Special situations

OpenEdge SQL Server

SQL client

Database

Tune here

DB-04 Tuning OpenEdge SQL


Tuning your sql server

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

DB-04 Tuning OpenEdge SQL


Updating sql statistics

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

DB-04 Tuning OpenEdge SQL


Tuning your sql statements

Tuning Your SQL Statements

Possible problems and remedies

  • 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”

DB-04 Tuning OpenEdge SQL


Tuning your sql statements1

Tuning Your SQL Statements

Possible problems and remedies - more

  • 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

DB-04 Tuning OpenEdge SQL


Tuning your sql statements2

Tuning Your SQL Statements

F10

F11

Possible problems and remedies - example

F1

F1

F3

F2

F4

Indexes and key components:

Xsales_terr

select …

from Sales s, Sales_History h

where

s.accno = h.F2

and s.city = h.F3

and s.col01 = h.F4

and s.col02 = h.F5

and s.regn = h.F10

and s.segid = h.F11

F1

F7

F1

F4

F5

F6

F2

Xconsumer_id

s.terr = h.F1

and

Xmarket_seg

DB-04 Tuning OpenEdge SQL


Tuning your sql statements3

Tuning Your SQL Statements

Finding problems

  • 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

DB-04 Tuning OpenEdge SQL


Getting the query plan

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;

DB-04 Tuning OpenEdge SQL


Getting the query plan1

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

DB-04 Tuning OpenEdge SQL


Query plan operations

Query Plan - Operations

What you need to find

  • Order of operations

    • Top to bottom

  • Tables

  • Indexes

    • Indexkeys

  • Joins

    • Join predicates

DB-04 Tuning OpenEdge SQL


Query plan operations1

Query Plan - Operations

What you can skip

  • Things to ignore

    • Project, sort

  • Sometimes useful to check out

    • Restrict

    • Dynamic index

DB-04 Tuning OpenEdge SQL


Query plan what to look for

Query Plan - What to Look for

Single-table query

  • Example from simple single table select

    • “select … from pub.customer where custnum between 1000 and 1100”

  • Simple indentation to show tree form

SELECT COMMAND.

PROJECT [66] (

| PROJECT [64] (

| | PUB.CUSTOMER. [0](

| | | INDEX SCANOF (

| | | | CustNum,

| | | | | (PUB.CUSTOMER.CustNum) between

(1000,1100))

DB-04 Tuning OpenEdge SQL


Query plan what to look for1

Query Plan - What to Look for

Two-table query

  • Example with 1 join key

-- 1 key join

select 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;

DB-04 Tuning OpenEdge SQL


Query plan what to look for2

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))

DB-04 Tuning OpenEdge SQL


Query plan what to look for3

Query Plan - What to Look for

Two-table query

  • Example with 2 join keys

-- 2 key join

select 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;

DB-04 Tuning OpenEdge SQL


Query plan what to look for4

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))

DB-04 Tuning OpenEdge SQL


Tuning your sql statements4

Tuning Your SQL Statements

Special situations

  • 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:

select … from Table1 t1, Table2 t2

where t1.key = 5 and t1.key = t2.key

NOEXECUTE;

DB-04 Tuning OpenEdge SQL


Tuning your sql statements5

Tuning Your SQL Statements

Special situations

  • Forcing the join order

    • NO REORDER phrase at end of FROM clause

    • When all else fails!

    • Example:

  • Index hints

    • Use carefully - not deterministic

select …

from Table1, Table2, Table3 {NO REORDER}

where … ;

DB-04 Tuning OpenEdge SQL


Index hint

Index Hint

  • Syntax

    • WITH ( INDEX ( <index name> ) )

  • Choose index if it is an eligible candidate

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_nbr

and 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’)

DB-04 Tuning OpenEdge SQL


In summary

In Summary

  • OpenEdge SQL Server configuration

  • What the server does for your application

  • Tuning to make the server do what you want

DB-04 Tuning OpenEdge SQL


Related sql exchange sessions

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

DB-04 Tuning OpenEdge SQL


Questions

Questions?

DB-04 Tuning OpenEdge SQL


Thank you for your time

Thank you for your time!

DB-04 Tuning OpenEdge SQL


Db 04 tuning openedge sql boosting your sql application performance

DB-04 Tuning OpenEdge SQL


Appendix online resources

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

DB-04 Tuning OpenEdge SQL


Appendix online resources1

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

DB-04 Tuning OpenEdge SQL


Openedge sql server configuration2

OpenEdge SQL Server Configuration

Separating 4GL and SQL brokers/servers … syntax

  • 4GL Primary Broker

    • -n and -Mn Primary Broker Specific

  • SQL Secondary Broker

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

DB-04 Tuning OpenEdge SQL


Appendix setting default schema for odbc

Appendix - setting default schema for ODBC

  • 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:

[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"

DB-04 Tuning OpenEdge SQL


Openedge sql security model3

OpenEdge SQL Security Model

Commands

  • 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;”

DB-04 Tuning OpenEdge SQL


Security considerations

Security Considerations

Privileges – Syntax: GRANTing them (2 types)

  • 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 , ... ) ] }

GRANT { DBA, RESOURCE }

TO user_name [, user_name ] , …;

GRANT { privilege [, privilege ], … | ALL }

ON table_name

TO { user_name [, user_name ] , … | PUBLIC }

[ WITH GRANT OPTION ];

DB-04 Tuning OpenEdge SQL


Security considerations1

Security Considerations

Privileges – Syntax: REVOKEing them (2 types)

  • 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 , ... ) ] }

REVOKE { DBA, RESOURCE }

FROM user_name [, user_name ] , …;

REVOKE [ GRANT OPTION FOR ] { privilege [, privilege ], … | ALL [ PRIVILEGES ] }

ON table_name

FROM { user_name [, user_name ] , … | PUBLIC }

[ RESTRICT | CASCADE ];

DB-04 Tuning OpenEdge SQL


Join method nested loop

Join method - nested loop

scan all rows

OrderLine

Order

get 1 row

Select …

From OrderO, OrderLine LWhere O.ordnum = 1004

And O.ordnum = L.ordnum;

Joined data

Join and eval

DB-04 Tuning OpenEdge SQL


Join method dynamic index

Join method - dynamic index

dynamicindex

Order

scan bracket rows

get 1 row

Or, index join

Joined data

Select …

From OrderO, OrderLine LWhere O.city = ‘MyTown’

And O.shipper = L.shipper;

Join

Temp tablewith extracted, indexedOrderLine data

DB-04 Tuning OpenEdge SQL


Updating sql statistics1

Updating sql statistics

Specific categories of 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.

DB-04 Tuning OpenEdge SQL


Appendix query plan view 1

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;

DB-04 Tuning OpenEdge SQL


Appendix query plan view 2

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;

DB-04 Tuning OpenEdge SQL


Appendix query plan definition

Appendix - query plan “definition”

  • 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.

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#.

);

DB-04 Tuning OpenEdge SQL


  • Login