| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • Whenever you search in PBworks, Dokkio Sidebar (from the makers of PBworks) will run the same search in your Drive, Dropbox, OneDrive, Gmail, and Slack. Now you can find what you're looking for wherever it lives. Try Dokkio Sidebar for free.

View
 

COP4709 Assignment 12A - Performance of queries

Page history last edited by Dr. Ron Eaglin 7 years, 8 months ago

 Assignment 12

 

Objectives

 

To develop an indexing scheme to improve performance in a database query.

 

Assignment

 

In a previous assignment you used the housing database with lookup tables. For this assignment you will use the same database and the system and queries you developed. You may have noticed with the larger amount of data that was in the queries in Assignment 8 that there was a delay in the queries. You WILL NEED to have an implementation of the database with the Category and Code table structure for this assignment.

 

Step 1 - The query you will be optimizing for is the initial query I gave you in the assignment (query below), Analyze this query and provide a report of where the performance bottlenecks occur. This report should include times for the query before any optimizations occur.

 

SELECT   [Codes].[Description],  COUNT(*) As 'Number Units' 
FROM     RawData, Codes, Categories 
WHERE    RawData.ACR = Codes.Name
  AND    Codes.CategoryName = 'ACR'
  AND    Categories.Name = Codes.CategoryName
GROUP BY [Codes].[Description]

 
Description                        Number Units
House on less than one acre              170745
House on one to less than ten acres       20940
House on ten acres or more                 2586

 

Step 2 - Optimize the database (using any technique) for this query. Provide a report of the analysis of the query after the optimization, including the optimizations used. Report query times pre and post optimization.

 

Format of report

 

Part I - Pre-Optimization performance

Part II - Optimization used

Part III - Post-Optimization performance

 

Information

 

Week 11 lectures- COP4709 Course Lectures By Week should be very useful to you for this assignment.

 

Estimated Completion Time

 

Spend some time to understand indexing and optimization, actually doing the assignment will take less than an hour.

 

Supporting Lectures 

 

Lecture - Monitoring Database Performance

Lecture - Database Indexing

 

Questions and Answers

 

 

 

External Resources

 

 

 

Grading Criteria

 

You will be graded on the thoroughness of your analysis, and the effectiveness of your performance plan.

 

Comments (0)

You don't have permission to comment on this page.