The database and the table in C#

Creation Code 128C in C# The database and the table

CREATE TABLE persons ( person_id int NOT NULL, first_name nvarchar(50) NULL, last_name nvarchar(50) NOT NULL, birth_date datetime NULL, email varchar(80) NOT NULL, other_data char(73) NOT NULL DEFAULT ' ' CONSTRAINT pk_persons PRIMARY KEY (person_id)) CREATE INDEX email_ix ON persons (email)

The aim is to implement queries that permit users to look up persons by searching on parts of an email address. The column other_data is a filler to make the table wider; in a real-life table you would instead find other columns, such as address, city and so on. If you want to work with the examples in this chapter, you should get the file Ch.17.zip from the download area for this book. This file includes a number of scripts that we will work with. The scripts assume that you have Microsoft SQL Server 2005 or later installed. Note also that some of the scripts require that you have enabled the Common Language Runtime (CLR) on you server; by default the execution of userwritten CLR code is disabled. Different file names denote different versions of the scripts for SQL 2005 and SQL 2008. The zip file also includes a BCP file from which the persons table is loaded. The script 01_build_database.sql creates a database called yourownindex, which is 1.7 GB, whereof 700 MB are the log files. Before you run the script, run a find-andreplace to match the path to which you extracted the files. The database collation is Latin1_General_CI_AS. The script loads one million rows into the persons table from the BCP file. Because including real email addresses in a download file could be sensitive, I have generated the addresses from a list of Slovenian words that I happened to have lying around. Slovenian is a language which is very rich in inflections, and this list has over a million entries. The user section of the addresses is completely random. There are some 20,000 different domains, with a skewed distribution to make some domains more common than others to mimic a real-world situation. The top domains are the normal ones, with the distribution taken from a real-world source. About 58 percent of the addresses end in .com or .net. The email addresses are lowercase only, and I have replaced all accented characters so that only international A through Z characters appear. There are no digits in the data. The data in the columns first_name, last_name, and birth_date are taken from the AdventureWorksDW database. The build script also creates a few more items that I will present as we encounter them. Expect the script to run for 2 3 minutes; it s a good idea to run the script at this point.

One should never go on a performance quest without a baseline. In our case this is the SELECT statement in the beginning of the article. This also gives me the opportunity to introduce the stored procedure tester_sp, created by 01_build_database.sql. Tester_sp expects a single parameter: the name of the procedure to be tested. Tester_sp assumes that the tested procedure takes a single parameter containing a search string, and that the procedure returns the columns person_id, first_name, last_name, birth date, and email for all persons whose email address contains the search string.

Tester_sp calls the tested procedure with four different search strings, and records the number of rows returned and the execution time in milliseconds. The procedure makes two calls for each search string, and before the first call for each string, tester_sp also executes the command DBCC DROPCLEANBUFFERS to flush the buffer cache. Thus, we measure the execution time both when reading from disk and when reading from memory. Of the four search strings, two are three-letter strings that appear in 10 and 25 email addresses respectively. One is a five-letter string that appears in 1978 email addresses, and the last string is a complete email address with a single occurrence. Here is how we test the plain_search procedure. (You can also find this script in the file 02_plain_search.sql.)

CREATE PROCEDURE plain_search @word varchar(50) AS SELECT person_id, first_name, last_name, birth_date, email FROM persons WHERE email LIKE '%' + @word + '%' go EXEC tester_sp 'plain_search' go

The output when I ran it on my machine was as follows:

6660 ms, 10 rows. Word = "joy". 6320 ms, 10 rows. Word = "joy". Data in cache. 7300 ms, 25 rows. Word = "aam". 6763 ms, 25 rows. Word = "aam". Data in cache. 17650 ms, 1978 rows. Word = "niska". 6453 ms, 1978 rows. Word = "niska". Data in cache. 6920 ms, 1 rows. Word = "omamo@petinosemdesetletnicah.com". 6423 ms, 1 rows. Word = "omamo@petinosemdesetletnicah.com". Data in cache.