Chapter 21 - Migrating Btrieve Applications to MS SQL Server 7.0

Article
01/28/2010

Many database application vendors are finding that their products are at a critical point in the product life cycle. They have released with a solid level of success several versions that use indexed sequential access method (ISAM)–based database management systems (DBMS) like Btrieve.

However, due to rapidly expanding customer needs and the powerful capabilities of relational database management systems (RDBMS) offerings, many Independent Software Vendors (ISVs) are now converting their applications to RDBMSs. Applications based on an RDBMS can offer better performance and scalability, and more replication features than ISAM-based solutions. Well-designed RDBMS solutions vary in size from a few megabytes to multiple terabytes.

Microsoft SQL Server 7.0 is a powerful and flexible RDBMS solution. The ease of use and scalability of SQL Server 7.0 has made it attractive for thousands of application developers. This chapter presents a strategy that will allow you to convert Btrieve applications to work with SQL Server. The strategy presents many of the features of SQL Server that make it a high-performance alternative to an ISAM platform. While the Btrieve platform is this chapter's focus, the chapter introduces many concepts that can be applied to any ISAM-based application.

Microsoft offers many programs and resources for developers. The benefits to developers include training, comarketing opportunities, and early bites of Microsoft products. Benefits vary based on the programs.

The Btrieve environment may seem simpler to use and administer than SQL Server. However, this simplicity comes at a price. Btrieve has several architectural shortcomings that add complexity to applications that access it. Btrieve does not:

Enforce logging of all data changes.
Provide a way to back up the data files while they are online and in use.
Provide mechanisms to automate administrative tasks such as data file backups.
Offer tools for monitoring performance.

The Microsoft strategy is to make SQL Server the easiest database for building, managing, and deploying business applications. This means providing a fast and efficient programming model for developers, eliminating database administration for standard operations, and providing sophisticated tools for more complex operations.

SQL Server 7.0 feature	Description
Administration Wizards	Many new wizards simplify advanced tasks such as creating databases, scheduling backups, importing and exporting data, and configuring replication.
DBA Profiling and Tuning Tools	New tools provide advanced profiling and tuning: · SQL Server Profiler improves debugging by allowing the capture and replay of server activity. · Index Tuning Wizard provides guidance through the index tuning process. · SQL Server Query Analyzer allows easy, in-depth query analysis.
Distributed Management Objects	Independent software vendors and corporate developers can easily develop custom management applications. The COM-based framework exposes all management interfaces for SQL Server. Automation components and custom applications can be written using the Microsoft Visual Studio development system, the Microsoft Visual Basic development system for Applications, and Java scripting.
Dynamic Self-Management	Reduces need for DBA intervention: memory and lock resources are adjusted dynamically; file sizes grow automatically; autotuning features guarantee consistent performance under variable-load conditions.
Event/Alert Management	Enhances ability to monitor performance, availability, and security status through policy-based event management. Improved alert management provides automatic notification and recovery in response to thresholds and severity levels.
Job Scheduling and Execution	The job scheduling and execution environment is extended to allow stand-alone, multiserver, single-step, multistep jobs and job step with dependencies. Great flexibility is provided though a variety of scripting environments: Microsoft Visual Basic Scripting Edition, Java scripting, Microsoft Windows NT commands and custom ODBC and OLE DB programs.
Multisite Management	Improves power and flexibility for managing multiple servers. Drag-and-drop and single commands can be used to implement changes across groups of servers. Management is simplified through the use of a repository that maintains schema, profiles, and data transformation metadata for all servers in the enterprise.
Security	Security administration is improved and simplified through better integration with Windows NT security and SQL Server roles. Windows NT integration includes authentication, support for multiple groups, grant/revoke/deny permission management activities and dynamic use of groups.
Standards Compliance	Full compliance with the ANSI/ISO SQL-92 Entry-level standards. Views are included for the ANSI/ISO schema information tables as defined in SQL-92, providing a standard method for metadata examination.
Version Upgrade	Databases are easily transferred from version 6.x to 7.0, via a fully automated upgrade utility. Customers are able to get up and running quickly on the new version and take advantage of new features with minimal impact on operations.
Visual Data Modeler	New tools provide a graphical interface for building and managing schema and other database objects.

Introducing Microsoft SQL Server Version 7.0

Microsoft SQL Server version 7.0 is a defining release that builds on the solid foundation established by SQL Server version 6.5. As the most robust database for the family of Windows operating systems, SQL Server is the RDBMS of choice for a broad spectrum of corporate customers and ISVs who are building business applications. Customer needs and requirements have driven significant product innovations in ease of use, reliability, scalability, and data warehousing.

SQL Server Design Goals

SQL Server 7.0 was designed with several goals in mind:

Leadership and innovation in the database industry
Ease of use for customers
Scalability and reliability
Data warehousing

Leadership and Innovation

Innovations enable SQL Server 7.0 to lead several of the database industry's fastest growing application categories, including data warehousing, line-of-business applications, mobile computing, branch automation, and e-commerce.

Innovations with which SQL Server 7.0 leads the database industry include:

Scalability from the laptop to branch offices using the same code base and offering 100 percent code compatibility.
Support for autoconfiguration and self-tuning.
Integrated online analytical processing (OLAP) server.
Integrated Data Transformation Services (DTS).
The Microsoft Data Warehousing Framework, the first comprehensive approach to solving the metadata problem.
Multiserver management for large numbers of servers.
A wide array of replication options, including replication to any database that supports OLE DB or ODBC.
Integration with the Microsoft Windows NT Server operating system, Microsoft Office, and the Microsoft BackOffice family of products.
Universal Data Access, the Microsoft strategy for enabling high-performance access to a variety of information sources.

Universal Data Access is a platform for developing multitier, enterprise applications that require access to diverse relational and nonrelational data. This collection of software components interacts using a common set of system-level interfaces called OLE DB, the Microsoft open specification for low-level interfacing with data. OLE DB is the next generation successor to the industry standard ODBC data access method.

OLE DB replaces DB-Library as the internal interface for all SQL Server database operations. OLE DB enables heterogeneous queries that use the SQL Server query processor to perform with the same high level of performance as queries to the SQL Server storage engine.

Ease of Use

Customers are looking for solutions to business problems. Most database solutions bring multiple layers of cost and complexity. The Microsoft strategy is to make SQL Server the easiest database for building, managing, and deploying business applications. This means providing a fast and simple programming model for developers, eliminating database administration for standard operations, and providing sophisticated tools for more complex operations.

SQL Server 7.0 lowers the total cost of ownership through features like multiserver, single-console management; event-based job execution and alerting; integrated security; and administrative scripting. This release also frees the database administrator for more sophisticated aspects of the job by automating routine tasks. Combining these powerful management facilities with new autoconfiguration features, SQL Server 7.0 is the ideal choice for branch automation and embedded database applications.

Scalability and Reliability

Customers make investments in database management systems in the form of the applications written to that database and the education that it takes for deployment and management. That investment must be protected: as the business grows, the database must grow to handle more data, transactions, and users. Customers also want to protect investments as they scale database applications down to laptops and up to branch offices.

To meet these needs, Microsoft delivers a single database engine that scales from a mobile laptop computer running the Microsoft Windows 95 and Windows 98 operating systems, to terabyte symmetric multiprocessor clusters running Windows NT Server, Enterprise Edition. All of these systems maintain the security and reliability demanded by mission-critical business systems.

New in SQL Server 7.0 is a low-memory footprint version with multisite replication capabilities. It is well suited to the growing needs of the mobile computing marketplace.

Other features such as dynamic row-level locking, intra-query parallelism, distributed query, and very large database (VLDB) enhancements make SQL Server 7.0 the ideal choice for high-end online transaction processing (OLTP) and data warehousing systems.

Data Warehousing

Transaction processing systems remain a key component of corporate database infrastructures. Companies are also investing heavily in improving understanding of their data. Microsoft's strategy is to reduce the cost and complexity of data warehousing while making the technology accessible to a wider audience.

Microsoft has established a comprehensive approach to the entire process of data warehousing. The goal is to make it easier to build and design cost-effective data warehousing solutions through a combination of technologies, services, and vendor alliances.

The Microsoft Alliance for Data Warehousing is a coalition that brings together the industry's leaders in data warehousing and applications. The Microsoft Data Warehousing Framework is a set of programming interfaces designed to simplify the integration and management of data warehousing solutions.

New product innovations in SQL Server 7.0 improve the data warehousing process with:

Microsoft SQL Server OLAP Services, an essential component for enterprise solutions that require OLAP, from corporate reporting and analysis to data modeling and decision support.
DTS, for importing, exporting, and transforming data.
Handling of complex queries and very large databases.
Microsoft Repository, a common infrastructure for sharing information.
Visual Design Tools, for creating and maintaining database diagrams.
Integrated replication, including multisite update, for maintaining dependent data marts.
Integration with third-party solutions.

Getting Started with the Migration

For clarity and ease of presentation, the reference development and application platforms are assumed to be the Microsoft Visual C++ development system and Microsoft Windows NT 4.0 or later, or Microsoft Windows 95 or Windows 98 operating systems. The Btrieve function and dynamic-link library (DLL) references throughout this chapter reflect this assumption. However, these techniques can also be applied to other compilers that create Microsoft Windows-based applications.

Examples from the pubs Database

Several tables from the Microsoft SQL Server pubs sample database are used in both the sample applications and sample code referenced in this chapter. These tables were chosen because they are readily available to any SQL Server user and because their structure and entity relationships are presented in the SQL Server documentation.

Sample Application and Code References

The following sample applications and wrapper DLLs are referenced in this chapter and you can find the source files on the CD-ROM that accompanies this volume.

Resource	Description
Btrvapp.exe	Sample Btrieve application that references the Sales.btr and Titlepub.btr Btrieve data files. It is the starting point for the conversion strategy, and it is a simple data-entry and reporting application for a database that maintains information about publishing companies, the titles they manage, and current sales information for each title.
Mybtrv32.dll	Sample wrapper DLL that translates the Btrieve calls in Btrvapp.exe to ODBC and Transact-SQL calls. The wrapper provides a bare minimum conversion from a Btrieve ISAM implementation to a SQL Server client/server implementation. Using this wrapper DLL, Btrvapp.exe accesses nonnormalized tables from the SQL Server pubs database instead of the Btrieve data files.
Odbcapp.exe	Sample ODBC and SQL Server application that performs the same function as the Btrvapp.exe and Mybtrv32.dll combination by using ODBC and Transact-SQL only, and no Btrieve calls or references.
Morepubs.sql	Script file that creates the nonnormalized tables in the pubs database referenced by the wrapper DLL, as well as the stored procedures used by Odbcapp.exe for illustrative purposes.

Conversion Strategy

Converting an application from one database environment to another takes time, patience, and resources. Because of the differences between platforms, a multiple-step conversion process is recommended. This approach provides multiple analysis points and makes the overall project development process more flexible.

This strategy provides a framework you can use when converting an application from a Btrieve-based implementation to a full ODBC and structured query language (SQL) implementation that accesses Microsoft SQL Server. Converting the application in several steps creates separate checkpoints that allow you to evaluate the status and direction of the conversion process at each stage of the project life cycle. The conversion strategy addresses the following areas:

Creating a wrapper DLL.
Converting the application data using DTS.
Converting the application to ODBC and SQL.
Using server resources effectively.
Ease-of-use features for administering a SQL Server 7.0 database.

The following illustration presents the application architecture implemented at each stage of the conversion process. The components of this illustration are analyzed in detail throughout the next two sections of this chapter.

Cc966479.sqc22001(en-us,TechNet.10).gif

Starting Point: Btrieve Application

Btrvapp.exe is a simple data-entry and reporting application that maintains information about book titles, the publishers that own the titles, and sales information for each title. The Btrieve application accesses two Btrieve files, Sales.btr and Titlepub.btr, through the Btrieve microkernel engine. The Sales file contains sales information for each title, and the Titlepub file maintains the title and publisher information for each title. The Sales file and the Titlepub file each have two keys that correspond to a publisher and a title ID.

The Btrieve application uses these keys to position itself within these files when it performs all searches. The Btrieve application uses ISAM row-at-a-time searching techniques and result processing to perform its operations, and Btrieve concurrent transactions to manage the locks in the data file while information is updated, inserted, or deleted. The Btrieve application provides the following functionality:

Searches for a particular title by its title ID key. The output of the search contains details about the title, its publisher, its recent sales, and a year-to-date sales total.
Adds a title and its corresponding publisher information.
Adds sales items for a title.
Updates the year-to-date sales for all titles owned by a particular publisher. The output of the search contains details about each title affected and its new year-to-date sales total.

Stage 1: Wrapper DLL

The goal of this stage in the conversion process is to provide a layer of abstraction between the base application and Microsoft SQL Server. Using the concept of a wrapper DLL, the base application, Btrvapp.exe, can access SQL Server data without modification. Essentially, the wrapper disguises the SQL Server data and responses as Btrieve data and responses to Btrvapp.exe. The wrapper uses Btrieve-like result set processing techniques to access two nonnormalized tables, bsales and titlepublisher. These tables are structured to maintain the same details as the Sales and Titlepub files accessed by Btrvapp.exe. Although the ODBC and SQL Server implementation techniques presented in the wrapper DLL are not optimal, they present an initial access methodology that is similar to Btrieve.

Stage 2: ODBC and SQL Server Application

Odbcapp.exe is a full ODBC and SQL Server application that accesses SQL Server data directly and more efficiently than the techniques implemented by the wrapper DLL. The ODBC application accesses data in the three normalized tables (titles, publishers, and sales), taking advantage of the relational model provided by SQL Server. Odbcapp.exe also uses several of the performance-enhancing features of SQL Server such as indexes, default result sets and stored procedures to process result sets.

Migrating Btrieve Data to Microsoft SQL Server

The Data Transformation Services wizard (DTS Wizard) can help you move your data from Btrieve to SQL Server. First, you create a data source name (DSN) by using an ODBC driver or an OLE DB provider. Then the wizard leads you through the steps required for data migration.

Note Before you can begin migrating Btrieve data, you must have a Pervasive Btrieve ODBC driver. You can use the ODBC driver included with the Pervasive Btrieve product or a third-party driver.

First, you must create a Pervasive ODBC data source:

On the Start menu, click Control Panel, and then click ODBC.
In the System DSN dialog box, click Add, and then configure the Btrieve data source. Make sure that the data source points to your database.
In the ODBC Data Source Administrator dialog box, verify that the driver is listed on the File DSN tab.
On the Start menu, point to SQL Server 7.0, and then click Import and Export Data to launch the Data Transformation Services Wizard.
Select the Btrieve DSN when you are asked for a Source database.
Select the name of the SQL Server database when you are asked for a destination database.
In the Specify Table Copy or Query dialog box, select Copy Table(s) from the source database.
Select the tables to move.
Select the data formats for the new Microsoft SQL Server database.
Optionally, modify the CREATE TABLE statement that was automatically generated.
Optionally, transform data as it is copied to its destination.

You can use this functionality to help you check for potential year 2000 problems, and to change data to reflect standard coding such as country codes, state names, or phone number formatting.
Select one or more source tables to copy to the destination database.

Do one of the following:
- To run the data export/import now, click Run Immediately.
- To save the DTS package later, click Save Package on SQL Server.
Optionally, schedule this export/import task to run on a recurring basis.
If you saved the DTS package, you can reexecute the routine from the Microsoft Management Console (MMC) at any time. The routine is located in the Data Transformation Services directory under Local Packages.

Using the Wrapper DLL

A wrapper DLL is a dynamic-link library that intercepts external library function calls made to an application. After a function call has been intercepted, the DLL controls the application or process that instantiated the DLL. The DLL can be designed to perform any task or set of tasks, or to do nothing at all. The developer can add to, change, or remove functionality or scope from the DLL without modifying the source code of the calling process or application.

For example, in the case of this conversion strategy, a wrapper DLL can intercept Btrieve calls made to an application and change them to use ODBC to access Microsoft SQL Server. This technique leaves the base application code intact while changing the scope and/or targets of the operation. The initial investment made in the application is preserved even though the application's capabilities have been extended to access SQL Server data.

Alternatively, the wrapper DLL could retrieve data from SQL Server into buffers maintained on the client or another computer. The application then fetches data from the buffers instead of from SQL Server directly by using ISAM-like processing techniques. Although this implementation enables the unmodified base application to access SQL Server, it is complex and can be difficult to implement. It is best suited for those instances when you do not want to use set operations or to develop a full ODBC- and SQL-based application. This methodology is not presented in this chapter.

Creating the Wrapper DLL

Four steps are involved in creating the wrapper DLL:

Determine which functions to wrap.
Map Btrieve import functions to export functions in the DLL.
Implement the Btrieve functions.
Link the application to the wrapper DLL.

Determining Which Functions to Wrap

The wrapper DLL must cover all the functions that the base application imports from the Btrieve library Wbtrv32.dll. Use a binary file dumping utility to list the functions imported from the various external link libraries and referenced by the application. In Microsoft Visual C++, the equivalent of the dumping utility is called Dumpbin.exe.

Use DUMPBIN /IMPORTS application_file_name to obtain the list of imported symbols for Wbtrv32.dll. In the following sample output, the function symbols in Btrvapp.exe imported from Wbtrv32.dll are ordinals 3, 2, and 1:

DUMPBIN /IMPORTS BTRVAPP.EXE
Microsoft (R) COFF Binary File Dumper Version 4.20.6164
Copyright (C) Microsoft Corp 1992-1997. All rights reserved.
Dump of file BTRVAPP.EXE
File Type: EXECUTABLE IMAGE
Section contains the following Imports:
wbtrv32.dll
Ordinal 3
Ordinal 2
Ordinal 1

Use DUMPBIN /EXPORTS DLL_file_name to obtain the list of exported symbols for the DLL in question. The symbols appear in the name column of the table whose headings are "ordinal," "hint," and "name." In the example, these correspond to BTRCALL, BTRCALLID, and WBRQSHELLINIT.

DUMPBIN /EXPORTS WBTRV32.DLL
Microsoft (R) COFF Binary File Dumper Version 4.20.6164
Copyright (C) Microsoft Corp 1992-1997. All rights reserved.
Dump of file wbtrv32.dll
File Type: DLL
Section contains the following Exports for wbtrv32.dll
0 characteristics
31D30571 time date stamp Thu Jun 27 15:04:33 1996
0.00 version
1 ordinal base
10 number of functions
10 number of names
ordinal hint name
1 0 BTRCALL (000014EC)
8 1 BTRCALLBACK (00003799)fs
2 2 BTRCALLID (00001561)
9 3 DBUGetInfo (00008600)
10 4 DBUSetInfo (000089E8)
3 5 WBRQSHELLINIT (00002090)
4 6 WBSHELLINIT (00002A6A)
7 7 WBTRVIDSTOP (00001812)
5 8 WBTRVINIT (00002A4F)
6 9 WBTRVSTOP (000017D2)

The information presented in these output excerpts is used to create the definition file for the wrapper DLL. You need to implement only the exported functions from Wbtrv32.dll that are used by the base application in the wrapper DLL. This eliminates the need to implement exported functions that are never used by the base application.

Mapping Functions in a DEF File

After you have identified the Btrieve import functions and symbols in the base application as well as the exported symbols for the DLL, map these import functions to export functions in the wrapper DLL by using a definition file for the wrapper DLL.

Create a DEF file that contains an EXPORTS section with the names of the functions listed in the name column of the DUMPBIN output. The exact import/export combination varies depending on what Btrieve functionality is used in the application.

Implementing the Btrieve Functions Within the Wrapper

The next step is to develop the basic framework within the wrapper so that all of the Btrieve operations are implemented properly. Most of the Btrieve operations are performed by using the BTRCALL and BTRCALLID functions. Their equivalent functions within the wrapper must be designed to address the operations used by the base applications. Each of these functions has all of the data necessary to perform the operations by using the input parameters it receives.

The following code fragment shows how the B_GET_EQUAL operation is handled by the BTRCALL function within Mybtrv32.dll:

DllExport int __stdcall BTRCALL (BTI_WORD operation, BTI_VOID_PTR posBlock, 
BTI_VOID_PTR dataBuffer, BTI_ULONG_PTR dataLen32,
BTI_VOID_PTR keyBuffer, BTI_BYTE keyLength, BTI_CHAR ckeynum)
{
SQLRETURN rc; // Btrieve operation return code
/*Perform tasks based on operation used in the calling application */
switch(operation){ 
case B_GET_EQUAL: 
// Get the first Title-Publisher record that matches the search
// criteria
if (!strcmp(posBlock, "titlepub.btr")){//Are we accessing title-publisher info
rc = GetTitlePublisher(henv1, hdbc1, hstmt, B_GET_EQUAL, ckeynum,keyBuffer);
if (rc != B_NO_ERROR)
return rc;
//Copy title-publisher data to titlepub record structure tpRec
memcpy(dataBuffer, &tpRec, sizeof(tpRec));
}
else { // Accessing sales info
rc=GetSales(henv1, hdbc2, hstmt2, B_GET_EQUAL, keyBuffer);
if (rc != B_NO_ERROR)
return rc;
//Copy sales data to sales record structure salesRec
memcpy(dataBuffer, &salesRec, sizeof(salesRec));
} 
break;

The most important parameters are the posBlock, operation, dataBuffer, keyBuffer, and ckeynum parameters. The posBlock parameter is described in "Addressing the Btrieve posBlock Handle," later in this chapter. The operation parameter designates what operation is to be performed. The contents of the dataBuffer, keyBuffer, and ckeynum parameters depend on the operation being performed. You must use these parameters in the same way they would be used if the function was being processed by Btrieve.

The posBlock parameter in the preceding code fragment determines the target SQL Server table. After the target has been determined, a function is called to retrieve the first data record that matches the keyBuffer and ckeynum values from the appropriate SQL Server cursor.

The same methodology is used throughout the wrapper DLL. This illustration shows the wrapper DLL concept.

Cc966479.sqc22014(en-us,TechNet.10).gif

The base application, Btrvapp.exe, requests the title and publisher information for TitleID "BU1032." While the wrapper DLL processes this request, the Btrieve application calls the Btrieve function BTRCALL to get the next record from the Titlepub.btr file. The wrapper DLL mimics BTRCALL but accesses SQL Server data instead. It examines the opcode parameter and then performs the appropriate ODBC and Transact-SQL operations to satisfy the request. In this example, the wrapper DLL retrieves the record for TitleID "BU1032" from the titlepub table in the database. The wrapper DLL returns the retrieved data to the base Btrieve application by using the record data buffer passed as part of the original BTRCALL function call.

Accessing the Wrapper DLL in the Base Application

After the wrapper DLL has been created, the original application must reference the wrapper DLL instead of the Btrieve DLL. Link the application with the wrapper DLL and ODBC library files (LIB) rather than with the Btrieve library file. You do not have to recompile the base code. The base application will access SQL Server and not the Btrieve microkernel.

Translating Btrieve Calls to ODBC and Transact-SQL

Now, the base application, Btrvapp.exe, can use the wrapper DLL to access SQL Server data. Essentially, the wrapper makes SQL Server look like Btrieve to Btrvapp.exe. The next step is to consider how ODBC and Transact-SQL will access SQL Server data within the scope of the wrapper DLL. The wrapper is designed to use ISAM processing techniques to access SQL Server. Although this implementation successfully accesses SQL Server data without making changes to the base application code, the data is not accessed optimally.

Addressing the Btrieve posBlock Handle

In the Btrieve environment, posBlock is a unique area of memory that is associated with each open file and that contains logical positional information to access records. The Btrieve libraries initialize and use this memory area to perform data functions. The Btrieve application inserts into every Btrieve call a pointer to the posBlock.

The wrapper DLL does not need to maintain any Btrieve-specific data within the posBlock, so it is free to use this memory area for other operations. In the example DLL wrapper, the memory area is used to store the unique identifier for the SQL Server data affected by the requested operation. Regardless of the contents of the posBlock maintained by the wrapper DLL, each memory block must be unique to each corresponding SQL Server table set.

For example, Btrvapp.exe references two Btrieve files, Sales.btr and Titlepub.btr, where Sales.btr contains sales information for each title and Titlepub.btr maintains the title and publisher for each title. These files correspond to the bsales and titlepublishers tables that were created in the pubs database by the sample script, Morepubs.sql. In Btrvapp.exe, the B_OPEN operation opens the requested Btrieve file and creates its corresponding posBlock.

In the wrapper, the same posBlock now references a particular table by name. The wrapper DLL can be designed to store any form of a unique identifier that represents the SQL Server data that it accesses. Table names are used in the context of this migration strategy for ease of presentation. The keybuffer parameter contains the file name of the Btrieve file to be opened when B_OPEN is called. The wrapper DLL implementation of the B_OPEN function sets the posBlock equal to this file or table name.

The following code fragment, taken from the wrapper DLL B_OPEN implementation (For more information, see source file Mybtrv32.c on the CD-ROM that accompanies this volume), demonstrates this concept:

/*Step1:*/
if (strlen((BTI_CHAR *) keyBuffer) <= MAX_POSBLOCK_LEN)
memcpy((BTI_CHAR *) posBlock, (BTI_CHAR *) keyBuffer, keyLength);
else 
memcpy((BTI_CHAR *) posBlock, (BTI_CHAR* ) keyBuffer, 
MAX_POSBLOCK_LEN -1);

In the example, the Sales.btr posBlock is set to Sales.btr and the Titlepub.btr posBlock is set to Titlepub.btr. Btrvapp.exe always knows what SQL Server table set is being referenced based on the file name referenced in the posBlock.

The same data record structure formats are used in both the base application and the wrapper DLL. This allows the wrapper DLL to transport record data between SQL Server and Btrvapp.exe in the same format as if the data were coming from Btrieve. The data record structures used in Btrvapp.exe and Mybtrv32.dll are presented in the following example. For more information, see source files Btrvapp.c and Mybtrv32.c.

/************************************************************
Data Record Structure Type Definitions
************************************************************/
//titlepub record structure
struct{
char TitleID[7]; //string
char Title[81]; //string
char Type[13]; //string
char PubID[5]; //string
float Price; //money
float Advance; //money
int Royalty; //integer
int YTD_Sales; //integer
char PubName[41]; //string
char City[21]; //string
char State[3]; //string
char Country[31]; //string
}tpRec;

//sales record structure
struct
{
char StorID[5]; //string
char TitleID[7]; //string
char OrdNum[21]; //string
int Qty; //integer
char PayTerms[13]; //string
}salesRec;

Establishing the ODBC Connections and Initializing Data Access

Within the sample wrapper implementation, the B_OPEN operation establishes a connection to SQL Server for each table set referenced by the base application Btrvapp.exe. The operation also creates and opens the cursors used to reference the SQL Server tables. The cursors are opened on the entire table without a WHERE clause to restrict the number of rows returned. These connections and cursors are used throughout Mybtrv32.dll to reference the SQL Server tables. To avoid the time and processing overhead associated with making or breaking connections to the server, the connections are not terminated until the application is closed.

This connection and cursor implementation were chosen for two reasons. First, they simulate a Btrieve application accessing a Btrieve file: one posBlock for every open file referenced by the application. Second, they demonstrate the inefficient use of connection management when SQL Server is accessed. Only one connection is needed in the context of this wrapper implementation because multiple server cursors can be opened and fetched concurrently on a single connection. Thus, the second connection is only creating overhead within the application. A more efficient connection management methodology uses only a single connection with multiple cursors opened on that connection.

Understanding ODBC and SQL Implementation

There are many different ways to access SQL Server data with ODBC and SQL. The wrapper Mybtrv32.dll uses server-side cursors. Cursors were chosen for several reasons:

Cursors and Btrieve files are accessed similarly, for example, through the use of FETCH, GET FIRST, and NEXT operations.
Cursors demonstrate the use of ISAM-like row-at-a-time processing and positioned updating of SQL Server data.
Cursors demonstrate the difference between the row-at-a-time processing model and the default result set processing model used in the Odbcapp.exe sample application.

Each Btrieve operation that is performed in the base application is ported to an ODBC and SQL equivalent within the wrapper DLL. Some of the operations, like the B_SET_DIR operation, are not applicable to the SQL Server environment and do nothing within the wrapper DLL. Optimal implementation strategies of ODBC and SQL for both the wrapper DLL and the final application port are presented in "Converting the Application to ODBC and SQL" later in this chapter.

Handling Errors

The wrapper DLL must use Btrieve return codes when exiting each function. Each wrapper function must return B_NO_ERROR or a Btrieve error code corresponding to the type of error that was encountered. By using a valid Btrieve return code, the base application code does not know that its library function is accessing SQL Server instead of Btrieve. You must return the Btrieve return codes that are expected by the base application in order for the wrapper DLL to work properly.

However, there is no direct mapping of SQL Server to Btrieve error codes. You must translate all SQL Server errors encountered in the ODBC code of the wrapper DLL to a Btrieve return code equivalent. The following example taken from the MakeConn function in the wrapper DLL source file Mybtrv32.c demonstrates this concept:

// Allocate a connection handle, set login timeout to 5 seconds, and
// connect to SQL Server
rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, hdbc); 
// Set login timeout
if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) 
rc=SQLSetConnectAttr(*hdbc, SQL_LOGIN_TIMEOUT,(SQLPOINTER)5, 0);
else{
// An error has been encountered: notify the user and return
ErrorDump("SQLAllocHandle HDBC", SQL_NULL_HENV, *hdbc, 
SQL_NULL_HSTMT);
return B_UNRECOVERABLE_ERROR;
}

In case an error is encountered, the SQL Server error code must be mapped to an applicable Btrieve error code. For example, the preceding code fragment allocates a connection handle for use in the wrapper DLL. Because Btrieve does not have the concept of connection handles, it does not have a corresponding error code. The solution is to choose a Btrieve return code that closely matches the severity or context of the message. The connection handle error was severe enough to the application to warrant the Btrieve return code B_UNRECOVERABLE_ERROR. You can choose any Btrieve return code provided that the base application is designed to address it.

Converting the Application to ODBC and SQL

To produce a high-performance Microsoft SQL Server application, it is important to understand some of the basic differences between the SQL Server relational and the Btrieve ISAM models. The wrapper DLL described in the previous section successfully disguises SQL Server as Btrieve. However, this implementation is inefficient in the way that it accesses SQL Server data. In fact, the wrapper DLL implementation is likely to perform significantly worse than the original Btrieve application.

The wrapper DLL accesses two nonnormalized tables instead of taking advantage of the relational model capabilities that SQL Server provides. It also does not process result sets by using any of the powerful application performance enhancing features like effective Transact-SQL syntax, stored procedures, indexes, and triggers, to process result sets.

The next step in migrating Btrieve applications to SQL Server is to change the focus of processing techniques used by the base application to take full advantage of the relational model provided by SQL Server. This can be accomplished through the creation of a new application, Odbcapp.exe, that uses ODBC and SQL only to access SQL Server data.

The performance of your SQL Server database depends on the design of the database, index, and queries. These design issues should be addressed first because they can drastically improve performance with a relatively small effort. This section focuses on optimizing database, index, and query design using ODBC and SQL. Implementation comparisons between the Btrieve and SQL Server processing models will be made frequently to demonstrate the advantages that SQL Server provides.

Understanding Normalized Databases

Relational Database Management Systems (RDBMS) are designed to work best with normalized databases. One of the key differences between an indexed service access method database and a normalized database is that a normalized database has less data redundancy. For example, the file and table record formats used by Btrvapp.exe and Mybtrv32.dll for the Titlepub.btr file and the titlepublisher table have redundant data. Specifically, the publisher information is stored for every title. Therefore, if a publisher manages more than one title, its publisher information will be stored for every title entry it manages. An ISAM-based system such as Btrieve neither provides join functionality nor groups data from multiple files at the server. Therefore, ISAM-based systems such as Btrieve require that developers add the redundant publisher data to the Titles table to avoid manual join processing of two separate files at the client.

With a normalized database, the titlepublisher table is transformed into two tables: a Title table and a Publisher table. The Title table has a column that contains a unique identifier (consisting of numbers or letters) that corresponds to the correct publisher in the Publisher table. With relational databases, multiple tables can be referenced together in a query; this is called a join.

The Data Transformation Services (DTS) feature used in the data migration stage can help you automate the migration and transformation of your data from a source database to a destination database. A data transformation consists of one or more database update statements. Each software application requires specific data transformations. These migration and transformation routines should be designed and saved for reuse by the Independent Software Vendor (ISV) or application developer who understands the business logic of the database. DTS helps to ensure that the data transformations occur in the right order and can be repeated hundreds of times for different ISV customers.

Consider using DTS to automate the migration and normalization of your customers' databases. The ability of DTS to save routines helps you repeat the migration process easily for many customers. DTS can migrate any data that can be accessed through ODBC drivers or OLE DB providers and move the data directly into another ODBC/OLE DB data store.

Comparing Data Retrieval Models

Choose the implementation methodologies for data retrieval, modification, insertions, and deletions based on how the data is used in the application. Microsoft SQL Server is a powerful and flexible RDBMS. While many of the aspects of processing data in Btrieve can be applied to the SQL Server environment, you should avoid using Btrieve ISAM-like, client-side, result-set processing techniques and take full advantage of the server processing that SQL Server provides. The following discussion compares the result-set processing models of both Btrieve and SQL Server. The goal is to briefly expose the differences in these processing models and to show you how to design this functionality effectively and, in many cases, more efficiently in the SQL Server environment.

Btrieve Navigational Model

Btrieve processes result sets based on the navigational data processing model. The navigational model accesses a single data file at a time; any operation involving multiple files must be performed in the application itself. When the data file is searched, all of the record data is returned to the client regardless of the number of record attributes needed to satisfy the requests. The navigational model is characteristic of positional-based operations. The application and the Btrieve engine maintain a position within the data file, and all operations that are performed against this file are based upon this position. All updates in Btrieve are positional, requiring the application to search for and lock the record to be updated. Because the operations are position based, the application cannot change or delete database records based on search criteria.

In most cases, you use an index to perform the searches. You must know all of the fields that are indexed within each data file that the application references. (Non-index fields can be filtered using the extended get call). The Btrieve navigational model is capable of simple search arguments using the =, <>, >, <, >=, and <= comparison operators to filter the records retrieved from the data file. These search arguments are normally performed between a single indexed field and a constant. Btrieve offers an extended fetch call that can set up a search filter composed of multiple search arguments that are combined using the logical AND or OR operators, but the logical combinations of criteria in these filters are limited.

Transact-SQL

The structured query language of SQL Server is called Transact-SQL. Transact-SQL is rich and robust and, if used effectively, can make application development easy and efficient. Transact-SQL can reference both nonnormalized and normalized tables. Transact-SQL also allows you to query specific columns needed to satisfy a request, instead of returning all of the columns. Query capabilities are not limited to indexed fields; you can query any column in any table referenced in the FROM clause. To increase the speed of an operation, the query optimizer chooses among existing indexes to find the fastest access path. More details on indexes can be found later in this chapter.

Transact-SQL provides advanced searching capabilities in addition to the basic comparison operators also provided by Btrieve. Using Transact-SQL, you can perform complex joins, aggregate functions such as SUM, MAX, and MIN, and data grouping and ordering. One of the strengths of Microsoft SQL Server is its ability to rapidly access and process data at the server. Processing query results at the server reduces the client workload and the number of trips between the client and the server needed to process data.

Transact-SQL uses joins to combine information from multiple tables on the server concurrently. The following example taken from the GetTitlePubRec function Odbcapp.exe demonstrates this. The function calls a stored procedure to return all of the title and publisher information for a particular TitleID.

/********************************************************************
Returns Title and Publisher information for @titleID. The query in 
this stored procedure performs a join between the Titles and the 
Publishers table based on Publisher ID
********************************************************************/
CREATE PROCEDURE GetTPByTitleId @titleid char(6) AS
SELECT T.TITLE_ID, T.TITLE, T.TYPE, T.PUB_ID, T.PRICE, T.ADVANCE, 
T.ROYALTY, T.YTD_SALES, P.PUB_NAME, P.CITY, P.STATE, 
P.COUNTRY 
FROM TITLES T, PUBLISHERS P 
WHERE T.TITLE_ID = @titleid AND T.PUB_ID = P.PUB_ID

After the client issues the call to the stored procedure GetTPByTitleID, the server executes the stored procedure, retrieves all of the requested columns for all of the records that match the criteria in the WHERE clause, and sends the result set back to the client.

To take advantage of these server-side resources and to reduce performance problems and overhead, application developers should use Transact-SQL fully, rather than create Transact-SQL statements underneath an ISAM-like interface. With direct access to the tables using Transact-SQL, you have complete control over the way data is processed throughout the application. You can fine-tune individual data requests to optimize performance and maximize throughput and data concurrency. You may find that optimizing the most frequently used queries improves performance drastically.

Comparing Default Result Sets and Cursors

SQL Server allows you to process data within your application by using both set-based operations (called default result sets) and row-at-a-time techniques with server cursors (processed similarly to Btrieve operations). Each of these processing models has its own distinct role in the application development process. In the following section, these processing options are compared and contrasted to help you choose the one that satisfies the data processing requirements within your application.

Default Result Sets

When an application uses Transact-SQL effectively to request only the rows it needs from the server, the most efficient way to retrieve these rows is a default result set. An application requests a default result set by leaving the ODBC statement options at their default values prior to executing SQLExecute or SQLExecDirect. For a default result set, the server returns the rows from a Transact-SQL statement as quickly as possible and maintains no position within this set of rows. The client application is responsible for immediately fetching these rows into application memory. Most data processing activity within an application can and should be accomplished using default result sets. Default result sets offer several distinct advantages:

Reduce network traffic.

One or multiple default result sets can be returned with only one round-trip between the client and the server.
Conserve server resources.

Between client calls, default result sets do not maintain position or other client state information at the server. This allows your application to support many users.
Increase code efficiency.

Stored procedures that return default result sets can also contain procedural logic, all at the server. This is more efficient than moving the data out of the server to apply procedural logic at the client. Stored procedures also allow data manipulation logic to be encapsulated and shared by all client applications.

SQL Server Cursors

Most data retrievals and modifications can be performed using SQL Server default result sets. However, in some cases, an application must use row-at-a-time capabilities to satisfy a certain application request. For example, the user may have to examine information one row at a time to make a decision or to use the information from the row to proceed with another task. SQL Server provides this functionality with server cursors. Like the Btrieve navigational model, server cursors maintain a position at the server. Server cursors act like an ISAM interface, except you cannot perform seek or find operations within the cursor result set. Cursors require that you perform this functionality within the client application.

Server Cursor Differences

Server cursors can be opened on the full table or any subset of a table just like default result sets. However, cursors differ from default result sets in the following distinct ways:

Variety of fetching operations.

Depending on the type of cursor chosen (static, keyset-driven, forward-only, dynamic, and so on), the application can perform a variety of different fetching operations (FETCH FIRST, NEXT, PREVIOUS, ABSOLUTE, RELATIVE, and so on) within the scope of the cursor result set at the server. With a default result set, an application can fetch only in a forward direction.
Server cursors do not hold locks at the server on large unfetched result sets.

When an application uses default result sets, shared locks may remain in effect until the client application processes the entire result set or cancels the operation. This can be a problem if the application waits for user input before it finishes fetching. If the application does not immediately fetch all data requested, the shared locks may be held for a long time, preventing others from updating or deleting. Because server cursors do not hold locks by default, they are unaffected by this application behavior.
Server cursors allow you to perform positional updates.

Default result sets, because of their set-based nature, do not provide this functionality. If a client application needs to update or delete a row retrieved from a default result set, it must use a SQL UPDATE or DELETE statement with a WHERE clause containing the row's primary key or call a stored procedure that takes the primary key and new values as parameters.
Multiple active statements.

By using ODBC, server cursors allow you to have multiple active statements on a single connection. An application that uses default result sets must fetch result rows entirely before a connection can be used for another statement execution.

Server Cursor Performance Costs and Limitations

Server cursors within SQL Server are powerful, flexible, and useful for ISAM-like, row-at-a-time processing and for positional updates. However, server cursors incur some cost and have some limitations:

More resources are required.

Opening a server cursor is more expensive than using default result sets within SQL Server in terms of parse, compile, and execute time and tempdb resources. The additional cost of a server cursor varies by query depending on the number of tables involved (for example, single table or join) and type chosen (static, keyset-driven, forward-only, dynamic, and so on).
There are limits on their usage.

Server cursors cannot be opened on Transact-SQL batches or stored procedures that return multiple result sets or involve conditional logic.
There may be increased network traffic.

Fetching from a server cursor requires one round-trip from client to server to open the cursor and retrieve the rowset number of rows specified within the application. In ODBC terms, this means one round-trip per call to SQLFetchScroll (or SQLExtendedFetch in ODBC 2.0).

Determining the Rowset Size of a SQL Server Cursor

The rowset size that is used to process a server cursor affects the processing throughput of your application. In ODBC, you can communicate the number of rows to fetch at a time by using the ROWSET SIZE statement option. The size you choose for the rowset depends on the operations performed by the application. Screen-based applications commonly follow one of two strategies: Setting the rowset size to the number of rows displayed on the screen or setting the rowset size to a larger number. If the user resizes the screen, the application changes the rowset size accordingly. Setting the rowset size low causes unnecessary fetching overhead. Setting the rowset size to a larger number, such as 100, reduces the number of fetches between the client and the server needed to scroll through the result set. Setting the rowset size high requires a larger buffer on the client, but minimizes the number of round-trips needed to return the result set between the client and server. An application that buffers many rows at the client enables the application to scroll within the client-side buffer instead of repeatedly requesting data from the server. In that case, the application only fetches additional rows when the user scrolls outside of the buffered portion of the result set.

Conclusions: Default Result Sets and SQL Server Cursors

You should carefully analyze the scope of each task within the application to decide whether default result sets or server cursors should be used. Default result sets should be used as much as possible. They require fewer resources, and their result sets are easier to process than those of server-side cursors. When a data request will retrieve a small number of rows or only one, and the application does not require positional updates, be sure to use a default result set.

Server cursors should be used sparingly and should be considered only for row-at-a-time processing within the application. If your application requires server cursors despite their performance disadvantages, make sure that the rowset size is set to a reasonably high value.

Understanding Data Access Interface Issues

Now that several different data processing implementation techniques have been compared, the next step is to use these methods to access SQL Server data effectively. The proper implementation of these concepts eliminates application overhead.

Accessing Tables Directly with Transact-SQL

Some third-party application development controls and objects provide properties and methods for accessing and retrieving data. These objects and controls expedite the development process by creating a simple interface that allows you to access tables while minimizing or eliminating the need to use Transact-SQL. This abstraction layer can put you at a disadvantage, however, because you will reduce your ability to tune the data-access operations.

By using Transact-SQL statements and stored procedures, you have direct access to the tables involved with your application. This allows you to determine how, and in what order, the data operations are performed. By accessing the tables directly, your application can issue Transact-SQL statements that are tuned for optimal performance.

In addition, stored procedures written in Transact-SQL can be used to encapsulate and standardize your data access.

Implementing Data Retrieval Effectively

You may be tempted to use server cursors to implement data seek operations at the client to emulate the ISAM-like processing techniques used in the Btrieve environment. However, using this methodology to implement data retrieval greatly diminishes the performance advantages of using SQL Server.

Introducing the Query Optimizer

SQL Server incorporates an intelligent, cost-based query optimizer that quickly determines the best access plan for executing a Transact-SQL statement. The query optimizer is invoked with every Transact-SQL statement that is sent to the database server. Its goal is to minimize execution time, which generally minimizes physical data accesses within the database. The query optimizer chooses from among the indexes you create on the tables. For more information about indexes, see "Using Effective Indexing Techniques," later in this chapter.

Joining Tables on the Server

Use table joins on the server rather than processing nested iterations of result set data on the client to reduce the amount of processing required at the client and the number of round-trips between the client and the server to process a result set.

Managing Concurrency and Locking Within the Application

Data concurrency and lock management are critical implementation issues in database application development. Effective lock management can have a substantial impact on data concurrency, scalability, and the overall performance of a database application system.

The row-level locking functionality of Microsoft SQL Server 7.0 resolves most application developers' database concurrency and locking issues. Nevertheless, a comparison of Btrieve (explicit) locking mechanisms and SQL Server (implicit) row-level locking capabilities ensures a smooth migration of the Btrieve application to SQL Server.

Locking and Concurrency Within the ISAM/Btrieve Model

In the Btrieve model, records can be locked by the application automatically. You can lock records automatically inside the scope of a transaction or manually on the statement level. Locks can be placed on a row, page, or the entire file; however, the decision of when and how to lock is left to you.

Because you are responsible for lock management, you must choose an optimistic or pessimistic locking concurrency approach in multiuser scenarios. Hard coding your locking scheme reduces the flexibility and scalability of the application. One locking scheme may not be optimal in all user environments due to varying database sizes and user-concurrency conditions. You must carefully examine each transaction and data modification made within the application to determine what locks are needed to satisfy its requirements.

Regardless of the locking scheme you choose in Btrieve, record reading, record writing, and the locks corresponding to each action are not implemented as an atomic unit. You must first read a record with a lock before it can be updated or deleted. This requires at least two round-trips between the client and the server for each data modification or deletion. For example, the following code retrieves a single record from Titlepub.btr by its TitleID with a single-row, wait-record lock (Btrieve lock bias 100) and then updates the record:

/* GET TITLE/PUBLISHERS RECORD WITH op and lock bias 100*/
memset( &tpRec, 0, sizeof(tpRec) );
dataLen = sizeof(tpRec);
tpStat = BTRV( op+100, tpPB, &tpRec, &dataLen, keyBuf, keyNum );
if (tpStat != B_NO_ERROR)
return tpStat;
.
.
.
// Update with -1 key value because key for the record is not to be
// changed
tpStat = BTRV(B_UPDATE, tpPB, &tpRec, &dataLen, TitleID, -1 );
if (tpStat != B_NO_ERROR){
printf( "\nBtrieve TitlePublishers UPDATE status = %d\n", tpStat );
return B_UNRECOVERABLE_ERROR;
}

Because multiple round-trips are required to process these types of requests, their associated locks are maintained longer. This may reduce the concurrency and scalability of the application. SQL Server performs the locks and data modification or deletion in one step, reducing both the round-trips and the lock maintenance overhead. SQL Server can also automatically perform optimistic concurrency locking by using cursors.

Locking and Concurrency Within SQL Server

SQL Server 7.0 supports row-level locking, which virtually eliminates the locking problems that added complexity to ISAM and RDBMS programming in the past. This drastically reduces the development time and complexity of the client application.

SQL Server escalates lock granularity automatically based on the constraints of the query or data modification that is issued. SQL Server does not require you to perform a separate read and lock before the application can update or delete a record. SQL Server reads and performs the required lock in a single operation when an application issues an UPDATE or DELETE statement. The qualifications in the WHERE clause tell SQL Server exactly what data will be affected and ultimately locked. For example, the following stored procedure and ODBC code perform the same update of a record based on its TitleID as the preceding Btrieve example:

/****** Object: Stored Procedure dbo.UpdtTitlesByPubID ******/
CREATE PROCEDURE UpdtTitlesByPubID @PubID char(4) AS
UPDATE TITLES SET YTD_SALES = YTD_SALES + 
(SELECT SUM(QTY) FROM SALES WHERE TITLES.TITLE_ID = 
SALES.TITLE_ID) 
WHERE PUB_ID = @PubID
GO
// Bind the PubID input parameter for the stored procedure
rc = SQLBindParameter(hstmtU, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 4, 0, 
choice, 5, &cbInval);
if (rc!=SQL_SUCCESS && rc!=SQL_SUCCESS_WITH_INFO) {
ErrorDump("SQLBIND SELECT TITLEPUB 1", SQL_NULL_HENV, 
SQL_NULL_HDBC, hstmtU);
SQLFreeStmt(hstmtU, SQL_RESET_PARAMS);
SQLFreeStmt(hstmtU, SQL_CLOSE);
return FALSE;
}
// Execute the UPDATE
rc=SQLExecDirect(hstmtU, "{call UpdtTitlesByPubID(?)}", SQL_NTS);
if ((rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)){
ErrorDump("SQLEXECUTE UPDATE TITLEPUB", SQL_NULL_HENV, 
SQL_NULL_HDBC, hstmtU);
return FALSE;
}

The numbers of operations needed to perform a task are reduced because SQL Server handles the row-level locking.

Emulating ISAM Processing and Locking Techniques with SQL Server

If you are accustomed to the nonrelational ISAM model, you may want to use ISAM data locking and concurrency management techniques within your SQL Server application code. These techniques, however, eliminate the performance-enhancing advantages that SQL Server provides. The following list presents two fundamental locking and concurrency implementation challenges that result from using ISAM processing and locking techniques, and a brief description of how each issue can be avoided.

If you attempt to implement pessimistic SELECT FROM T (UPDLOCK) or locking cursors (using the SQL_CONCURRENCY statement option) to mimic the ISAM processing model, remember that Microsoft SQL Server performs the row-level locking automatically based on the query or data modification that has been issued; therefore, you do not have to lock the target record explicitly within your application.
If SQL Server rereads the same record multiple times with lock to verify record existence before performing an update or delete, consider that in an ISAM environment such as Btrieve, you must verify a record's existence before it can be modified or deleted. This processing model incurs a great deal of overhead. It also reduces concurrency within the data due to the number of round-trips between the client and application that are needed to perform this verification and the number and durations of locks acquired by these checks.

SQL Server provides a mechanism for eliminating this overhead. The SQL Server @@ROWCOUNT function indicates the number of rows that were affected by the last operation. Use this function when you issue an UPDATE or DELETE statement within the application to verify how many records were affected. If no records exist that match the qualifications you specify in the WHERE clause, @@ROWCOUNT will be set to zero, and no records will be affected. The following example demonstrates the use of @@ROWCOUNT for this purpose:

UPDATE PUBLISHERS SET PUB_NAME = 'Microsoft Press', City = 'Redmond', State= 'WA', Country = 'USA' WHERE = TITLE_ID = 'BU1032'
/* Verify that record was updated */
IF @@ROWCOUNT <1
/* Record does not exist so create it with correct values */
INSERT PUBLISHERS VALUES ('BU1032', 'Microsoft Press', 
'Redmond', 'WA', 'USA') WHERE TITLE_ID = 'BU1032'

In the preceding example, the UPDATE is performed, and @@ROWCOUNT is set to the number of records it affected. If no records were modified, then a new record is inserted.

Implementing Effective Database and Query Design

Database and query design have a dramatic impact on the performance of your SQL Server application. Successful planning and design at this stage can have positively influence your application's performance.

The first step in effective query design is to limit the amount of data transferred between the client and the server by limiting the columns specified in a Transact-SQL statement to the values required by the application (for example, through the efficient use of the SELECT and FROM clauses), and by limiting the number of rows fetched from the database (for example, through the efficient use of the WHERE clause).

After reviewing the Transact-SQL statements to ensure that they request only the required rows and columns, a database developer must consider the use of indexes, stored procedures, and efficient coding to improve application performance.

Selecting Specific Table Columns

In the Btrieve environment, most operations retrieve every column or attribute for each record involved in an operation. With SQL Server, this technique can be inefficient, especially in cases where only a small number of columns are actually required. For example, the GetSales function in Btrvapp.exe retrieves all of the sales record attributes from Sales.btr even though only the TitleID and Qty attributes are needed to complete the task. The following is the code fragment from the GetSales function in Mybtrv32.c that exhibits this behavior:

/* Get TITLE/PUBLISHER with OPERATION*/
// Copy the desired TitleID to the keyBuffer for use by Btrieve and
// initialize parameters
strcpy(TitleID, keyBuf); 
memset( &salesRec, 0, sizeof(salesRec) );
dataLen = sizeof(salesRec);'
// Retrieve the sales record 
salesStat = BTRV( op, salesPB, &salesRec, &dataLen, keyBuf, 1 );
if (salesStat != B_NO_ERROR)
return salesStat;

The same design inefficiency can be implemented in the SQL Server environment by selecting all of the columns from tables, views, or cursors involved in an operation. For example, the following code fragment from the GetSales function retrieves all of the sales record attributes from the BSALESCURSOR even though only TitleID and Qty are needed to complete the task.

// Bind result set columns to buffers
SQLBindCol(hstmt, 1, SQL_C_CHAR, salesRec.StorID, 5, &cbStorID);
SQLBindCol(hstmt, 2, SQL_C_CHAR, salesRec.TitleID, 7, &cbTitleID);
SQLBindCol(hstmt, 3, SQL_C_CHAR, salesRec.OrdNum, 21, &cbOrdNum);
SQLBindCol(hstmt, 4, SQL_C_SLONG, &salesRec.Qty, 0, &QtyInd);
SQLBindCol(hstmt, 5, SQL_C_CHAR, salesRec.PayTerms, 13, &cbPayTerms);
// Fetch records one-at-a-time from the server until the desired
// record is found
while(!found)
{
memset(&salesRec, 0, sizeof(salesRec)); // Initialize the record buffer
// Fetch the record from the server cursor
rc = SQLFetchScroll(hstmt, FetchOrientation, FetchOffset);
if ((rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO))
.
.
.

You can avoid design inefficiency by accessing only the record attributes required to satisfy a particular task. Odbcapp.exe demonstrates a more efficient design concept in its GetSales function. The GetSales function in Odbcapp.exe calls the GetSales stored procedure from the SQL Server pubs database to retrieve only the TitleID and Qty columns for the desired title. The following code fragment presents the GetSales stored procedure. It demonstrates how the stored procedure is executed and its results processed in the Odbcapp.exe GetSales function.

/*Get Sales stored procedure */
CREATE PROCEDURE GetSales @titleid char(6) AS
SELECT TITLE_ID, QTY FROM SALES WHERE TITLE_ID = @titleid
GO
// Execute the stored procedure and bind client buffers for each
// column of the result
rc = SQLExecDirect(hstmtS, "{callGetSales(?)}", SQL_NTS);
.
.
.
SQLBindCol(hstmtS,1, SQL_C_CHAR, TitleID, 7, &cbTitleID);
SQLBindCol(hstmtS, 2, SQL_C_SLONG, & Qty, 0, &QtyInd);
// Fetch result set from the server until SQL_NO_DATA_FOUND
while( rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
rc = SQLFetch(hstmtS);
.
.
.

Using WHERE Clauses to Reduce the Result Set Size

Use WHERE clauses to restrict the quantity of rows returned. Using WHERE clauses reduces the total amount of data affected by the operation, reduces unnecessary processing, and minimizes the number of locks needed to process the request. By using the WHERE clause to reduce the result set, you can avoid table contention, reduce the amount of data transferred between the client and the server, and increase the processing speed of the request.

For example, the following cursor code was taken from the CreateCursor function of Mybtrv32.c:

// Creates the BSALESCURSOR
if (!strcmp(curname, "BSALESCURSOR"))
rc=SQLExecDirect(*hstmt2,
"SELECT STOR_ID, TITLE_ID, ORDNUM, QTY, PAYTERMS FROM 
BSALES", SQL_NTS);

The BSALESCURSOR is created without using a WHERE clause. As a result, the server creates a cursor that retrieves all rows in the bsales table. This cursor results in more resources and processing at the client than it needs.

The application actually requires that the query obtain the sales information for a particular TitleID column. It would be more efficient to use a WHERE clause that defines the exact TitleID or even a range of TitleID columns. This would reduce the amount of data sent for client-side examination and the number of round-trips between the client and the server. This example is shown below. Notice that the cursor is more efficient because it only requests the TitleID and Qty columns from the bsales table; in this application, only those columns are used by the business logic.

if (!strcmp(curname, "BSALESCURSOR")){
SQLBindParameter(hstmtU, 1, SQL_PARAM_INPUT, SQL_C_CHAR, 
SQL_CHAR, 6, 0, inval, 7, &cbInval);
rc=SQLExecDirect(*hstmt2,
"SELECT TITLE_ID, QTY FROM BSALES WHERE TITLE_ID LIKE ?",
SQL_NTS);

Using the Singleton SELECT

A singleton SELECT returns one row based on the criteria defined in the WHERE clause of the statement. Singleton SELECTs are often performed in applications and are worthy of special attention. Because only one row is returned, you should always use a default result set SELECT statement rather than a server-side cursor to retrieve the record. The default result set SELECT statement retrieves the record faster and requires far fewer resources on both the client and the server. The following code fragment is an example of a singleton SELECT that returns the Pub_ID and Title for a single Title_ID:

SELECT PUB_ID, TITLE FROM TITLES WHERE TITLE_ID = 'PC8888'

Because singleton SELECTs are performed frequently in applications, consider creating stored procedures to perform these SELECT statements. By using a stored procedure rather than issuing a SELECT statement directly, you can reduce the parse, compile, and execute time necessary to process the request. The following code, taken from the GetTitlePubRec function in Odbcapp.exe, executes a singleton SELECT through the GetTPByTitleId stored procedure. Notice the small amount of processing needed in Odbcapp.exe to execute this stored procedure.

switch(key)
{
case 1: // Title_ID search
strcpy(StoredProc, "{call GetTPByTitleID(?)}");
// Identify stored procedure to call
// Bind the input parameter buffer
SQLBindParameter(hstmtU, 1, SQL_PARAM_INPUT, 
SQL_C_CHAR, 
SQL_CHAR, 6, 0, inval, 7, &cbInval);
break;
.
.
.
// Execute the stored procedure and bind result set row columns to variables
memset( &tpRec, 0, sizeof(tpRec) ); // Initialize buffer record
// structure
rc=SQLExecDirect(hstmtU, StoredProc, SQL_NTS );
.
.
.
SQLBindCol(hstmtU, 1, SQL_C_CHAR, tpRec.TitleID, 7,
&cbTitleID);
SQLBindCol(hstmtU, 2, SQL_C_CHAR, tpRec.Title, 81, &cbTitle);
.
.
.
SQLBindCol(hstmtU, 12, SQL_C_CHAR, tpRec.Country, 31, 
&cbCountry);
// Process the results until SQL_NO_DATA_FOUND
while (rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO) 
{
rc=SQLFetch(hstmtU);
if (rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO) {
.
.

Using Effective Indexing Techniques

Careful index design improves the performance of a SQL Server database. The query optimizer in SQL Server selects the most effective index for most cases; however, the following new features of SQL Server 7.0 allow you to create the best indexes for your application:

The Index Tuning Wizard allows you to select and create an optimal set of indexes and statistics for a SQL Server database without requiring an expert understanding of the structure of the database, the workload, or the internals of SQL Server.
The graphical SQL Server Query Analyzer *** *** provides easy and in-depth query analysis. You may want to create one index per table as a starting point, and then run the SQL Server Query Analyzer after a few days in a test environment to further optimize the database.

For guidelines about index creation before you implement the Index Tuning Wizard and SQL Server Query Analyzer, see "Recommendations for Creating Indexes" later in this chapter.

Implementing Stored Procedures

Stored procedures enhance the power, efficiency, and flexibility of Transact-SQL and can dramatically improve the performance of Transact-SQL statements and batches. Stored procedures differ from individual Transact-SQL statements because they are precompiled. The first time a stored procedure is run, the SQL Server query engine creates an execution plan and stores the procedure in memory for future use. Subsequent calls to the stored procedure run almost instantaneously since most of the preprocessing work has already been completed. The Odbcapp.exe application demonstrates how stored procedures are called using ODBC within an application.

For more information on stored procedures, see SQL Server Books Online* *and the Microsoft Knowledge Base.

Keeping Transactions Short and Efficient

Short and efficient transactions decrease the number of row-level locks managed by the system at any given point in time and are considered to be good programming practice. This is especially true in mixed decision support and online transaction processing (OLTP) environments.

Updating and Deleting Data as Set Operations

Use the WHERE clause to implement UPDATE and DELETE statements as set-based operations, restricting the data involved with the operation to that which matches a specific criteria. By implementing these operations using this methodology, you only update or delete the rows you intend to update or delete, reduce the total amount of data affected by processing the operations, and reduce the number of row-level locks needed to process them.

These operations should be performed only by using server cursors if the criteria for determining the rows for the UPDATE or DELETE operations cannot be specified in the Transact-SQL statement itself.

The following two examples demonstrate the difference between a set-based update and a positional update using a cursor. Both of these examples update the YTD_Sales for each title covered by a specific PubID. The first example is a stored procedure used by Odbcapp.exe. It demonstrates the use of a default result set update that uses a WHERE clause.

/****** Object: Stored Procedure dbo.UpdtTitlesByPubID ******/
CREATE PROCEDURE UpdtTitlesByPubID @PubID char(4) AS
UPDATE TITLES SET YTD_SALES = YTD_SALES + (SELECT SUM(QTY) FROM
SALES WHERE TITLES.TITLE_ID = SALES.TITLE_ID) 
WHERE PUB_ID = @PubID
GO

The preceding example is efficient and uses the server to perform the processing and row selection for the UPDATE.

The following example taken from Mybtrv32.exe demonstrates the inefficient use of a positional update through a cursor. This example must fetch through the cursor, updating each record that has the desired PubID. Notice the amount of fetching (round-trips between the client and the server) needed to process this request.

// The following code is taken from the GetTitlePublisher function in
// Mybtrv32.c
// Scroll through the cursor a row-at-a-time until the row needing
// updated is found.
while (!found)
{
memset( &tpRec, 0, sizeof(tpRec) ); // Initialize
// the client row buffer
// Fetch the record 
rc=SQLFetchScroll(hstmt8, FetchOrientation, FetchOffset);
if ((rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO))
{
if (rc!=SQL_NO_DATA_FOUND){ 
// Error encountered before end of cursor notify the user and return 
ErrorDump("SQLFetchScroll TitlePub", SQL_NULL_HENV, 
SQL_NULL_HDBC, hstmt8);
return B_UNRECOVERABLE_ERROR;
}
else {
return B_END_OF_FILE;} // End of cursor
// found. Record does not exist
} 
// Check to see if this is the record we want to update
if (!strcmp(keyBuffer, tpRec.PubID))
found=1;
}
// The record to be updated has been found. The next step is to
// update it.
// The following code is taken from the CursorUPD function in
// Mybtrv32.c
// Initialize the client record buffer
memset( &tpRec, 0, sizeof(tpRec) );
memcpy(&tpRec, dataBuffer, sizeof(tpRec));
// Initialize the tpRec data structure 
memset( &tpRec, 0, sizeof(tpRec) );
memcpy(&tpRec, dataBuffer, sizeof(tpRec));
/* Update the current row within the cursor. We rebind the columns
/* to update the length of the NULL terminated string columns. We 
/* are using 0 for the numRows parameter to affect all rows in 
/* the rowset. Since we have a rowset size of 1 only the positioned 
/* row will be affected. The key value of the current record is not 
/* changing so we issue the positioned update using SQLSet
/* Pos(SQL_UPDATE, SQL_LOCK_NO_CHANGE)*/
SQLBindCol(hstmtS, 1, SQL_C_CHAR, tpRec.TitleID, 7, &cbTitleID);
.
.
.
rc=SQLSetPos(hstmtS, numRows, SQL_UPDATE, SQL_LOCK_NO_CHANGE);
if ((rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO))
{
ErrorDump("SQLSetPos SQL_UPDATE for TITLEPUBLISHER FAILED", SQL_NULL_HENV, SQL_NULL_HDBC, hstmtS);
return B_UNRECOVERABLE_ERROR;
}
return B_NO_ERROR;

Using Server Resources Effectively

This section focuses on maximizing the benefits of Microsoft SQL Server server-side resources. There are several areas on the server side that relate closely to the overall performance of your application system. Although these topics fall out of the scope of mainstream application development, when used effectively they provide benefits important to the overall performance of the system.

Three of the most versatile and powerful server-side resources are triggers, declarative referential integrity (DRI), and views. Triggers, DRI, and views are often used to reduce the complexity of the client and application. Used effectively, these features can improve the performance of your application.

Business Rules and Referential Integrity

In a SQL Server database, triggers are special stored procedures that take effect when data is modified in a specific table. Business rule consistency is enforced across logically related data in different tables by using triggers. Triggers are executed automatically when data modification occurs, regardless of the application interface that is used.

Referential integrity refers to the way in which an RDBMS manages relationships between tables. Referential integrity is implemented in the database using the CREATE TABLE or ALTER TABLE statements, with a clause that starts with FOREIGN KEY. For example, in a scenario with Orders and OrderLineItems, records should not exist in the OrderLineItems table if there is no corresponding record in the Orders table. Because Btrieve does not offer this feature, the Btrieve application performs all referential integrity at the client. Enforcing referential integrity at the server eliminates this processing from the client and can provide slight performance improvements.

Triggers and foreign key constraints also eliminate the need to change the application in multiple places if table schemas or relationships change. These modifications can be made at the server.

Views

A view is an alternate way of looking at data resulting from a query of one or more tables. Views allow users to focus on data that fits their particular needs. Views simplify the way users can look at the data and manipulate it. Frequently used joins and selections can be defined as views so that users can avoid respecifying conditions and qualifications each time additional data operations are performed. Views also provide logical data independence because they help to shield users from changes in the structure of the base tables. In many cases, if a base table is restructured, only the view has to be modified, rather than each individual reference to the table.

For more information about views and triggers, see SQL Server Books Online and the Microsoft Knowledge Base.

Recommendations for Creating Indexes

Users of Microsoft SQL Server 7.0 can benefit from the new graphical SQL Server Query Analyzer and Index Tuning Wizard. These tools remove the guesswork from index creation. Nevertheless, understanding some basic index design recommendations can be useful to developers new to RDBMS.

Index to enforce a primary key

The system creates a unique index on a column or columns referenced as a primary key in a CREATE TABLE or ALTER TABLE statement. The index ensures that no duplicate values are entered in the column(s) that comprise the primary key. SQL Server does not require you to declare a primary key for every table, but it is considered good programming practice for RDBMS developers.
Indexes to improve performance

You can optionally create unique or nonunique indexes on tables. These indexes can improve the performance of queries against the table.
Unique indexes

Unique indexes improve performance and ensure that the values in the specified columns are unique.
Clustered indexes

Clustered indexes physically order the table data on the indexed column(s). Because the table can be physically ordered in only one way, only one clustered index can be created per table. If you have only one index on a table, it should be a clustered index. PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.

Database Index Guidelines

The following are a few database index guidelines:

The columns in a WHERE clause of a Transact-SQL query are the primary focus of the query optimizer.

The column(s) listed in the WHERE clause of a Transact-SQL statement is a possible candidate for an index. For each table, consider creating optional indexes based on the frequency with which the columns are used in WHERE clauses, and take into account the results of the graphical SQL Server Query Analyzer.
Use single-column indexes.

Single-column indexes are often more effective than multicolumn indexes. First, they require less space, allowing more index values to fit on one data page. Secondly, the query optimizer can effectively analyze thousands of index-join possibilities. Maintaining a large number of single (or very few)-column indexes provides the query optimizer with more options from which to choose.
Use clustered indexes.

The SQL Server Query Analyzer helps you determine which clustered indexes to create. Appropriate use of clustered indexes can significantly improve performance.

For more information on indexing and performance, see SQL Server Books Online and the Microsoft Knowledge Base.