1 / 12

Session – 9 QUERY OPTIMATIZATION

Session – 9 QUERY OPTIMATIZATION. Matakuliah : M0184 / Pengolahan Data Distribusi Tahun : 2005 Versi :. OBJECTIVE. Definition of Query optimization Query Optimization Process Essential aspects of query processing in a distributed environment The importance of query optimization.

venus
Download Presentation

Session – 9 QUERY OPTIMATIZATION

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Session – 9QUERY OPTIMATIZATION Matakuliah : M0184 / Pengolahan Data Distribusi Tahun : 2005 Versi :

  2. OBJECTIVE • Definition of Query optimization • Query Optimization Process • Essential aspects of query processing in a distributed environment • The importance of query optimization

  3. Query Optimization • Query optimization refers to the process of producing a query execution plan (QEP) which represent an execution strategy for the query. • Query optimization is the process of ensuring that either total cost or the total response time for a query are minimized. • A query optimizer, the software module that perform query optimization, is usually seen as three components : search space, a cost model and search strategy

  4. INPUT QUERY SEARCH SPACE GENERATION TRANSFORMATION RULES EQUIVALENT QEP SEARCH STRATEGY COST MODEL BEST QEP Query Optimization Process Source : Principle of Distributed Database System

  5. Query Optimization Process Cont’d • SEARCH SPACE, is the set of alternative execution plans to represent the input query. Search Space is obtained by applying transformation rules. • COST MODEL predicts the cost of a given execution plan. To be accurate the cost model must have good knowledge about the distributed execution environment • The SEARCH STRATEGY explores the search space and select the best plan using the cost model

  6. Essential Aspect of Query Processing in a Distributed environment • Data and message have to be transmitted across communications lines, which has a tendency to slow down the whole process • The existence of multiple processor in the network means that there are opportunities for parallel processing and data transformation, which raises the possibility of speeding up responses.

  7. The importance of Query Optimization There can be very substantial savings in the cost of execution a query or in the length of time the user has to wait for a response. • Execution cost optimizer is to minimize the use of total system resources for a query and hence reduce its cost. • Response time can be taken to represent a cost to an organization

  8. Variation in Ways of Executing Queries • Two basic tools for use in optimization : • Query Transformation, the relational operator such as JOIN and PROJECT • Query Mapping, execution relational operator using low-level algorithms and access devices such as pointer.

  9. EXAMPLE

  10. EXAMPLE Cont’d • Query : Find the names and GP all patients who weight over 100Kg and have been treated in the orthopedic department since 1 January 2005.

  11. EXAMPLE – OPTION 1 • Move PATIENT relation to Community Care, JOIN it with SURVEY relation and move the result to the query site for joining with the other relation, shipped from the Hospital t : cost for transmitting a tuple c : overall cost Cost : (10000 * 1000c)  for PATIENT and restricted SURVEY (assume 1000 tuple result) + (200 * 1000c)  for joining this result and restricted HOSPITALIZATION Transmit : 200t + 10000t + 1000t

  12. EXAMPLE – OPTION 1 • Send the restricted HOSPITALIZATION relation to Community Care, Join it with Restricted SURVE. Join the result with PATIENT. Cost : 1000 * 200c Community Care + 100 * 10000c  Health Care Transmit : 200t + 100t + 1000t

More Related