code 128 checksum c# These are the execution times we should try to beat. in C#

Encoder Code 128 Code Set C in C# These are the execution times we should try to beat.

Consider this procedure:

CREATE PROCEDURE substring_search @word varchar(50) AS SELECT person_id, first_name, last_name, birth_date, email FROM persons WHERE substring(email, 2, len(email)) = @word

This procedure does not meet the user requirements for our search. Nevertheless, the performance data shows something interesting:

Disk Cache joy 5006 296 aam 4726 296 niska 4896 296 omamo@ 4673 296

The execution times for this procedure are better than those for plain_search, and when the data is in cache, the difference is dramatic. Yet, this procedure, too, must scan, either the table or the index on the email column. So why is it so much faster The answer is that the LIKE operator is expensive. In the case of the substring function, SQL Server can examine whether the second character in the column matches the first letter of the search string, and move on if it doesn t. But for LIKE, SQL Server

must examine every character at least once. On top of that, the collation in the test database is a Windows collation, so SQL Server applies the complex rules of Unicode. (The fact that the data type of the column is varchar does not matter.) This has an important ramification when designing our search routines: we should try to minimize the use of the LIKE operator.

One of the alternatives for improving the performance of the LIKE operator is to force a binary collation as follows:

COLLATE Latin1_General_BIN2 LIKE '%' + @word + '%'

With a binary collation, the complex Unicode rules are replaced by a simple byte comparison. In the file 02_plain_search.sql, there is the procedure plain_search_binary. When I ran this procedure through tester_sp, I got these results:

Disk Cache joy 4530 656 aam 4633 636 niska 4590 733 omamo@ 4693 656

Obviously, it s not always feasible to use a binary collation, because many users expect searches to be case insensitive. However, I think it s workable for email addresses. They are largely restricted to ASCII characters, and you can convert them to lowercase when you store them. The solutions I present in this chapter aim at even better performance, but there are situations in which using a binary collation can be good enough.

In English-speaking countries, particularly in the US, it s common to use a SQL collation. For varchar data, the rules of a SQL collation encompass only 255 characters. Using a binary collation gives only a marginal gain over a regular case-insensitive SQL collation.

We will now look at the first solution in which we build our own index to get good performance with searches using LIKE, even on tens of millions of rows. To achieve this, we first need to introduce a restriction for the user. We require his search string to contain at least three contiguous characters. Next we extract all threeletter sequences from the email addresses and store these fragments in a table together with the person_id they belong to. When the user enters a search string, we split up the search string into three-letter fragments as well, and look up which persons they map to. This way, we should be able to find the matching email addresses quickly. This is the strategy in a nutshell. We will now go on to implement it.

The first thing we need is to create the table itself:

CREATE TABLE fragments_persons ( fragment char(3) NOT NULL, person_id int NOT NULL,

CONSTRAINT pk_fragments_persons PRIMARY KEY (fragment, person_id) )

You find the script for this table in the file 03_fragments_persons.sql. This script also creates a second table that I will return to later. Ignore it for now. Next, we need a way to get all three-letter fragments from a string and return them in a table. To this end, we employ a table of numbers. A table of numbers is a onecolumn table with all numbers from 1 to some limit. A table of numbers is good to have lying around as you can solve more than one database problem with such a table. The script to build the database for this chapter, 01_build_database.sql, created the table numbers with numbers up to one million. When we have this table, writing the function is easy:

CREATE FUNCTION wordfragments(@word varchar(50)) RETURNS TABLE AS RETURN (SELECT DISTINCT frag = substring(@word, n, 3) FROM numbers WHERE n BETWEEN 1 AND len(@word) - 2 )

Note the use of DISTINCT. If the same sequence appears multiple times in the same email address, we should store the mapping only once. You find the wordfragments function in the file 03_fragments_persons.sql. Next, we need to load the table. The CROSS APPLY operator that was introduced in SQL 2005 makes it possible to pass a column from a table as a parameter to a tablevalued function. This permits us to load the entire table using a single SQL statement:

INSERT fragments_persons(fragment, person_id) SELECT w.frag, p.person_id FROM persons p CROSS APPLY wordfragments(p.email) AS w

This may not be optimal, though, as loading all rows in one go could cause the transaction log to grow excessively. The script 03_fragments_persons.sql includes the stored procedure load_fragments_persons, which runs a loop to load the fragments for 20,000 persons at a time. The demo database for this chapter is set to simple recovery, so no further precautions are needed. For a production database in full recovery, you would also have to arrange for log backups being taken while the procedure is running to avoid the log growth. If you have created the database, you may want to run the procedure now. On my computer the procedure completes in 7 10 minutes.