DB04 Tuning Open Edge SQL Boosting Your SQL

DB-04 Tuning Open. Edge™ SQL: Boosting Your SQL Application Performance Steve Pittman Principle Software Engineer, SQL Team

Agenda n n n Open. Edge SQL Server architecture Configuration and Security Applications and SQL Execution thru the SQL engine Tuning and best practices Questions 2 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Under Development n n 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 I S C 3 © 2005 Progress Software Corporation L A I M E R DB-04 Tuning Open. Edge SQL

Open. Edge SQL System Architecture SQL Servers SQL client Shared Memory SQL client SQL & 4 GL Broker 4 GL client 4 © 2005 Progress Software Corporation Database 4 GL Servers DB-04 Tuning Open. Edge SQL

SQL Architecture Components n SQL engine – Statement planning – Statement execution - scan, join, sort, etc. – Communications n Database storage manager – Persistent data storage and indexes – Transactions – Locking – Etc. 5 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Agenda n n n Open. Edge SQL Server architecture Configuration and Security Applications and SQL Execution thru the SQL engine Tuning and best practices 6 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Open. Edge SQL Server Configuration Tuning the server: Threads vs. Processes n Threads are better than processes – – n 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 n Default settings not the best for SQL! – Better example: -Mi 4 -Ma 8 -Mn 4 7 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Open. Edge SQL - Startup Parameters SQL client SQL Servers Shared Memory SQL client -Mi 5 5 threads min -Ma 10 10 threads max 4 GL Servers 8 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Open. Edge SQL Server Configuration Separating 4 GL and SQL brokers/servers … examples n Example: Start a 4 GL Primary broker proserve Sports 2000 -S 6000 -H localhost -n 48 -Mn 8 -Mpb 4 -Server. Type 4 GL -minport 6100 -maxport 6300 -Mi 1 -Ma 5 n Example: Start a Secondary SQL broker proserve Sports 2000 -S 5000 -H localhost -m 3 –Mpb 3 -Server. Type SQL -minport 5100 -maxport 5300 -Mi 4 -Ma 8 9 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Open. Edge SQL - Configured SQL client SQL Broker SQL Servers Shared Memory SQL

Open. Edge SQL Security Model Making your data safe and accessible n Authentication – Who am I? User List SQL client 11 © 2005 Progress Software Corporation • aaa • jones • smith SQL Server Database DB-04 Tuning Open. Edge SQL

Open. Edge SQL Security Model Enabling safe data access n Authorization – What can I do? – Closed model (SQL) vs. open model (4 GL) – Every action possible must be authorized n Privileges – DBA - can do everything – Table (also column) privileges – Sequence, stored procedure privileges n Common error – “Access Denied (Authorization failed) (7512)” 12 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Open. Edge SQL Security Model Best practices n Require user authentication n Define two, limited-use DBA users – Do not use predefined system DBA userid, please!! n Grant table privileges to all or selected users – GRANT is online 13 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Agenda n n n Open. Edge SQL Server architecture Configuration and Security Applications and SQL Execution thru the SQL engine Tuning and best practices 14 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Applications and Open. Edge SQL A look at the relationship n Representative applications – Crystal Reports – Java, Web. Sphere, and JDBC – Delphi, ODBC, Web server SQL Server SQL client Database Client and server interaction 15 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Statement-oriented Select SQL statements n n n 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 o where c. custnum = o. custnum SQL client 8765 5143 8123 … 16 © 2005 Progress Software Corporation General Motors Toyota Mfg, Inc. Chrysler Motors … SQL Server 123 422 274 … Database DB-04 Tuning Open. Edge SQL

Statement-oriented Update sql statements n n n Prepare, execute “cycle” Simple execute message exchange No table data flow between client and server Update pub. orders set delivery_date = ‘ 06 -30 -2005’ where delivery_date = ‘ 06 -15 -2005’ SQL client 2 rows updated 17 © 2005 Progress Software Corporation SQL Server Database DB-04 Tuning Open. Edge SQL

Statement-oriented Patterns of statements n Similar statement are executed by common prepared statement – Select * from sales where date =‘ 06/15/05’; – Select * from sales where date =‘ 01/08/04’; n Automatic optimization for statements on a connection Select * from pub. sales where date = ‘ 06/15/05’ SQL client 8765 5143 8123 … 18 © 2005 Progress Software Corporation Select * from pub. sales where date = ‘ 01/08/04’ General Motors 123 3205 Laval Motors Toyota Mfg, Inc. 422 Chrysler Motors 274 … … 689 SQL Server Database DB-04 Tuning Open. Edge SQL

Transactions Isolation levels n n n Read Committed - default Repeatable Read Change via ODBC/JDBC API n ODBC DSN Advanced option SQL client 1 SQL Server Database 4 GL client 2 19 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Schemas Sets of tables with common owner n PUB schema – Schema for inter-operability with 4 GL n n Default schema Changing the default – Set schema ‘pub’ ; SQL Server SQL client 20 © 2005 Progress Software Corporation PUB SMITH JONES DB-04 Tuning Open. Edge SQL

Agenda n n n Open. Edge SQL Server architecture Configuration and Security Applications and SQL Execution thru the SQL engine Tuning and best practices 21 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Executing SQL Statements What the server does n n 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? Open. Edge SQL Server Select * from T 1, T 2 where T 1. f=T 2. g 22 © 2005 Progress Software Corporation Query Plan • Join • Table Scan T 1 • Index Scan T 2 Database DB-04 Tuning Open. Edge SQL

What’s in a Query Plan Building blocks for execution n Query plan elements – Table scan – Index scan – Join – Restrict – Project – Sort n n Organization and form What’s useful to know 23 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Building the Query Plan sql statement U s e r schema sql statistics SQL

Cost-based Optimization What does this mean? n Optimization model – Figure out all feasible ways to do a step – Figure out the costs of each way – Choose way with smallest cost n Optimize from the inside out – Optimize table access – Optimize joins – Optimize result set n What cost is 25 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

How the Optimizer Knows Cost Rule-based mode n n When no statistics exist Table and index metadata – number of key components used – unique and non-unique indexes n Default column selectivity per operator – “=” is. 04, “between” is. 1, etc. n n n Heuristics Assume all tables have n K rows Cost = – cardinality * selectivity * row-cost * k 26 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

How Optimizer Knows Cost Statistics-based mode - basic statistics n n n Table statistics - cardinality Column statistics - data distribution++ Individual column selectivity per operator – % of table’s data returned by predicate n n n Combining multiple columns selectivities Best for range operators(“between”, etc. ), especially in Version 9 Cost = – cardinality * selectivity * row-cost * k 27 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

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’ How many rows table statistics 28 © 2005 Progress Software Corporation What percentage of data column statistics DB-04 Tuning Open. Edge SQL

More on Column Statistics n n n Based on sampling table’s data “Histogram” derived from sampled data Open. Edge 10 vs. Version 9 – New: estimated number of distinct values per histogram bucket - an explanation… – New: extensibility in statistics format in schema 110 230 340 424 545 676 757 868 990 1104 Example - Histogram of 2000 random integers 1. . 1150 95 101 92 81 108 94 102 100 96 104 Example - number distinct values for Histogram 29 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

How the Optimizer Knows Cost Statistics-based mode - index statistics n n n 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 30 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

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 31 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

More on Index Statistics n n Count of number of unique values for each prefix Open. Edge 10 vs. Version 9 – OE 10: counts for all prefixes n Prefix - key components 1 to n – V 9: counts for first key component, and last 3 prefixes – V 9 “interpolation” for prefixes without counts n Estimate via “straight line” between first 2 counts 32 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Example - Open. Edge 10 Index Statistics n 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 ; 33 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Example - Open. Edge 10 Index Statistics Indexes and key components: abc 1 2004 90 10 K 50 K 100 K Xsales_terr index statistics 056 xyz M abc 1 90 10 K 20 K 300 K 800 K 1 M Xconsumer_id NE Assume cardinality (total number of rows) = 1, 000 34 © 2005 Progress Software Corporation 5 5 75 Xmarket_seg DB-04 Tuning Open. Edge SQL

Example - Cost via Index Statistics Index #comps count Xconsumer_id 1 90 2 10 K 3 20 K 4 300 K 5 800 K 6 1 M index statistics Cost = (1 M/ 800 K )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’; 35 © 2005 Progress Software Corporation 5 components specified DB-04 Tuning Open. Edge SQL

Optimizing Join Execution Or, What gets optimized n n Join order Join methods – index join (= augmented nested loop) – nested loop – dynamic index (looks like index join) n n Hash join when low data volume Index join when larger data volume 36 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

What Optimizer Does for Join Order n Consider many possible join orders – choose least cost order n Use join cardinality as cost metric – Joining small amount of data to larger amount of data is usually least cost n Cost estimation drivers – Table statistics – Index statistics n Open. Edge 10 and V 9. 1 E employ much more powerful join order exploration 37 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Planning Join Method - Index Joined data Or, augmented nested loop Select … From Sales s, Sales. Hist h Where s. city = ‘My. Town’ And s. acct = h. acct ; Join index k-Ord. L get 1 row Sales 38 © 2005 Progress Software Corporation scan bracket rows Sales. Hist DB-04 Tuning Open. Edge SQL

Example - Open. Edge 10 Index Statistics Indexes and key components: index statistics F 1 F 2 F 3 90 10 K 350 K 700 K Xsales_terr select … from Sales s, Sales_History h where s. terr = h. F 1 and s. acct = h. F 2 and s. city = h. F 3 and s. col 01 = h. F 4 and s. col 02 = h. F 5 and s. regn = h. F 10 and s. segid = h. F 11 39 © 2005 Progress Software Corporation F 4 F 1 F 2 90 F 5 F 6 10 k 20 k 100 K 800 K 1 M Xconsumer_id F 10 F 11 5 75 Xmarket_seg DB-04 Tuning Open. Edge SQL

Agenda n n n Open. Edge SQL Server architecture Configuration and Security Applications and SQL Execution thru the SQL engine Tuning and best practices 40 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

What to Tune n n Tune your SQL server – SQL Statistics – Releases Tune your SQL statements – Possible problems – Finding problems – Special situations Open. Edge SQL Server SQL client Database Tune here 41 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Tuning Your SQL Server n n Create, or update, sql statistics Move to newer release – Open. Edge 10. 0 B is better than 10. 0 A – Open. Edge 10. 0 A is better than Version 9. 1 E – Version 9. 1 E is better than 9. 1 D n Latest Service Pack – Open. Edge 10 service packs – Version 9. 1 service packs n Possibly consider adding indexes 42 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Updating SQL Statistics n Default statistics – “update statistics [for <table name>]; ” n 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. 5 G customer db, 600 tables 4500 indexes n Index stats runtime = 25 cpu minutes – Index statistics drive best join optimizations n n Must be DBA When to do – relationships between tables or indexes change 43 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Tuning Your SQL Statements Possible problems and remedies n 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 n “select … from pub. orders O, pub. orderlines L where O. onum = L. onum” 44 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Tuning Your SQL Statements Possible problems and remedies - more n Leading keys of indexes not specified – Remedy - give predicates on leading keys n Predicates best for index use not used – Remedy - best are “=”, IN – Almost best - BETWEEN – Good - >, >=, <, <= – Note - OR can disable optimizations n Several similar indexes not distinguished as expected – Remedy: index statistics 45 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Tuning Your SQL Statements Possible problems and remedies - example Indexes and key components: select … from Sales s, Sales_History h where s. terr = h. F 1 s. accno = h. F 2 and s. city = h. F 3 and s. col 01 = h. F 4 and s. col 02 = h. F 5 and s. regn = h. F 10 and s. segid = h. F 11 46 © 2005 Progress Software Corporation F 1 F 2 F 3 F 4 Xsales_terr F 1 F 2 F 4 F 5 F 6 F 7 Xconsumer_id F 10 F 11 Xmarket_seg DB-04 Tuning Open. Edge SQL

Tuning Your SQL Statements Finding problems n n n 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: n n n tables indexes joins predicates order – Note: only your query plans available (currently) – Must be DBA or have DBA grant privileges 47 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Getting the Query Plan n Basic form select substring("_Description", 1, 80) from pub. "_Sql_Qplan“ where "_Pnumber" = (select max("_Pnumber") from pub. "_Sql_Qplan" where "_Ptype" > 0 ); n Simplify with views select * from my_Qplan; 48 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Getting the Query Plan n Get your SQL statement – Crystal Reports: n Database menu – “Show SQL query …” n Copy into a SQL query tool – SQL Explorer, Win. SQL, DB Visualizer n n Run your statement Run the SQL statement to get query plan 49 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Query Plan - Operations What you need to find n Order of operations – Top to bottom n n Tables Indexes – Index keys n Joins – Join predicates 50 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Query Plan - Operations What you can skip n Things to ignore – Project, sort n Sometimes useful to check out – Restrict – Dynamic index 51 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Query Plan - What to Look for Single-table query n Example from simple single table select – “select … from pub. customer where custnum between 1000 and 1100” n Simple indentation to show tree form SELECT COMMAND. PROJECT [66] ( | PROJECT [64] ( | | PUB. CUSTOMER. [0]( | | | INDEX SCAN OF ( | | Cust. Num, | | | (PUB. CUSTOMER. Cust. Num) between (1000, 1100)) 52 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Query Plan - What to Look for Two-table query n 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; 53 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Query Plan - What to Look for Two-table query n 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; 55 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Tuning Your SQL Statements Special situations n n Experiment with alternate queries without cost of query execution! Use NOEXECUTE to experiment – – Test, inspect query plan, repeat until done Open. Edge 10 and Progress Version 9 NOEXECUTE is 1 word Example: select … from Table 1 t 1, Table 2 t 2 where t 1. key = 5 and t 1. key = t 2. key NOEXECUTE; 57 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Tuning Your SQL Statements Special situations n Forcing the join order – NO REORDER phrase at end of FROM clause – When all else fails! – Example: select … from Table 1, Table 2, Table 3 {NO REORDER} where … ; n Index hints – Use carefully - not deterministic 58 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Index Hint n Syntax – WITH ( INDEX ( <index name> ) ) n Choose index if it is an eligible candidate select … from PUB. ih_hist, PUB. pt_mstr with (index(pt_part_type)), PUB. 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’) 59 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

In Summary n n n Open. Edge SQL Server configuration What the server does for your application Tuning to make the server do what you want 60 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Related SQL Exchange Sessions n DB-15: Developing Performance-Oriented ODBC/JDBC Open. Edge Applications – Wednesday, 8 June, 2: 15 pm - 3: 15 pm n DB-07: Open. Edge SQL and Sonic - Using JMS with SQL Applications – Tuesday, 7 June, 9: 15 am - 10: 15 am n DB-09: Database Roadmap – Tuesday, 7 June, 2: 15 pm - 3: 15 pm 61 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Questions? 62 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Thank you for your time! 63 © 2005 Progress Software Corporation DB-04 Tuning Open.

64 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Appendix - online resources n White paper on query optimizer – http: //psdn. progress. com/library/whitepapers/sql 92/docs/s ql 92_optimizer. pdf n Collection of white papers – Getting Started with Crystal – ODBC, JDBC Configuration – Locking – Index statistics – Server configuration – Visit: n http: //psdn. progress. com/library/white_papers/sql/index 65 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Appendix - online resources n Documentation – http: //www. progress. com/products/documentation /index. ssp n Knowledge base articles – 19968, p 7843 - secondary broker – 20143 - authorization – 21676, 20007, 20327 - query plan 66 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Open. Edge SQL Server Configuration Separating 4 GL and SQL brokers/servers … syntax n 4 GL Primary Broker proserve dbname -S n -H x -n n -Mpb n -Server. Type 4 GL -minport n -maxport n -Mi n -Ma n – -n and -Mn Primary Broker Specific n SQL Secondary Broker proserve dbname -S n -H x -m 3 –Mpb n -Server. Type SQL -minport n -maxport n -Mi n -Ma n 67 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Appendix - setting default schema for ODBC n In Windows registry, find entry for – HKEY_LOCAL_MACHINESOFTWAREODBC. INI n Find sub-entry for your ODBC Data Source Name – …SOFTWAREODBC. INI<DSN NAME> n Create new string value for schema name: – Value name: …ODBC. INI<DSN NAME>Default. Schema – Value data: <your schema name> n Example: [HKEY_LOCAL_MACHINESOFTWAREODBC. INIPD 6 D 1 KPM 001] "Driver"="c: \progress-91 d\bin\PGPRO 915. DLL" "Description"="" "Host. Name"="cuttyhunk" "Port. Number"="2525" "Database. Name"="test 1" "Logon. ID"="pdadmin" "Static. Cursor. Long. Col. Buff. Len"="4096" "Default. Isolation. Level"="SQL_TXN_REPEATABLE_READ" "Array. Size"="50" "Default. Long. Data. Buff. Len"="2048" "Default. Schema"="PUB" 68 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Open. Edge SQL Security Model Commands n GRANT statement – GRANT is online – Example n n n “grant select on pub. orders to jones; ” “grant all on pub. orders to public; ” REVOKE statement – REVOKE is offline – Example n “revoke all on pub. orders from public; ” 69 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Security Considerations Privileges – Syntax: GRANTing them (2 types) n Database wide (system admin or general creation) GRANT { DBA, RESOURCE } TO user_name [, user_name ] , …; n For specified Tables or Views GRANT { privilege [, privilege ], … | ALL } ON table_name TO { user_name [, user_name ] , … | PUBLIC } [ WITH GRANT OPTION ]; Where ‘privilege’ is: { SELECT | INSERT | DELETE | INDEX | UPDATE [ ( column , . . . ) ] | REFERENCES [ ( column , . . . ) ] } n 70 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Security Considerations Privileges – Syntax: REVOKEing them (2 types) n Database wide (system admin or general creation) REVOKE { DBA, RESOURCE } FROM user_name [, user_name ] , …; n For specified Tables or Views REVOKE [ GRANT OPTION FOR ] { privilege [, privilege ], … | ALL [ PRIVILEGES ] } ON table_name FROM { user_name [, user_name ] , … | PUBLIC } [ RESTRICT | CASCADE ]; Where ‘privilege’ is: { SELECT | INSERT | DELETE | INDEX | UPDATE [ ( column , . . . ) ] | REFERENCES [ ( column , . . . ) ] } n 71 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Join method - nested loop Joined data Select … From Order O, Order. Line L Where O. ordnum = 1004 And O. ordnum = L. ordnum; Join and eval get 1 row Order 72 © 2005 Progress Software Corporation scan all rows Order. Line DB-04 Tuning Open. Edge SQL

Join method - dynamic index Joined data Or, index join Select … From Order O, Order. Line L Where O. city = ‘My. Town’ And O. shipper = L. shipper; Join dynamic index get 1 row Order 73 © 2005 Progress Software Corporation Temp table with extracted, indexed Order. Line data scan bracket rows DB-04 Tuning Open. Edge SQL

Updating sql statistics Specific categories of statistics n Table statistics – “update table statistics [for <table name>]; ” n Column statistics – “update [all] column statistics [for <table name>]; ” n Index statistics – “update index statistics [for <table name>]; ” – reads all of each index for 1, or all, tables. 74 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

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; 75 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

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 '%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; 76 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL

Appendix - query plan “definition” n n 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#. ); 77 © 2005 Progress Software Corporation DB-04 Tuning Open. Edge SQL