| 
  • 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!

View
 

Spring 2014 COP4709 Assignment 11

This version was saved 10 years, 1 month ago View current version     Page history
Saved by Dr. Ron Eaglin
on February 24, 2014 at 4:20:16 pm
 

 Assignment 11

 

Objectives

 

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

 

Assignment

 

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

 

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.