当前位置:天才代写 > 作业代写 > Query Optimization代写 Assignment代写 TPC-H database代写 Project代写

Query Optimization代写 Assignment代写 TPC-H database代写 Project代写

2020-12-23 17:47 星期三 所属: 作业代写 浏览:259

CS 564, Fall 2019

Query Optimization代写 In this assignment, you will still use SQLite3 to perform various queries on TPC-H database (introduced in assignment 2)

Assignment #4: Query Optimization

Due Date: TBA. No late days.

Project Grade Weight: 15% of the total grade

Introduction Query Optimization代写

In this assignment, you will still use SQLite3 to perform various queries on TPC-H database (introduced in assignment 2). However, for this assignment, the TPC-H database you’ll be working with in this assignment is about 1.2 GB (scale factor = 1) instead of 100MB in assignment 2. We have provided you the TPC-H database file, available here.

The goal of this assignment is to help you understand query optimization from hands-on experience. Specifically, you will be asked to rewrite several given queries to make these queries as fast as possible.

Query Optimization代写
Query Optimization代写

Queries

For each query below, you are going to optimize it through various strategies (e.g., to run with a different join order, to push grouping operations below a join, to add indexes to the database, etc.) and put your optimized query in a separate file. For example, given query2.sql, your need to save your rewritten query in query2_opt.sql. Query Optimization代写

query2.sql

query3.sql

query4.sql

Measuring Performance

Note that each file above starts with .timer on to print the execution time as below after query results.

Run Time: real 0.119 user 0.039955 sys 0.041150Query Optimization代写

As the execution time varies each time, you can run the same query five times to measure the average execution time. You only need to collect the user time (instead of real/sys time) for this assignment.

Modifications on DatabaseQuery Optimization代写

For this assignment, please download the provided database and only modify your local copy of the database. For any modifications you need to make on the database (e.g. indexes), you need to provide a file named preprocessing.sql and run this file before running your rewritten query. You also need to provide a file named clean.sql to clean up any changes you made. (Remember to clean any changes you made before measuring your baseline, i.e. provided queries)Query Optimization代写

Requirement on creating indexes: the final, optimized queries must all run against the same database, i.e., the same set of indexes and no more than three indexes should be created per table.

ReportQuery Optimization代写

Fill up a report using the template file, report.txt, describing how you optimize each query and the percentage of improvements after optimization.

  • Measure the percentage of improvements (using query2 as anexample)
    • Run query2.sql multiple times and take down the average execution time (user time) ast_base
    • Run query2_opt.sql multiple times and take down the average execution time (usertime) as t_opt
  • percentage of improvements = (t_base – t_opt) / t_base (keep two decimalpoints)
  • Note that we require your t_opt to be exactly smaller than t_base for each

GradingQuery Optimization代写

  • An optimized query that do not return the correct results will result in ZERO points for the query. So, please make sure your rewritten query always returns the correct results (exactly the same as the results returned by givenquery).
  • Detailed rubrics will be released later on piazza.

Submitting Your Assignment

Only the following files are required for this assignment:

  • sql,
  • sql,
  • sql,
  • txt
  • sql (if made any changes like creating indexes on thedatabase)
  • sql (if made any changes like creating indexes on thedatabase)

When you’re finished, please follow these instructions to submit the project:

  • Place all six required files in a directory.Query Optimization代写
  • Name this directory using the format: <netID>_P4(e.g.klklassy_P4).
  • Run:

tar -czvf <netID>_P4.tar.gz <netID>_P4

  • Submit the tar
  • To check, you can uncompress the tar file. (run: tar –xzvf<netID>_P4.tar.gz)

In this assignment, you will still use SQLite3 to perform various queries on TPC-H database (introduced in assignment 2). However, for this assignment, the TPC-H database you’ll be working with in this assignment is about 1.2 GB (scale factor = 1) instead of 100MB in assignment 2. We have provided you the TPC-H database file, available here.Query Optimization代写

The goal of this assignment is to help you understand query optimization from hands-on experience. Specifically, you will be asked to rewrite several given queries to make these queries as fast as possible.

Queries

For each query below, you are going to optimize it through various strategies (e.g., to run with a different join order, to push grouping operations below a join, to add indexes to the database, etc.) and put your optimized query in a separate file. For example, given query2.sql, your need to save your rewritten query in query2_opt.sql.

query2.sql

query3.sql

query4.sql

Measuring Performance

Note that each file above starts with .timer on to print the execution time as below after query results.

Run Time: real 0.119 user 0.039955 sys 0.041150Query Optimization代写

As the execution time varies each time, you can run the same query five times to measure the average execution time. You only need to collect the user time (instead of real/sys time) for this assignment.

Modifications on Database

For this assignment, please download the provided database and only modify your local copy of the database. For any modifications you need to make on the database (e.g. indexes), you need to provide a file named preprocessing.sql and run this file before running your rewritten query. You also need to provide a file named clean.sql to clean up any changes you made. (Remember to clean any changes you made before measuring your baseline, i.e. provided queries)Query Optimization代写

Requirement on creating indexes: the final, optimized queries must all run against the same database, i.e., the same set of indexes and no more than three indexes should be created per table.

Report

Fill up a report using the template file, report.txt, describing how you optimize each query and the percentage of improvements after optimization.

  • Measure the percentage of improvements (using query2 as anexample)
    • Run query2.sql multiple times and take down the average execution time (user time) ast_base
    • Run query2_opt.sql multiple times and take down the average execution time (usertime) as t_optQuery Optimization代写
  • percentage of improvements = (t_base – t_opt) / t_base (keep two decimalpoints)
  • Note that we require your t_opt to be exactly smaller than t_base for each

Grading

  • An optimized query that do not return the correct results will result in ZERO points for the query. So, please make sure your rewritten query always returns the correct results (exactly the same as the results returned by givenquery).
  • Detailed rubrics will be released later on piazza.

Submitting Your Assignment Query Optimization代写

Only the following files are required for this assignment:

  • sql,
  • sql,
  • sql,
  • txt
  • sql (if made any changes like creating indexes on thedatabase)
  • sql (if made any changes like creating indexes on thedatabase)

When you’re finished, please follow these instructions to submit the project:

(1)Place all six required files in a directory.Query Optimization代写

(2)Name this directory using the format: <netID>_P4(e.g.klklassy_P4).

(3)Run:

tar -czvf <netID>_P4.tar.gz <netID>_P4

(4)Submit the tar

(5)To check, you can uncompress the tar file. (run: tar –xzvf<netID>_P4.tar.gz)

Query Optimization代写
Query Optimization代写

其他代写:考试助攻 计算机代写 java代写 algorithm代写 代写CS assembly代写 function代写paper代写 r代写 金融经济统计代写 web代写 数学代写 essay代写 编程代写 report代写 algorithm代写

合作平台:天才代写 幽灵代写 写手招聘 Essay代写

 

天才代写-代写联系方式