5-311 1 2 3 4

No discussion of performance and tuning would be complete without addressing the client side.  This second session was devoted entirely to addressing performance characteristics on the client side.

The first step in creating high performance applications is in choosing the correct data access API.  This is the fine art of selecting from Microsoft's alphabet soup of APIs.  Do I use JDBC, ESQL, DB-Lib, DAO, RDO, RDS, ODBC, OLE DB, or ADO?  So many choices, so few applications.  For those who have been following the evolution of the APIs, the choices are relatively clear.  You use either JDBC, ODBC, OLE DB, or ADO with OLE DB and ADO being the favored APIs.  The one thing to note out of this for those DB-Lib developers is to start the migration to another API as DB-Lib is no longer being worked on and can not access the majority of the features in SQL Server 7.0.

SQL Server 7.0 contains a querying feature that you can make use of in your applications to improve performance.  I really wasn't aware of this until I reached Tech Ed.  I'll cover it briefly here, but will provide more detailed information in the Internals session synopsis.  The feature I'm referring to is Ad-hoc query plan caching.  We are all familiar with the plan caching that occurs with triggers and stored procedures that causes them to perform better than ad-hoc SQL.  You can now take advantage of those efficiencies even with ad-hoc queries.  Plan caching for ad-hoc queries is automatic.  In caching the query plans, SQL Server will parameterize queries.  The downfall to the plan caching is that only queries with exact textual matches will cause a plan to be reused.  The example given is as follows:

insert mytable values (1.0)
insert mytable values (2.0)
insert mytable values (1.0)

The 3rd query will reuse the cached plan from query 1.  But, the second query will be compiled from scratch.

The auto-parameterization is limited to simple statements.  Essentially, SQL Server substitutes parameters for constants and if it determines the parameterization is "safe", a template is cached.  Then any subsequent queries that follow the template created, will use the corresponding cached plan.  Auto-parameterization will be attempted for insert, update, delete, and select statements.  These are of the simple variety: insert...values, delete ... where,  update..set...where, and select...from...where...order by.  Basically, only those statements that produce an invariant result when parameterized will be subject to auto-parameterization and plan caching.

Stored procedures can be one of the single best constructs to be used in development when implemented properly.  Stored procs place the business logic close to the data and provide a single point of control.  Query plans are compiled and cached giving you a performance boost.  It also reduces network traffic since you are only sending a call to the stored proc instead of an entire batch of SQL.  The can become a hindrance though when you force recompiles of the query plans.  If you are using temp tables, restrict access to the temp tables to the proc that creates them.  Accessing a temp table from a calling or called proc or within an exec string forces a recompile of the query plan invalidating any performance boost you would have gotten.  Declaring a cursor over a temp table will also invalidate the query plan.  Stored procedures do require persistent object management.

5-311 1 2 3 4