Database Optimization Project
Installing MySQL
We installed the MySQL Server 5.0 for Windows. We selected the Community version and downloaded the “Windows Essentials” msi file. To have the luxury of working the MySQL Query Browser, we downloaded the GUI Tools package. Once we ran the msi file, we navigated to “MySQL Server Instance Config Wizard” from the Start menu and followed the wizard to the end where we were able to set a root password (which is a pain to recover, so don’t forget it!) for administrative access to the MySQL Server. After installing the GUI Tools, we navigated to “MySQL Query Browser” from the Start menu and input ‘localhost’ as the Server Host, ‘root’ as Username, the root password, and the Default Schema we wanted as ‘mrp’. We verified that Port 3306 was input and open via our Firewall as well. The configuration looked like this:

To load up the MRP database, we ran the “MRP Schema & Data.sql” script provided by selecting File > Open Script… in the Query Browser and executing it. From here we were able to cut and paste certain queries in the “MRPQueries.doc” Word file provided and analyze them using the Query Browser. Such analysis led to optimization changes in table data types as well as the addition of helpful indices.
Recommending Indices
As a group, we viewed each table in the database and looked for opportunities for indexing. As a general rule, we created indices for tables that included a primary key and then a secondary key (another ID which type included INTEGER). If the tables had a primary key but not an evident foreign key, then we made sure that the primary key had an index. An example of the conventions we used to name the indices is as follows:
As applied to the Amenity table

There were some tables that we didn’t have a primary key but had a foreign key (e.g. unavailabledate table). For those tables we added an index for the foreign key. We did not take the time to add indexes to every table. We only indexed the tables that were associated to the queries that we were testing.
Simple Query
Amenity Table
To start our optimization process, we executed a simple query from the Amenity Table. The purpose of the query is to grabs all amenities where the groundID is greater than one. The query execution took .043 seconds without any indexes. With adding campID as an index it was resulted in a faster execute time of .0409 seconds. Although the execution time difference isn’t very significant, it taught us that indexing keys can have an impact on execution time.
SELECT * FROM Amenity WHERE groundID>’1’
More complex Queries
Camp Reservation
The first complex query that we executed pulled reservation data by stake and ward out of the Reservation and ReservationByCampsite tables. By not changing anything and running the queries as is the execution time was 1.7 seconds. In order to decrease the execution time, we deleted the casts on the startDate, endDate, and wardID attributes. After deleting the cast and changing the start date and end date type from VARCHAR[50] to DATETIME, we included actual dates in the “firstdate” and “lastdate” parameters. These changes improved the execution time to 1.6193 seconds.
We also added an index called “campID_IX” which includes the campID and reservID columns and is a generic “INDEX” kind with the binary tree index type. Adding this index improved the execution time to 1.42 seconds. Therefore, indexing and changing the data type to be more appropriate saved us 0.3 seconds of execution time. Although that may not seem significant for a small database, an enterprise database that receives thousands of queries would benefit from such an optimization.
SELECT r.reservid, r.reservedBy, r.stake, r.ward, r.groupType,
r.startDate AS arrival,
r.endDate AS departure,
(SELECT stakeName FROM Stake s
WHERE s.stakeID=r.stake) AS stakeName,
(SELECT wardName FROM Ward w
WHERE w.wardID =r.ward) AS wardName
FROM Reservation r
INNER JOIN ReservationForCampsite rfc ON (r.reservid=rfc.reservid)
WHERE ((r.startDate >= ‘2006-07-10′ AND
r.startDate <= ‘2007-04-15′) OR
(r.endDate >= ‘2006-07-11′ AND
r.endDate <= ‘2007-04-16′))
ORDER BY departure
Agents Stake
Again like before we wanted to have a benchmark time before we tried to optimize the queries which resulted in a 4.8 second execution time. As our first try at optimizing it we indexed with both columns CampID and reservID. This help reduce the execution time down to 2.5, but we thought we could still do better then that. We figured that since indices worked so well let’s keep adding more. So we have been adding indices to foreign keys and primary keys of any table that this query is referencing. Execution times were recorded for each added index. Here are the results.
• .18 seconds when stakeID on the Stake table was indexed
• .16 seconds when areaID and StakeID were indexed
In short we were able to get a query that executed at a 4.8 second time down to a .16 second execute time. This is a dramatic increase in performance and would surely save the customer from waiting a lengthy amount of time for a query to execute.
SELECT startDate, endDate, numPeople, groupType,
ward, reservedBy, r.campID,
r.contactPhone, contactAddress,
r.contactEmail, specialRequests,
managerComments, confirmed, totCost, c.name as propertyName,
managerName, c.contactPhone AS manPhone,
c.contactEmail as manEmail,
g.name AS spName, street, city, state, zip,
s.stakeName AS ssname, a.stakeName AS asname
FROM Reservation r, Camp c, AgentStake g, Stake s, Stake a
WHERE r.campID=c.campID AND c.campID=g.CampID AND
s.stakeID=stake AND a.stakeID=g.stakeID AND reservID>’100′
Select Name, Accept Reservation
Before we added indices the query ran at a speed of 1.3 seconds, but shortly after adding indices to all of the tables involved, it executed at a speed of .1415 seconds. The indices we added were the following:
• campId_IX (consisting of campId & reservID) to ‘reservation’ table
• campID_IX to ‘unavailabledate’ table
• groundID_IX (consisting of groundID & siteID) to ‘campsite’ table
We changed the firstScheduled and lastScheduled columns from VARCHAR(50) to DATETIME datatypes. We did the same for the nextfirstScheduled and nextlastScheduled columns as these also should be represented as dates. These four changes incrementally increased the speed of this query. We also changed ground_ID in the campsite table from VARCHAR[10] to INTEGER.
SELECT name, acceptReservations,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID AND confirmed<>’N') AS Confirmed,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID AND confirmed=’N') AS Unconfirmed,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID) AS Total,
(SELECT COUNT(*) FROM UnavailableDate WHERE UnavailableDate.campID=c.campID) AS Unavailable,
(SELECT COUNT(*) FROM Campsite s, Campground g WHERE s.groundID=g.groundID AND g.campID=c.campID) AS Sites,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-12′)) As Day0,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-13′)) As Day1,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-14′)) As Day2,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-15′)) As Day3,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-16′)) As Day4,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc
WHERE r.reservID=rfc.reservID AND r.startDate=r.endDate AND
r.campID=c.campID AND r.startDate IN (’2006-04-17′)) AS Day5
FROM Camp c
ORDER BY name
Matt Thomas
Jeff Brinkerhoff
Mike White
Final….Thoughts
Blogging has never been a strong point for me, but since this class I kind of started to enjoy it. This entry is an overview of what was taught and learned in the class throughout the semester. I will describe my thoughts on the topics and the assignments. I did learn some valuable things throughout the semester and plan to use and implement these things at my job and at home.
Enterprise Application Integration
This is not possible, although this would be wonderful. The reason that I say this is because there are too many different systems out there. These systems are built on many different platforms in many different languages that it would almost be impossible to assimilate all of the data between these systems together. If this were to be successful then managers would be much more informed with correct information that they can base their decisions.
Currently at my job I work with an application that is very proprietary. This application allows us to create attributes inside of this tool. These attributes such as a text box are not stored in a relational way which makes it very difficult to retrieve any data from this application. You have to use an OLE DB provider to access anything. It is rather annoying and it makes my life difficult. I bring this up because this is one example of a systems being very hard to get data out of and I can not imagine integrating it with other systems. I think this would be very hard.
The other thing with application integration is the strain on the servers. We are already consuming tons of energy to run these machines, but how much more would we need to make integrate all of this data. Along with energy costs there would need to be an increase in bandwidth and number of servers. I just don’t see this being feasible.
Service Oriented Architecture and Web Services
Coming into the class I had never really heard much about we services or service oriented architecture. The two are pretty similar in nature in the fact that SOA is the way to design a reusable piece of software and a web service is a the ability to connect to someone else’s code with out actually seeing their code or knowing it. All they would have to know is what to expect in return.
It was funny because once we started to talk about it in class then I started noticing it everywhere. It is very similar to buying a car, in the fact that which ever one you just bought you suddenly see a lot more of them on the road because you have one too. One of the first times that I heard about it since learning it in class was at work. One of the project managers was talking about using web services with this new software they were looking to purchase. It was a perfect example of the real world application of all of this.
The Web Services assignment really got my feet wet. I had learned it at a very high level conceptually and now I needed to take a deeper look the nuts and bolts of the whole thing. At first I kept putting it off because I didn’t know what was being asked to do. Once I buckled down and realized that I had to get it done I really focused and got it done. It was cool to see that it actually worked correctly. I know I felt like I had really accomplished something that I could use throughout my life.
MDA
It seems as this solution seems like pie in the sky like application integration does. The reason that I say this is because it would seem that your models don’t always capture what you want the application to do. I missed the OlivaNova day in class so I am not exactly sure how the tool works, but it is worth using. I hate writing simple parts of code to help set everything up so that I can start programming logic, but this might work.
With trying to abstract away as much as possible from machine language this might just be another step. We have layers on top of layers that make it easier to write code for so maybe this is just one of those layers. We will see if it really works.
Database Optimization
I love databases because it makes finding things so easy. I have written code before that would interact with databases, but I have never needed to optimize it. The main reason for that is because I have never had the traffic on my site to necessitate it. I knew from Dr. Gary Hansen’s class that queries could be optimized so that run the fastest. I never really knew how to do this, but this class taught me this. I really don’t see myself ever optimizing a database so I really didn’t see a lot of value and doing the assignment.
None the less I found that indexes and making sure data types were appropriate to what they were would definitely help to improve performance executing a query. With a database that is executing queries all the time shaving off a .001 second would make a huge difference because if they were any longer that would just have a cascading affect on all other queries that would be run.
Event Logging/Monitoring
There are bugs in software everywhere. Sometimes these errors are never captured by some system or file which makes it impossible to fix the problems. With event loggers such as log4j we see that you can make it so that ever error that occurs in an application is caught and reported somehow. What is really need is that you can put the severity of the error right in to the error. That way after it has been logged you can create a condition to escalate it according to its severity.
With enterprise systems having millions of lines of code it is critical that you track and log these errors. I have not had any personal experience with event logging other then the standard Microsoft event viewer, which doesn’t provide a lot of information regarding the error. There is a lot of benefit to using an event logger.
Team Paper
The class so far had been very high level which is sometimes good. The team paper allowed me to learn and a deeper level. I had to do research on load-balancing and how it worked. It was a great chance to dive deep and understand the algorithms that some people enterprises use. With turning in the paper to you not everyone was able to benefit. By having us do an oral presentation we were able to discuss what we had learned and also get some feedback. I felt that the paper was a great learning experience for me.
MDA - Part 3
“Model Driven Architecture (MDA) is a great way to manage complexity, achieve high levels of re-use and significantly reduce the development effort required on software development projects. With support for MDA built in, Enterprise Architect helps bridge the gap between the analysis and implementation.” Enterprise Architect by Sparx Systems is a UML tool that can then be used to create the code for your application.
It only cost $335 and allows you to do all of your diagramming and then allows you to implement it. If you are creating small modules that can then be put together in the end then this tool would work for you. I have seen this tool first hand since BYU has some licenses for it, although I haven’t had a lot of experience with it. It seemed a little cryptic, but I am sure I just did not have enough experience with it. From what Sparx says it does this product is great and would be beneficial to any organization.
No commentsMDA - Part 2
Once again the value of using MDA in any organization is cost savings. There is tremendous savings in hours spent writing code to debugging it. With MDA, as long as your models are correct, you will have perfect code that does not have bugs. Now we all know that this is highly unlikely, but maybe one day it will be perfect. For now we will just have to wait and see. I want to quickly talk about how I would determine whether to use MDA or not.
To make sure that MDA was a viable option for building our systems I would first have to make sure that we had well documented our processes so that we could build UML models based on those processes. The other important thing that I would look for would be that we looked into how complex and how customizable we wanted the application after it was built. Making sure that this application would be built to our specifications with the functionality that we needed would be one of the most important factors in the decision to use MDA or not.
It seems when you are working with an industry that has processes that are well defined then MDA would be a great approach to take. I am going to work for Omniture, who does web analytics, once I graduate. Omniture is a company that has built it self of having amazing databases to store all of the different information that it gathers from people visiting their clients’ sites. I see MDA playing a huge role in building their applications. Their products are so complex that I do not think creating a UML model of what they want their system to be like will be practical.
No commentsMDA - Part 1
Overview
MDA is the idea of creating Unified Modeling Language (UML) models, which are written in a way which makes them technology-independent. This is adding a layer of abstraction to the already many layers that exist. By creating more and more levels of abstraction it makes it easier for applications to be developed and deployed. MDA standard addresses the software development life cycle of designing, deploying, integrating, and managing systems.
These UML models will then be used to create code in any language such as Java, .Net, PHP, Ruby. So in a sense it allows you to create the models so that depending upon what platform you are using the code can be generated with that specific language.
One of the main promoters of MDA is the Object Management Group (OMG). Their main role since they were founded in 1989 has been creating and promoting standards. This consortium and originally consisted of eleven companies including; Hewlett-Packard, IBM, Sun Microsystems, Apple Computer, American Airlines and Data General.
Value Proposition
The main reason that an organizations would want to use MDA is simply for cost savings. Through MDA there will be a reduced number of hours spent on writing code which will say on the overall cost of an application. With having the code generated automatically this greatly reduces the time spent on debugging and fixing problems with the code.
Tools and Vendors
From all of the websites that I visited is seemed that they offered some if not all of the type of tools that are listed below.
- Creation Tool: A tool used to elicit initial models and/or edit derived models.
- Analysis Tool: A tool used to check models for completeness, inconsistencies, or error and warning conditions.
- Transformation Tool: A tool used to transform models into other models or into code and documentation.
- Composition Tool: A tool used to merge several different models together.
- Test Tool: A tool used to test the models
- Simulation Tool: A tool used to simulate the execution of a system represented by a given model.
- Metadata Management Tool: A tool used to handle model information and the relationships between different versions
- Reverse Engineering Tool: A tool used to turn legacy systems into working models
It seems that there are many software packages that will help you with achieving what is desired from using a MDA approach.
No commentslog4j
log4j is a logging utility that was developed by Ceki Gülcü and is part of the Apache Logging Services project under the umbrella of the Apache Software Foundation. This project was designed to help minimize the number of println statements that one would write throughout their code. We have all done it, written line and line again of “debugging code” in an effort to make your code work. What log4j does is make it so that you have any exceptions caught and logged to any location. They can be stored in a flat file, database, an email alert, or anyway that you want it to delivered.
The other cool thing about log4j is that you can capture the severity of the error using these six logging levels:
- FATAL
- ERROR
- WARN
- INFO
- DEBUG
- TRACE
When you know the severity of the error you can allocate appropriate resources to address the issue. To help better understand how this would be used in your code I have copied and pasted from the lo4j manual.
import com.foo.Bar;
// Import log4j classes.
import org.apache.log4j.Logger;
import org.apache.log4j.BasicConfigurator;
public class MyApp {
// Define a static logger variable so that it references the
// Logger instance named “MyApp”.
static Logger logger = Logger.getLogger(MyApp.class);
public static void main(String[] args) {
// Set up a simple configuration that logs on the console.
BasicConfigurator.configure();
logger.info(”Entering application.”);
Bar bar = new Bar();
bar.doIt();
logger.info(”Exiting application.”);
}
}
package com.foo;
import org.apache.log4j.Logger;
public class Bar {
static Logger logger = Logger.getLogger(Bar.class);
public void doIt() {
logger.debug(”Did it again!”);
}
}
No comments
Canyonlands
On Friday the 23rd we left Provo to go down to Island in The Sky in the Canyonlands National Park. It was raining as we traveled down there, but that did not deter us at all. By the time we got into the park it wasn’t raining but things were damp. We quickly set up our camp with tents and rain flies because it was for sure going to rain. We went to a short hike and then came back to camp. We played some cards and then hit the sack. I got into my new Marmot Limelight 2P tent with my new Therm-a-Rest Prolite 4. Having a self inflating sleeping pad helped me sleep better through out the night. I woke up in the morning to pouring rain. The amazing thing was that there was not one drop of water in my tent, I love this tent. Others around me had some water inside their tent, but some of this was do to condensation building up on the inside of the tent. The nice thing about the Limelight is that the rain fly sits on top of the poles and does not touch the tent material which makes it so condensation doesn’t come through from the rain fly. After we had laid around for an hour or so we decided to get up and hit some of the 1 mile hikes. We saw Mesa Arch, and Grandview Overlook. After those hikes we went back to the camp site and had lunch. After lunch a couple of my friends took off and headed home. We decided to go and do the Gooseberry Trail which was a 5.4 mile round trip hike with a change in elevation of 1,400 feet. It was a pretty cool trail that had a quick decent and only took us 3 hours roundtrip. All in all it was a good trip and a chance to use my new tent minus the little mishap with tent seen in the picture below.
1 commentGearing Up
As I look forward to graduation I find myself wanting to start buying things in anticipation of my increased salary. This summer I plan on doing a lot of outdoor activities such as biking, hiking, and camping. There are several places I want to go including The Wave, King’s Peak, Lone Peak, Havasupai Falls, Blackbox and others. Since I already had a sleeping bag the next big thing I needed would be a good tent. I have a 5 man Wal-Mart special tent that weighs 20 pounds, but this would not be a good tent to take on a backpacking trip. So I spent a little money and got a new 2 person tent. I purchased a Marmot Limelight 2P on moosejaw.com with the optional gear loft and footprint. With the optional footprint it allows for a base set up with just the rain fly and the footprint. When the package arrived at my apartment I set it up to see how big it was and what it looked like. From my little experience with the tent so far I like it. I am excited to use it the next time that I go camping which will most likely be in the Canyonlands at the end of March. I think my next purchase will be an internal frame backpack.
Fun With Post-It Notes
They say laughter is the best medicine, I couldn’t agree more! On Saturday night as I dropped Megan off at her apartment we started working on her birthday prank. In an effort to be original I decided that I would use Post-It Notes and cover her car with them. We started at 1 am, thinking this will be so fun, and by 5 am we just wanted it to be done. We had 6 people there helping us put all the sticky notes on her car and yet it still took such a long time. We started with 12 packets each having 100 Post-It Notes thinking that would be plenty, but after two more trips to the store at 2 am and at 3 am we realized that was not nearly enough. We finished the whole car except the driver side window. We wanted to drive it across the street and put it in the school parking lot so that everyone could see it. At 5 am we drove it across the street and finished the one window. The end result was 3,200 Post-It Notes used and 4 hours of work, all creating a masterpiece. Below are a few pictures.
1 commentKevin Rollins

Today Kevin Rollins, former CEO of Dell, came and spoke to the Collegiate Entrepreneurs Organization on campus at BYU. We were all stuffed into a small auditurium style room in the law building. He spoke about the entrepreneur spirit. He said that the door has not been closed on entrepreneurial opportunities, but that you have to be very thoughtful but also visionary approach. He said that the internet is continuing to grow and we can not comprehend how it will change our lives. He focused his lecture on technology and some areas that have not been totally capitalized on yet including:
- Community Based
- Globalization
- 2/3 of the worlds population has not been able to use the internet like we in the US experience
- 1 billion users on the internet currently and that will double in 1 year
- They will start off in an advanced stage such as starting with wireless not wired access
- They will start off with laptops, or something smaller and cheaper, instead of desktops
- The internet will penetrate new markets
- Wireless
- WIMAX is going to make wireless easy and faster
Kevin also said that we need to expand our horizons on how we use this new. The US market is a very mature market with some opportunities, but right now there are a lot of opportunities in countries outside the US. Other countries will be able to grow quickly because we have already taken the time to develop the technologies and test it. With this increase in data we have to be willing to constantly be learning. Kevin said that leadership capabilities need to exceed what we are doing right now. We need to get better, faster and assimilate more then we have ever had to before. He closed the lecture with four suggestions:
- Ever learning as a leader
- Continual balance
- There is a time and a season for everything
- Understand where you are and prioritize
- Give back
- The responsibility that you have with that success is that you give back
- Maintain the standards you started with
- He has never felt he has had to compromise his standards
- You do not have to feel you need to compromise to be successful


