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.