Sample Database

For this chapter, and the example walkthroughs, we looked at the various sample databases available; and after playing with most of them we decided that a simpler case would work just as well, be easier for readers to reproduce in their own environment, and be simpler to use to illustrate how each feature works.

The sample workload consists of a key table called people that we want to insert into as fast as we can. Each insert into this table has to do lookups in three reference tables, and then perform some calculations to determine the rest of the data to be inserted. The three reference tables are a list of male names, a list of female names, and a list of last names.

There is a procedure that can be used to randomly generate names, MakeNames, which has two arguments. The first is the number of names to insert (the default is 100). The second indicates which type of name to insert: a male name ("B" for Boys), a female name ("G" for Girls), or a last name (the default, or "L" for Lastnames)

There are two ways to inserts rows in the people table. The first is used to randomly distribute data over the time period of the database. This simulates an existing data set distributed over a time period. This insert procedure does random number lookups to find a male and female first name, and then a last name. It then randomly generates a date of birth for a date between today and 110 years ago. Then, with all this new data, two rows are inserted into the people table: one male, one female. This is the data load procedure that can be run to initially populate the people table.

The other data insert procedure is used primarily for the partitioning example, and is called Birth. This procedure does the same male and female name lookup, but then inserts two records using the current date/time as the DOB. This simulates inserting new records into one end of the table.

The update part of the workload consists of several procedures. One is called marriage, which finds a male entry and a female entry, and updates the female last name to match the male last name. Another stored procedure is called death. This finds the oldest record in the database and sets its DOD to be the current date/time.

All the individual procedures also have other procedures that call them in a loop to make it easier to apply a load to the system. These looping procedures are called do<something> where something will either be people, births, marriages, or deaths. The do<something> procedures all take two arguments, both of which are defaulted. The first argument is the number of times to call the underlying procedure (defaults to 10,000). The second indicates how frequently to report stats on the number of inserts, and time taken (defaults to 500)

To help clarify the simplicity of this database design, here are the only tables:

 create table people (   personID uniqueidentifier DEFAULT NEWSEQUENTIALID(),   firstName varchar(80) not null,   lastName varchar(80) not null,   DOB datetime not null,   DOD datetime null,   sex char(1) not null ) create table BoysNames   (   ID int identity(0,1) not null,   Name varchar(80) not null   ) create table GirlsNames   (   ID int identity(0,1) not null,   Name varchar(80) not null   ) create table LastNames   (   ID int identity(0,1) not null,   Name varchar(80) not null   ) 

You can find scripts to create the database, tables, indexes, stored procedures, and scripts for partitioning on the Web site for this book at www.wrox.com.