SQL Server Magazine

First, create a fake temporary table that includes the fields you require from the Inserted and Deleted tables, including the common primary key fields and Inst_ID. The fake temporary table must include previous and new salary information and employee name:

CREATE TABLE fttEmployeesSalary (
	Inst_ID INT NOT NULL,
	Emp_ID INT NOT NULL,
	Employee_Name VARCHAR(50) NULL,
	PreviousSalary MONEY NULL,
	NewSalary MONEY NULL,
	
CONSTRAINT PK_ftt_EmployeesSalary
	PRIMARY KEY NONCLUSTERED (Inst_ID, Emp_ID))
GO
/* Optional nonclustered index to speed searching process on Emp_ID */

CREATE NONCLUSTERED INDEX idxEmployeesSalary_Emp_ID
	ON fttEmployeesSalary (Emp_ID)

Next, create a stored procedure called GetInstID_EmployeesSalary, which generates a unique Inst_ID value as Listing 1's and Listing 2's different versions of GetInstID_SelectedProducts do. You can now create your trigger, udtEmployeesSalary, which Listing 3 shows. When anyone modifies salary data in the Employees table, the trigger sends an email notification to the employees' appropriate managers.

Let's look more closely at how the udtEmployeesSalary trigger works. First, the trigger executes the GetInstID_EmployeesSalary stored procedure to retrieve the process's instance identifier from the @Inst_ID variable. Using this instance identifier, the trigger inserts into the fttEmployeesSalary fake temporary table the Emp_ID and Employee_Name of the employee whose information has been modified, as well as the employee's old and new salaries. The trigger then creates a message containing as parameters the name of the employee who modified the data and the date and time of that modification. Next, the trigger creates a query that it will send through email. The query, which contains information about the changes to the Employees table, uses the fake temporary table to filter the information by instance ID (Inst_ID = @Inst_ID) and to avoid the dummy entry (Emp_ID > 0). The trigger then uses xp_sendmail to send the query result as an email message and deletes from the fake temporary table any rows marked with the current instance ID.

You could create a narrower scope for your fake temporary table, creating it to store only modified data and leaving the employee name in the Employees table. But to store only modified data, you'd have to join fttEmployeesSalary to the Employees table in every query so that you could retrieve additional employee information. My example, which uses only the fake temporary table, consumes more storage space, but it's more efficient than storing only salary changes because it uses fewer pages to produce the final result.

Batch Execution of Stored Procedures
You can use stored procedures to optimize your system, and you can parameterize stored procedures to accept different values, as long as you use consistent data types. Many times, you might find sending a table as a parameter to a stored procedure useful. However, T-SQL doesn't provide an array, object, or data set data type. You can pass a table name to a stored procedure only as a string—you can't pass a table as a data set. Even when you pass a table name, you must rely on SQL Server's dynamic execution to use the name—an inefficient operation that requires SQL Server to optimize the query on every execution and that bypasses the benefits of stored procedure reuse. So, how can you send a data set to a stored procedure for further processing?

Suppose you have a stored procedure called CheckInvoice, which converts proposed invoices into permanent ones. The procedure performs three actions:

1. It updates the Invoices table to set the Checked field to 1 for checked invoices.
2. It updates two denormalized fields in the Clients table: TotalPreInvoiced (the total value of proposed invoices) and TotalInvoiced (the total amount invoiced).
3. It notifies the finance department of which invoices to print and send to clients by using xp_sendmail to email the appropriate Invoice_No values.

To perform these functions, you would typically call the CheckInvoice procedure once per invoice by sending the procedure the Inv_ID value, which identifies the invoice. But with fake temporary tables, you can make this checking process more efficient. Simply modify the CheckInvoice procedure to use a fake temporary table that lets you check all the invoices at once. The fake temporary table uses the instance identifier to specify which invoices the operator has marked as permanent. Let's call the modified stored procedure CheckInvoices to differentiate it from the original CheckInvoice procedure.

For this example, you create a Visual Basic (VB) application that shows proposed invoice details and lets the operator mark them as permanent by using a check box called chkCheckInvoice. When the operator has finished checking the invoices and wants to send the changes to the server, the operator clicks Commit Command (cmdCommit) to send changes to the server.

The application's back end uses a fake temporary table to hold the checked invoices. Whenever the operator selects an invoice as permanent, the application inserts a new record into the fake temporary table. And whenever the operator clears a previously selected invoice, the application deletes the corresponding record from the table. After the operator makes the changes permanent, the application calls the CheckInvoices procedure to execute the required actions. To optimize this process, you can create stored procedures to insert entries into and delete them from the fake temporary table. (Note that you can't use a similar procedure to convert permanent invoices back to proposed invoices because that process has legal and financial ramifications.)

Listing 4, page 51, shows the T-SQL code for creating the fake temporary table. (The online version of Listing 4 also shows the T-SQL code for the stored procedures that generate a unique Inst_ID and that insert and delete invoices from the table. See the More on the Web box for download instructions.) Web Listing 1, available online, shows the simplified VB code for the application. Here are the steps you'd take to create such an application:

1. Create the fake temporary table fttCheckedInvoices, which contains only two fields: Inst_ID and Inv_ID.
2. Create the GetInstID_CheckedInvoices stored procedure, which searches for an unused Inst_ID instance identifier value on fttCheckedInvoices whenever the operator starts a new session.
3. Create stored procedure AddCheckedInvoice, which inserts new entries into fttCheckedInvoices whenever the operator selects an invoice.
4. Create stored procedure DelCheckedInvoice, which removes entries from fttCheckedInvoices whenever the operator clears an invoice.
5. Create the CheckInvoices stored procedure, which accepts only Inst_ID as a parameter, to send the data set of permanent invoices to the server when the operator clicks Commit.
6. Remove entries from fttCheckedInvoices after the procedure executes successfully.

With this fake temporary table strategy, you avoid concurrency conflicts, because every connection uses its own set of data. If you think that page locks might be a problem in SQL Server 6.5, you could create the fake temporary table's primary key as CLUSTERED so that you spread entries from different connections onto different pages (remember that Inst_ID is the first field in the primary key).

Optimizing Fake Temporary Tables
Fake temporary tables are simple and leave you little functionality to optimize. You need to define a primary key to ensure uniqueness, and you need to define a nonclustered index on the original primary key—in case you have to link the fake temporary table to other tables to retrieve extra information. Deadlock situations with fake temporary tables are unlikely. Every connection uses its own set of rows, which the Inst_ID column identifies, and you have an index starting with Inst_ID, so only one connection uses the unique path seek of each Inst_ID value.

Creating tables, temporary or not, involves some overhead. SQL Server inserts information into system tables to define the new table and creates allocation structures to support storage in the table. However, a fake temporary table is a permanent table that already exists: For example, the system catalog already contains its definition, and SQL Server has already created the necessary allocation pages to control data storage. If a stored procedure uses a temporary table, SQL Server must create and destroy the temporary table on every execution of the stored procedure. So, using fake temporary tables in stored procedures also improves the procedures' execution speed. And applications use the values entered in a fake temporary table immediately after their insertion, so SQL Server keeps the values in cache and removes them when the connection ends, keeping physical disk reads to a minimum.

As you saw earlier, using NEWID() to generate the Inst_ID value as a uniqueidentifier data type would simplify your code, but at a cost: Uniqueidentifiers use 16 bytes. Using the NEWID() function will provide completely different 16-byte numbers, as Listing 5, page 51, shows. Extracting 4 bytes only, to get performance similar to that of random integer numbers, is difficult because the NEWID() function guarantees the uniqueness of 16 bytes but not of any part of the number in isolation. The results of Listing 6 and Listing 7 show that using the RAND() function to generate and insert random integer values takes about 15 percent longer than using NEWID() to insert uniqueidentifier values, but searching for integer values is twice as fast as searching for uniqueidentifier values.

Listing 8 shows a comparison of values that RAND() manually generates versus values that an algorithm based on NEWID() generates. As you can see, the NEWID() algorithm was 40 percent faster than the manual RAND() at producing instance identifier values. But the real values depend on the actual fake temporary table configuration. The best advice is to test your real-world situation.

Fake temporary tables can open up a whole new world of possibilities for designing your database for more efficient processing and for more functionality. Your specific database needs and your developer imagination will surely suggest other uses for this technique.

End of Article

Prev. page     1 2 [3]     next page -->