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 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.
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.