Datasheet

22
Part I ✦ Laying the Foundation
Figure 1-3: Optimization Theory explains that each optimization
technique is dependent upon, and enabled by, other optimization
techniques.
Queries
SQL is a set-based system, and iterative row-by-row operations actually function as zillions of
small single-row sets. Whether the iterations take place as server-side SQL cursors or ADO
loops through a record set, iterative code is costly. My number-two performance strategy is
to use set-based solutions. But good set-based code can’t overcome a clumsy or overly com-
plex schema design.
When deciding where to use iterative code vs. set-based query, use Table 1-4 as a guiding rule
of thumb.
Table 1-4: Coding Methods
Problem Best Solution
Complex business logic Queries, subqueries, CTEs
Dynamic DDL Generation Cursors
Demoralizing a List Multiple assignment variables or cursor
Crosstab Query with pivot or case expression
Navigating a hierarchy UDF or CTE
Cumulative totals, running sums Cursor
Advanced
Scalability
Concurrency
Indexing
Set-Based
Schema
Can’t overcome
Optimization
Theory
Enables
05_542567 ch01.qxp 9/27/06 9:58 PM Page 22