Improving Database Application Performance in Visual Studio .NET

Paint GTIN - 12 in Visual Studio .NET Improving Database Application Performance

8

The new version of the stored procedure would check to see whether the end date passed in as a parameter exceeded three years. If it did, then it would use a UNION clause to return records from both the original database and the newly created linked server. The OPENDATASOURCE function is used to execute an ad hoc query against the server named svrProductHistory. The OPENDATASOURCE function provides an alternative to using a four-part name to query the linked server. For more information about using a four-part name to query a linked server, refer to Lesson 1, Writing Database Queries, in 2. By default, SQL Server 2005 does not allow you to execute ad hoc queries. Therefore, you must enable this option using the sp_configure system stored procedure. Because it is an advanced option, you will also need to first show the advanced options. You could use the following Transact-SQL code to enable ad hoc queries:

sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO

The last thing to consider is that as time passes, more history records will be added the database. You will need to create a process that moves records older than three years to the second database. Although you could do this using a custom application, it would be easier to simply create a SQL Server Integration Services (SSIS) package that can be scheduled to run once a day. The package would execute code that queried the database for records older than three years. If any are found, it can delete the records from the original database and insert them into the second database.

Implementing a successful scale-out technique involves consideration of all potential solutions. It is possible that you might need to implement a combination of one or more techniques to accomplish your goal. This section presents techniques that can be used to target multiple servers and thus scale out your application.

A Distributed Partition View (DPV) is a technique in which data partitioned horizontally across multiple servers is joined together to make it appear as if it comes from one table. The data is partitioned by a key value from each table. For example, the Product ID column could be used to partition the product tables in the AdventureWorks database. The goal of a DPV is to transparently distribute the load of a database application across multiple servers. This is different from the partition we created earlier, in which a table was assigned to multiple filegroups on the same server. In the case of a DPV, the data is partitioned across two or more servers. To create a DPV, you will first need to horizontally partition your table or tables. The considerations that apply to this task are the same as those discussed in the section of this chapter titled Specifying a Data-Partitioning Model. Once the data has been moved to its respective servers, you will need to utilize check constraints to ensure the range of values for each partition. For example, assume we want to create three partitions for the Person.Contact table in the AdventureWorks database. The partitions will reside on three separate servers named ServerA, ServerB, and ServerC. The Person.Contact table contains 19,972 records. If this table is partitioned on the column ContactID, we can split it so that 6,657 records reside in the partition on ServerA, 6,657 records in the partition on ServerB, and 6,658 records in the partition on ServerC (6,657 + 6,657 + 6,658 = 19,972). The partitioned tables will need to include a check constraint that enforces the rule that each server will contain a certain range of ContactID records. You can add check constraints with the ALTER TABLE statement or by using the Check Constraints dialog box in SQL Server Management Studio (refer to Figure 8-2). The Transact-SQL statement used to create a check constraint on ServerA would appear as follows:

ALTER TABLE Person.Contact ADD CONSTRAINT CK_Contact_ContactID CHECK (ContactID BETWEEN 1 AND 6657) GO