Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 354

sex CHAR(1) not null

)
go
CREATE TABLE boysnames
(
ID INT IDENTITY(0,1) not null,
[name] VARCHAR(80) not null
)
go
CREATE TABLE girlsnames
(
ID INT IDENTITY(0,1) not null,
[name] VARCHAR(80) not null
)
go
CREATE TABLE lastnames
(
ID INT IDENTITY(0,1) not null,
[name] VARCHAR(80) not null
)
go

The scripts to create the database, tables, and stored procedures can be found at www.wrox.com .Ifyou
want to follow the examples you should run createDatabase.sql , createTables.sql , createStored-
Procedures.sql ,and loadNames.sql before continuing.

The key table is people and you want to insert into it as fast as you can. Each insert into this table has
to do lookups in three reference tables and then 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.

For reference, here is a description of the stored procedures that are used:

❑

usp_namesInsert: Randomly generates names for the three reference tables. Used by the load-
Names.sql script, which should be run before starting.

❑ usp_generateDOB: Generates random birth dates within the last 110 years. Used by

usp_peopleInsert .

❑ usp_peopleInsert: Inserts a boy and a girl to the people table.

❑ usp_loopPeopleInsert: Runs usp_peopleInsert 10000 times. This default can be overriden by

passing a value to the @people parameter.

❑ usp_birthInsert: Inserts a new boy and girl record with today's date as the date of birth.

❑ usp_loopBirthInsert: Runs usp_birthInsert 10000 times. This default can be overriden by

passing a value to the @people parameter.

❑

usp_marriageUpdate: Matches a boy and girl together and updates the girl's surname to be the
same as the boy's surname.

❑

usp_loopMarriageUpdate: Runs usp_marriageUpdate 10000 times. This default can be overri-
den by passing a value to the @people parameter.