Principles of query processing
Download
1 / 55

Principles of Query Processing - PowerPoint PPT Presentation


  • 139 Views
  • Updated On :

CS5226 Week 5. Principles of Query Processing. Pang Hwee Hwa School of Computing, NUS. Application Programmer (e.g., business analyst, Data architect). Application. Sophisticated Application Programmer (e.g., SAP admin). Query Processor. Indexes. Storage Subsystem. Concurrency Control.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Principles of Query Processing' - tariq


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
Principles of query processing l.jpg

CS5226 Week 5

Principles of Query Processing

Pang Hwee Hwa

School of Computing, NUS

H. Pang / NUS


Slide2 l.jpg

ApplicationProgrammer(e.g., business analyst,

Data architect)

Application

SophisticatedApplicationProgrammer(e.g., SAP admin)

QueryProcessor

Indexes

Storage Subsystem

Concurrency Control

Recovery

DBA,Tuner

Operating System

Hardware[Processor(s), Disk(s), Memory]

H. Pang / NUS


Overview of query processing l.jpg
Overview of Query Processing

Database

Statistics

Cost Model

Query

Optimizer

Query

Evaluator

Parsed Query

QEP

Parser

High Level Query

Query Result

H. Pang / NUS


Outline l.jpg
Outline

  • Processing relational operators

  • Query optimization

  • Performance tuning

H. Pang / NUS


Projection operator l.jpg
Projection Operator

  • R.attrib, .. (R)

  • Implementation is straightforward

SELECT bid

FROM Reserves R

WHERE R.rname < ‘C%’

H. Pang / NUS


Selection operator l.jpg
Selection Operator

  • R.attr op value (R)

  • Size of result = R * selectivity

  • Scan

  • Clustered index: Good

  • Non-clustered index:

    • Good for low selectivity

    • Worse than scan for high selectivity

SELECT *

FROM Reserves R

WHERE R.rname < ‘C%’

H. Pang / NUS


Example of join l.jpg
Example of Join

SELECT *

FROM Sailors R, Reserve S

WHERE R.sid=S.sid

H. Pang / NUS


Notations l.jpg
Notations

  • |R| = number of pages in outer table R

  • ||R|| = number of tuples in outer table R

  • |S| = number of pages in inner table S

  • ||S|| = number of tuples in inner table S

  • M = number of main memory pages allocated

H. Pang / NUS


Simple nested loop join l.jpg

1 scan per R tuple

|S| pages per scan

Simple Nested Loop Join

R

S

Tuple

||R|| tuples

H. Pang / NUS


Simple nested loop join10 l.jpg
Simple Nested Loop Join

  • Scan inner table S per R tuple: ||R|| * |S|

    • Each scan costs |S| pages

    • For ||R|| tuples

  • |R| pages for outer table R

  • Total cost = |R| + ||R|| * |S| pages

  • Not optimal!

H. Pang / NUS


Block nested loop join l.jpg

1 scan per R block

|S| pages per scan

Block Nested Loop Join

R

S

M – 2 pages

|R| / (M – 2) blocks

H. Pang / NUS


Block nested loop join12 l.jpg
Block Nested Loop Join

  • Scan inner table S per block of (M – 2) pages of R tuples

    • Each scan costs |S| pages

    • |R| / (M – 2) blocks of R tuples

  • |R| pages for outer table R

  • Total cost = |R| + |R| / (M – 2) * |S| pages

  • R should be the smaller table

H. Pang / NUS


Index nested loop join l.jpg

1 probe per R tuple

Index Nested Loop Join

R

Index

S

Tuple

||R|| tuples

H. Pang / NUS


Index nested loop join14 l.jpg
Index Nested Loop Join

  • Probe S index for matching S tuples per R tuple

    • Probe hash index: 1.2 I/Os

    • Probe B+ tree: 2-4 I/Os, plus retrieve matching S tuples: 1 I/O

    • For ||R|| tuples

  • |R| pages for outer table R

  • Total cost = |R| + ||R|| * index retrieval

  • Better than Block NL join only for small number of R tuples

H. Pang / NUS


Sort merge join l.jpg
Sort Merge Join

  • External sort R

  • External sort S

  • Merge sorted R and sorted S

H. Pang / NUS


External sort r l.jpg

Merge pass 2

R2,1

Merge pass 1

R1,1

R1,2

R1,M-1

Split pass R

R0,M-1

R0,M

R0,1

External Sort R

(m-1)-way

merge

Size of R0,i = M, # R0,i’s = |R|/M

# merge passes = logM-1 |R|/M

Cost per pass = |R| input + |R| output = 2 |R|

Total cost = 2 |R| (logM-1 |R|/M + 1)includingsplit pass

H. Pang / NUS


Sort merge join17 l.jpg
Sort Merge Join

  • External-sort R: 2 |R| * (logM-1 |R|/M + 1)

    • Split R into |R|/M sorted runs each of size M: 2 |R|

    • Merge up to (M – 1) runs repeatedly

    • logM-1 |R|/M passes, each costing 2 |R|

  • External-sort S: 2 |S| * (logM-1 |S|/M + 1)

  • Merge matching tuples from sorted R and S: |R| + |S|

  • Total cost = 2 |R| * (logM-1 |R|/M + 1) + 2 |S| * (logM-1 |S|/M + 1) + |R| + |S|

    • If |R| < M*(M-1), cost = 5 * (|R| + |S|)

H. Pang / NUS


Grace hash join l.jpg
GRACE Hash Join

S

0 1 2 3

X X X

X X X

X X X

X X X

X X X

X X X

X X X

X X X

X X X

X X X

X X X

X X X

0

bucketID = X mod 4

Join on R.X = S.X

1

R S = R0 S0 +

R1 S1 +

R2 S2 +

R3 S3

R

2

3

H. Pang / NUS


Grace hash join partition phase l.jpg

Original

Relation

Partitions

OUTPUT

1

1

2

INPUT

2

hash

function

h1

. . .

M-1

M-1

M main memory buffers

Disk

Disk

GRACE Hash Join – Partition Phase

  • R  (M – 1) partitions, each of size |R| / (M – 1)

H. Pang / NUS


Grace hash join join phase l.jpg

Partitions

of R & S

Join Result

Hash table for partition

Ri (< M-1 pages)

hash

fn

h2

h2

Output

buffer

Input buffer

for Si

B main memory buffers

Disk

Disk

GRACE Hash Join – Join Phase

Partition must fit in memory: |R| / (M – 1) < M -1

H. Pang / NUS


Grace hash join algorithm l.jpg
GRACE Hash Join Algorithm

  • Partition phase: 2 (|R| + |S|)

    • Partition table R using hash function h1: 2 |R|

    • Partition table S using hash function h1: 2 |S|

    • R tuples in partition i will match only S tuples in partition I

    • R  (M – 1) partitions, each of size |R| / (M – 1)

  • Join phase: |R| + |S|

    • Read in a partition of R (|R| / (M – 1) < M -1)

    • Hash it using function h2 (<> h1!)

    • Scan corresponding S partition, search for matches

  • Total cost = 3 (|R| + |S|) pages

  • Condition: M > √f|R|, f ≈ 1.2 to account for hash table

H. Pang / NUS


Summary of join operator l.jpg
Summary of Join Operator

  • Simple nested loop: |R| + ||R|| * |S|

  • Block nested loop: |R| + |R| / (M – 2) * |S|

  • Index nested loop: |R| + ||R|| * index retrieval

  • Sort-merge: 2 |R| * (logM-1 |R|/M + 1) + 2 |S| * (logM-1 |S|/M + 1) + |R| + |S|

  • GRACE hash: 3 * (|R| + |S|)

    • Condition: M > √f|R|

H. Pang / NUS


Overview of query processing23 l.jpg
Overview of Query Processing

Database

Statistics

Cost Model

Query

Optimizer

Query

Evaluator

Parsed Query

QEP

Parser

High Level Query

Query Result

H. Pang / NUS


Query optimization l.jpg

sname

rating > 5

bid=100

sid=sid

Sailors

Reserves

Query Optimization

  • Given: An SQL query joining n tables

  • Dream: Map to most efficient plan

  • Reality: Avoid rotten plans

  • State of the art:

    • Most optimizers follow System R’s technique

    • Works fine up to about 10 joins

SELECT S.sname

FROM Reserves R, Sailors S

WHERE R.sid=S.sid AND

R.bid=100 AND S.rating>5

H. Pang / NUS


Complexity of query optimization l.jpg

Many degrees of freedom

Selection: scan versus (clustered, non-clustered) index

Join: block nested loop, sort-merge, hash

Relative order of the operators

Exponential search space!

Heuristics

Push the selections down

Push the projections down

Delay Cartesian products

System R: Only left-deep trees

D

C

B

A

Complexity of Query Optimization

H. Pang / NUS


Equivalences in relational algebra l.jpg
Equivalences in Relational Algebra

  • Selection: - cascade

    - commutative

  • Projection: - cascade

  • Join: - associative

    - commutative

R (S T) (R S) T

(R S) (S R)

H. Pang / NUS


Equivalences in relational algebra27 l.jpg
Equivalences in Relational Algebra

  • A projection commutes with a selection that only uses attributes retained by the projection

  • Selection between attributes of the two arguments of a cross-product converts cross-product to a join

  • A selection on just attributes of R commutes with join R S (i.e., (R S) (R) S )

  • Similarly, if a projection follows a join R S, we can `push’ it by retaining only attributes of R (and S) that are needed for the join or are kept by the projection

H. Pang / NUS


System r optimizer l.jpg
System R Optimizer

  • Find all plans for accessing each base table

  • For each table

    • Save cheapest unordered plan

    • Save cheapest plan for each interesting order

    • Discard all others

  • Try all ways of joining pairs of 1-table plans; save cheapest unordered + interesting ordered plans

  • Try all ways of joining 2-table with 1-table

  • Combine k-table with 1-table till you have full plan tree

  • At the top, to satisfy GROUP BY and ORDER BY

    • Use interesting ordered plan

    • Add a sort node to unordered plan

H. Pang / NUS


Slide29 l.jpg

H. Pang / NUS

Source: Selinger et al, “Access Path Selection in a Relational Database Management System”


Slide30 l.jpg

Note: Only branches for NL join are shown here. Additional branches

for other join methods (e.g. sort-merge) are not shown.

H. Pang / NUS

Source: Selinger et al, “Access Path Selection in a Relational Database Management System”


What is cheapest l.jpg
What is “ branchesCheapest”?

  • Need information about the relations and indexes involved

  • Catalogstypically contain at least:

    • # tuples (NTuples) and # pages (NPages) for each relation.

    • # distinct key values (NKeys) and NPages for each index.

    • Index height, low/high key values (Low/High) for each tree index.

  • Catalogs updated periodically.

    • Updating whenever data changes is too expensive; lots of approximation anyway, so slight inconsistency ok.

  • More detailed information (e.g., histograms of the values in some field) are sometimes stored.

H. Pang / NUS


Estimating result size l.jpg
Estimating Result Size branches

SELECT attribute list

FROM relation list

WHERE term1AND ... ANDtermk

  • Consider a query block:

  • Maximum # tuples in result is the product of the cardinalities of relations in the FROM clause.

  • Reduction factor (RF) associated with eachtermireflects the impact of the term in reducing result size

    • Term col=value has RF 1/NKeys(I)

    • Term col1=col2 has RF 1/MAX(NKeys(I1), NKeys(I2))

    • Term col>value has RF (High(I)-value)/(High(I)-Low(I))

  • Resultcardinality = Max # tuples * product of all RF’s.

    • Implicit assumption that terms are independent!

H. Pang / NUS


Cost estimates for single table plans l.jpg
Cost Estimates for Single-Table Plans branches

  • Index I on primary key matches selection:

    • Cost is Height(I)+1 for a B+ tree, about 1.2 for hash index.

  • Clustered index I matching one or more selects:

    • (NPages(I)+NPages(R)) * product of RF’s of matching selects.

  • Non-clustered index I matching one or more selects:

    • (NPages(I)+NTuples(R)) * product of RF’s of matching selects.

  • Sequential scan of file:

    • NPages(R).

  • Note:Typically, no duplicate elimination on projections! (Exception: Done on answers if user says DISTINCT.)

H. Pang / NUS


Counting the costs l.jpg

(On-the-fly) branches

sname

(Sort-Merge Join)

sid=sid

(Scan;

(Scan;

write to

write to

rating > 5

bid=100

temp T2)

temp T1)

Reserves

Sailors

Counting the Costs

  • With 5 buffers, cost of plan:

    • Scan Reserves (1000) + write temp T1 (10 pages, if we have 100 boats, uniform distribution)

    • Scan Sailors (500) + write temp T2 (250 pages, if we have 10 ratings).

    • Sort T1 (2*10*2), sort T2 (2*250*4), merge (10+250), total=2300

    • Total: 4060 page I/Os

  • If we used BNL join, join cost = 10+4*250, total cost = 2770

  • If we ‘push’ projections, T1 has only sid, T2 only sid and sname:

    • T1 fits in 3 pages, cost of BNL drops to under 250 pages, total < 2000

SELECT S.sname

FROM Reserves R, Sailors S

WHERE R.sid=S.sid AND

R.bid=100 AND S.rating>5

H. Pang / NUS


Exercise l.jpg
Exercise branches

  • Reserves: 100,000 tuples, 100 tuples per page

  • With clustered index on bid of Reserves, we get 100,000/100 = 1000 tuples on 1000/100 = 10 pages

  • Join column sid is a key for Sailors - at most one matching tuple

  • Decision not to push rating>5 before the join is based on availability of sid index on Sailors

  • Cost: Selection of Reserves tuples (10 I/Os); for each tuple, must get matching Sailors tuple (1000*1.2); total 1210 I/Os

(On-the-fly)

sname

(On-the-fly)

rating > 5

(Index Nested Loops,

with pipelining )

sid=sid

(Use hash

Index on sid)

Sailors

bid=100

(Use

clustered

index on

sid)

Reserves

H. Pang / NUS


Query tuning l.jpg

Query Tuning branches

H. Pang / NUS


Avoid redundant distinct l.jpg
Avoid Redundant DISTINCT branches

  • DISTINCT usually entails a sort operation

  • Slow down query optimization because one more “interesting” order to consider

  • Remove if you know the result has no duplicates

SELECT DISTINCT ssnum

FROM Employee

WHEREdept = ‘information systems’

H. Pang / NUS


Change nested queries to join l.jpg
Change Nested Queries to Join branches

  • Might not use index on Employee.dept

  • Need DISTINCT if an employee might belong to multiple departments

SELECT ssnum

FROM Employee

WHEREdept IN (SELECT dept FROM Techdept)

SELECT ssnum

FROM Employee, Techdept

WHERE Employee.dept = Techdept.dept

H. Pang / NUS


Avoid unnecessary temp tables l.jpg
Avoid Unnecessary Temp Tables branches

  • Creating temp table causes update to catalog

  • Cannot use any index on original table

SELECT * INTO Temp

FROM Employee

WHEREsalary > 40000

SELECT ssnum

FROM Temp

WHERE Temp.dept = ‘information systems’

SELECT ssnum

FROM Employee

WHERE Employee.dept = ‘information systems’

AND salary > 40000

H. Pang / NUS


Avoid complicated correlation subqueries l.jpg
Avoid Complicated Correlation Subqueries branches

  • Search all of e2 for each e1 record!

SELECT ssnum

FROM Employee e1

WHERE salary =

(SELECT MAX(salary)

FROM Employee e2

WHERE e2.dept = e1.dept

SELECT MAX(salary) as bigsalary, dept INTO Temp

FROM Employee

GROUP BY dept

SELECT ssnum

FROM Employee, Temp

WHERE salary = bigsalary

AND Employee.dept = Temp.dept

H. Pang / NUS


Avoid complicated correlation subqueries41 l.jpg
Avoid Complicated Correlation Subqueries branches

  • SQL Server 2000 does a good job at handling the correlated subqueries (a hash join is used as opposed to a nested loop between query blocks)

    • The techniques implemented in SQL Server 2000 are described in “Orthogonal Optimization of Subqueries and Aggregates” by C.Galindo-Legaria and M.Joshi, SIGMOD 2001.

> 1000

> 10000

H. Pang / NUS


Join on clustering and integer attributes l.jpg
Join on Clustering and Integer Attributes branches

  • Employee is clustered on ssnum

  • ssnum is an integer

SELECT Employee.ssnum

FROM Employee, Student

WHERE Employee.name = Student.name

SELECT Employee.ssnum

FROM Employee, Student

WHERE Employee.ssnum = Student.ssnum

H. Pang / NUS


Avoid having when where is enough l.jpg
Avoid HAVING when WHERE is enough branches

  • May first perform grouping for all departments!

SELECT AVG(salary) as avgsalary, dept

FROM Employee

GROUP BY dept

HAVING dept = ‘information systems’

SELECT AVG(salary) as avgsalary

FROM Employee

WHERE dept = ‘information systems’

GROUP BY dept

H. Pang / NUS


Avoid views with unnecessary joins l.jpg
Avoid Views with unnecessary Joins branches

  • Join with Techdept unnecessarily

CREATE VIEW Techlocation

AS SELECT ssnum, Techdept.dept, location

FROM Employee, Techdept

WHERE Employee.dept = Techdept.dept

SELECT dept

FROM Techlocation

WHERE ssnum = 4444

SELECT dept

FROM Employee

WHERE ssnum = 4444

H. Pang / NUS


Aggregate maintenance l.jpg
Aggregate Maintenance branches

  • Materialize an aggregate if needed “frequently”

  • Use trigger to update

  • create trigger updateVendorOutstanding on orders for insert as

  • update vendorOutstanding

  • set amount =

  • (select vendorOutstanding.amount+sum(inserted.quantity*item.price)

  • from inserted,item

  • where inserted.itemnum = item.itemnum

  • )

  • where vendor = (select vendor from inserted) ;

H. Pang / NUS


Avoid external loops l.jpg
Avoid External Loops branches

  • No loop:

    sqlStmt = “select * from lineitem where l_partkey <= 200;”

    odbc->prepareStmt(sqlStmt);

    odbc->execPrepared(sqlStmt);

  • Loop:

    sqlStmt = “select * from lineitem where l_partkey = ?;”

    odbc->prepareStmt(sqlStmt);

    for (int i=1; i<200; i++)

    {

    odbc->bindParameter(1, SQL_INTEGER, i);

    odbc->execPrepared(sqlStmt);

    }

H. Pang / NUS


Avoid external loops47 l.jpg
Avoid External Loops branches

  • SQL Server 2000 on Windows 2000

  • Crossing the application interface has a significant impact on performance

Let the DBMS optimize

set operations

H. Pang / NUS


Avoid cursors l.jpg
Avoid Cursors branches

  • No cursor

    select * from employees;

  • Cursor

    DECLARE d_cursor CURSOR FOR select * from employees;

    OPEN d_cursorwhile (@@FETCH_STATUS = 0)

    BEGIN

    FETCH NEXT from d_cursorEND

    CLOSE d_cursor

    go

H. Pang / NUS


Avoid cursors49 l.jpg
Avoid Cursors branches

  • SQL Server 2000 on Windows 2000

  • Response time is a few seconds with a SQL query and more than an hour iterating over a cursor

H. Pang / NUS


Retrieve needed columns only l.jpg

All branches

Select * from lineitem;

Covered subset

Select l_orderkey, l_partkey, l_suppkey, l_shipdate, l_commitdate from lineitem;

Avoid transferring unnecessary data

May enable use of a covering index.

Retrieve Needed Columns Only

H. Pang / NUS


Use direct path for bulk loading l.jpg
Use Direct Path for Bulk Loading branches

sqlldr directpath=true control=load_lineitem.ctl data=E:\Data\lineitem.tbl

load data

infile "lineitem.tbl"

into table LINEITEM append

fields terminated by '|'

(

L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE DATE "YYYY-MM-DD", L_COMMITDATE DATE "YYYY-MM-DD", L_RECEIPTDATE DATE "YYYY-MM-DD", L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT

)

H. Pang / NUS


Use direct path for bulk loading52 l.jpg
Use Direct Path for Bulk Loading branches

  • Direct path loading bypasses the query engine and the storage manager. It is orders of magnitude faster than for conventional bulk load (commit every 100 records) and inserts (commit for each record).

H. Pang / NUS


Some idiosyncrasies l.jpg
Some Idiosyncrasies branches

  • OR may stop the index being used

    • break the query and use UNION

  • Order of tables may affect join implementation

H. Pang / NUS


Query tuning thou shalt l.jpg
Query Tuning – Thou Shalt … branches

  • Avoid redundant DISTINCT

  • Change nested queries to join

  • Avoid unnecessary temp tables

  • Avoid complicated correlation subqueries

  • Join on clustering and integer attributes

  • Avoid HAVING when WHERE is enough

  • Avoid views with unnecessary joins

  • Maintain frequently used aggregates

  • Avoid external loops

H. Pang / NUS


Query tuning thou shalt55 l.jpg
Query Tuning – Thou Shalt … branches

  • Avoid cursors

  • Retrieve needed columns only

  • Use direct path for bulk loading

H. Pang / NUS


ad