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.