To begin, I used four scripts to create the tables in this chapter (these tables appear again in some examples in later chapters). If you want to run the programs on your own test database, you should start by generating the sample data.

These two scripts create the tables and the primary key indexes:

These two scripts create additional indexes; the second script creates an index that replaces the index created by the first one (please refer to Chapter 1 for details):

The big transactions table is populated by GenerateData.java, which requires database.properties, which you must update to specify how to connect to your database.

Once the tables are created and populated, you can run the following programs:

All of them use the same database.properties file as the data generation program.

The following scripts are product-specific:

The values returned by snapmon.sql are cumulative. If you want values by time slice, you must compute each time you poll the difference with the previous results.

You can do this in several ways. You can dump the output of the query to files, merge and sort two successive files, or make creative use of awk or perl to get what you want. I advise you to use SQLite (http://www.sqlite.org), which has APIs for most languages and scripting languages, and proceed as follows:

At the time of this writing, something similar wasn’t available for MySQL, for which other monitoring means may be more adequate. Nevertheless, depending on the storage engine that is used, some useful data can be collected from information_schema.global_status (COM_xxx and INNODB_xxx parameters) as well as the falcon_xxx tables that will at least give you a global picture. This can be completed by select statements against information_schema.processlist, which must be sampled more frantically than the Oracle and SQL Server tables because it shows only current information.

The following scripts are used to create the tables in Chapter 2:

A few other .java programs come with the mandatory database.properties file. The following three programs test the performance loss of parsing:

These programs show step by step how a list of values can be passed as a single parameter:

These three programs fetch rows from the transactions table; the first one uses the default fetch size, the second one takes the fetch size as a command-line argument, and the third one activates the MySQL streaming mode:

And finally, this program tests the impact of the commit rate on throughput:

For Oracle, there are three different ways to write a function that counts patterns. There is also a script that creates a test table and successively applies the three functions to this table.

Also for Oracle, there are three functions to check whether a date corresponds to a weekend day, first as a nondeterministic function and then as a deterministic function, and finally as a function that wraps a deterministic function after removing the time part from the date:

A naive function is available for computing the next business day (NextBusinessDay.sql). This function relies on a table created by public_holidays.sql.

Also, with Oracle (in particular) and with MySQL, there are various attempts at refactoring this function. For Oracle, NextBusinessDay2.sql and NextBusinessDay3.sql both require Oracle11g or later, which isn’t the case for NextBusinessDay4.sql:

Similarly, there is a naive currency conversion function called fx_convert.sql and, for Oracle and MySQL, alternative versions (the Oracle fx_convert2.sql file requires Oracle11g or later):

Finally, one script replaces the function with a simple join:

You’ll find various views built on the tables created by the scripts and programs in Chapter 1:

You’ll also find a query that tries to identify complex views that might be a reason for poor query performance:

For Chapter 4, there is the definition of the view, which returns 10 rows numbered 0 through 9:

For SQL Server, there is the creation of the view that is required for further generation of random data, and the function that uses this view:

Two functions to return random numbers that are not uniformly distributed in an interval are in these scripts:

And if you want to see what the distribution looks like, a small script that draws a character-based histogram is available:

To generate a string of random characters (which can be useful for password generation), you have this script:

job_ref.sql shows how to generate job names that follow a predefined distribution.

To generate American names that are more realistic than those of all the people who email you to tout various drugs, anatomical enhancement devices, and fake watches, I suggest you go to http://www.census.gov/genealogy/www/ and click the link that will bring you to a list of frequently occurring surnames. You should find there a spreadsheet that contains the top 1,000 names in the United States. Download it and save it as a .csv file; my scripts assume that a semicolon is used for a separator.

Once you have the data, create a table to load it:

The MySQL script creates the table, loads and normalizes the data, and creates an index, and so does the SQL Server script (which uses an ancillary .xml file).

For Oracle, name_ref.sql just creates the table. You must load the data using name_ref.ctl, which is a control file you can use with SQL*Loader as follows:

sqlldr <username>/<password> control=name_ref.ctl

Then, still with Oracle, run name_ref2.sql to normalize the data and index the table.

get_ten_names.sql generates 10 random American surnames.

For Oracle, there are three additional scripts: dept_ref.sql allows you to randomly pick a department number for the emp table; gen_emp.sql is a pure SQL generator of rows for the emp table that fails to get truly random data; and gen_emp_pl.sql is a PL/SQL version that works fine.

For the generation of random text, please see Appendix B.

check (MySQL and Oracle) is a bash script that runs the output of an arbitrary query through the md5sum program.

qrysum.sql is a function (Oracle) or procedure (MySQL and SQL Server) that checksums the output of an arbitrary query. The script qrysum_complicated.sql does the same for Oracle, but applies successful checksums to buffers. It is mostly interesting as a wild PL/SQL example of dynamic SQL.

There is no script sample for this chapter. Examples in this chapter come from real cases.

Most of the examples in this chapter also come from real cases, but I wrote some of them for this book.

The SQL Server directory contains the files for the example of unlocking accounts after payment. Two functions were written in Visual Basic—one that strictly follows specifications and another that performs the same task with a more clever use of SQL:

There is also a rather complete PHP example written for MySQL, and a configuration file that must be updated with connection data. The first two examples show an incorrect and a correct use of found_rows() (very specific to MySQL):

Another series of PHP programs shows how you can avoid counting before fetching data when there is an upper limit on the number of rows that will be displayed:

The most interesting query in the last program is a query that returns the total number of rows in the result set in the first row. It is provided (as a query on the transactions table that returns more than 500 rows) for all DBMS products (including MySQL).

For Oracle users, a very simple example that uses emp and dept shows why I dislike cursor loops:

For Chapter 7, there are code samples for MySQL only (however, SQL Server and Oracle users mustn’t forget the Roughbench tool, which is described in Appendix B).

The table that is used in the examples is created by fifo.sql.

The various programs that simulate the processing of messages need the following sources and the accompanying makefile:

For the test of concurrent inserts, there are two subdirectories, one for each storage engine used. Scripts postfixed with _ai use an auto-increment key, and scripts postfixed with _p use a partitioned table. There are scripts to create the tables, and scripts to insert data, which you must run with Roughbench.