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

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!


Spring 2013 COP4709 Assignment 11

Page history last edited by Dr. Ron Eaglin 11 years, 2 months ago

 Assignment 11




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




In Spring 2103 COP4709 Assignment 8 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.


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.