Query Optimization Practice in Visual FoxPro

1
Query Optimization Practice in Visual FoxPro

• Project Presentation
• By Hung Jin

2
Outline

• What is Visual Foxpro?
• History
• An Review
• Database And SQL
• What is Query Optimization?
• Costs of Query
• Plans of Query
• Examples

3
History of Visual Foxpro

• FoxPro is a data-centric object-oriented and
  procedural programming language by Microsoft. It
  is derived from FoxPro (originally known as
  FoxBASE) which was developed by Fox Software
  beginning in 1984 Fox Software merged with
  Microsoft in 1992 and the software acquired
  further features and the prefix "Visual". The
  last version of FoxPro (2.6) worked under the Mac
  OS, DOS, Windows, and Unix Visual FoxPro 3.0,
  the first "Visual" version, dropped the platform
  support to only Mac and Windows, and later
  versions were Windows-only.
• FoxPro is a member of the class of languages
  commonly referred to as "xBase" languages, which
  have syntax based on the dBase programming
  language. Other members of the xBase language
  family include Clipper, dBase, and Recital. A
  history of the early years of xBase can be found
  in the dBase entry.
• Visual FoxPro, also known as VFP, is mainly used
  to write desktop database applications running on
  Windows but it can also be used to write fat
  client, middleware, and web applications. VFP 9
  was released to manufacturing on December 17,
  2004, and Microsoft representatives have spoken
  about further enhancements to be announced in
  2005.

4
An Amazon.com Review

• FoxPro disproves the notion that database
  development and administration are black
  sciences, comprehensible only to specialists.
  This database suite demonstrates that you can do
  meaningful database work in an environment that
  can be figured out intuitively. The operative
  word is meaningful. FoxPro is not a
  friendly-but-feeble database management system
  you can use only to manage your recipes. Despite
  its friendly interface, it's a serious database
  tool.
• I think, however, to be a database specialist
  gets more chances to develop a serious
  application.

5
Database in Visual FoxPro

• Database Manipulation
• Table Manipulation
• Record Manipulation
• Field Manipulation
• Relations
• Indexes

• Free Tables
• Database Tables

6
SQL in VFP

• SELECT ALL DISTINCT TOP nExpr
  PERCENTAlias. Select_Item AS
  Column_Name, Alias. Select_Item AS
  Column_Name ...
• FROM FORCEDatabaseName!Table AS
  Local_AliasINNER LEFT OUTER RIGHT
  OUTER FULL OUTER JOINDatabaseName!Table
  AS Local_AliasON JoinCondition
• INTO Destination TO FILE FileName
  ADDITIVE TO PRINTER PROMPT TO SCREEN
• PREFERENCE PreferenceName
• NOCONSOLE
• PLAIN
• NOWAIT
• WHERE JoinCondition AND JoinCondition ...AND
  OR FilterCondition AND OR FilterCondition
  ...
• GROUP BY GroupColumn , GroupColumn ...
• HAVING FilterCondition
• UNION ALL SELECTCommand
• ORDER BY Order_Item ASC DESC , Order_Item
  ASC DESC ...

7
Query Optimization In VFP

• Rushmore query optimization involves the
  efficient use of indexes to quickly find a set of
  records.
• Rushmore query optimization is used on queries
  involving multi-column, indexed restrictions.
• Index intersection alone is the most
  straightforward and obvious Rushmore feature, but
  Rushmore encompasses several other ideas.
• Index Union and COUNT() queries can be elegantly
  handled by Rushmore.

8
What is Query Optimization?

• SQL processing architecture
• Parser
• Optimizer
• Row Generator
• SQL Execution

• Enumerating a subset of all possible plans
• Estimating the cost of each plan and choosing the
  plan with the lowest estimated cost.

• Optimizer

9
The Goal of Optimizer

• The optimizer considers many factors related to
• the objects referenced and
• the conditions specified in the query.

10
The Best Plan

• Estimating Operation Cost
• Choice of access paths
• Choice of join orders
• Choice of join methods

• Estimating Result Size
• Reduction Factor
• Columnvalue 1/NKey
• Col1Col2 1/Nkey1,1/NKey2
• Improved Statistics
• Histograms

11
Relational Algebra Equivalences

• Selections
• Cascading
• Commutative
• Projections
• Cascading
• Cross-Products Joins
• Commutative

• sc1c2Cn (R) ? sc1(sc2(scn(R))))
• sc1(sc2(R))) ? sc2(sc1(R)))
• p a1 (R) ? p a1(p a2(p an(R))))
• R x S ? S x R
• R x S 8 S x R

12
Relational Algebra Equivalences

• Combination
• Select, Project
• Join, Select
• Select, join

• pa(sc(R)) ? sc(pa(R)))
• R 8c S ? sc(S x R)
• sc(R x S) ? sc(R) x S
• sc(R 8 S) ? sc(R) x R

13
Alternative Plans

• Sequential Scan all tuples
• Single-Index Access Path
• Multiple-Index Access Path
• Sorted-Index Access Path
• Index-Only Access Path

• Single-Relation Query
• Plans without Index
• Plans with Index

14
Alternative Plans

• Multiple-Relation Queries
• Left-Deep Plans
• Process each relation as single-relation
• Process two-relation plan
• Process three-relation plan
• Repeated process
• Nested Correlated Sub-query
• Potentially expensive to compute
• Causes inefficient evaluation
• Misses nested ordering plan

15
Plans Utilizing an Index

• Single-Index Access Path
• In WERE clause, each matching index offers an
  alternative access path. The result of fewest
  pages is the best path.
• Multiple-Index Access Path
• Intersection of each term in conditions of
  selection causes a sort on page id.
• Sorted-Index Access Path
• GROUP BY clause causes a sort on the index being
  grouped.
• Index-Only Access Path
• Index-only scan performs on the dense index not
  actually retrieve data records.

16
Examples

• Problems encounted in VFP application
• Large sets of data slowdown the speed of
  execution
• Rushmore just works alone because of lack of
  database administration tools.
• Limitted memory space on PC and page swapping of
  Window OS cost extra disk IO when SQL generates
  on-the-fly queries.

17
Examples

• Directions of Problems Solving
• Information collection about SQL executions
• Show plan command sys(3054, nn)
• Rebuild indexes involving the conditions of SQL
• Single-key
• Compound-key expression index
• Equivalences of SQL for the best plan
• Conditioning joins smallest result size
• Reduce levels of sub-query shortest path

18
Examples

• Table noindex.dbf
• Recordsgt 500,000

• SELECT COUNT() AS CNT FROM csc675\noindex
• WHERE price-cost lt 1 AND
• shipqty gt 10 AND
• itemno '41001'
• INTO CURSOR ss

19
Examples

• Table indexed.dbf
• Recordsgt 500,000
• Single-index

• SELECT COUNT() AS CNT FROM csc675\indexed
• WHERE price-cost lt 1 AND
• shipqty gt 10 AND
• itemno '41001'
• INTO CURSOR ss

20
Prerefenrences

• Chapter 15 A Typical Query Optimizer In Textbook
• Oracle-Introduction to the Optimizer
  http//www.lc.leidenuniv.nl/awcourse/oracle/server
  .920/a96533/optimops.htm
• VFP Documentation http//msdn.microsoft.com/vfoxpr
  o/

21
Questions ?

• Thank You!