To develop an indexing scheme to improve performance in a database query.
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
Week 11 lectures- COP4709 Course Lectures By Week should be very useful to you for this assignment.
Spend some time to understand indexing and optimization, actually doing the assignment will take less than an hour.
Lecture - Monitoring Database Performance
You will be graded on the thoroughness of your analysis, and the effectiveness of your performance plan.