The Data Access Handbook: Achieving Optimal Database Application Performance and Scalability

Home Page\r......Page 1
Contents......Page 5
Preface......Page 13
Acknowledgments......Page 17
About the Authors......Page 19
CHAPTER 1 PERFORMANCE ISN’T WHAT IT USED TO BE......Page 21
Where Are We Today?......Page 24
The Database Driver......Page 25
The Environment......Page 26
Your Database Application......Page 27
Our Goal for This Book......Page 28
CHAPTER 2 DESIGNING FOR PERFORMANCE: WHAT’S YOUR STRATEGY?......Page 29
Database Connections......Page 30
Transaction Management......Page 41
SQL Statements......Page 47
Data Retrieval......Page 50
Extended Security......Page 57
Static SQL Versus Dynamic SQL......Page 63
The Network......Page 64
The Database Driver......Page 66
Know Your Database System......Page 67
Using Object-Relational Mapping Tools......Page 68
Summary......Page 69
CHAPTER 3 DATABASE MIDDLEWARE: WHY IT’S IMPORTANT......Page 71
How Database Middleware Affects Application Performance......Page 72
Database Drivers......Page 73
What Does a Database Driver Do?......Page 74
Database Driver Architecture......Page 75
Runtime Performance Tuning Options......Page 82
Configuring Database Drivers/Data Providers......Page 83
Summary......Page 94
CHAPTER 4 THE ENVIRONMENT: TUNING FOR PERFORMANCE......Page 95
JVM......Page 97
.NET CLR......Page 102
Operating System......Page 103
Database Protocol Packets......Page 106
Network Packets......Page 109
Configuring Packet Size......Page 112
Analyzing the Network Path......Page 114
Reducing Network Hops and Contention......Page 116
Avoiding Network Packet Fragmentation......Page 118
Hardware......Page 126
Memory......Page 127
Disk......Page 130
CPU (Processor)......Page 132
Network Adapter......Page 136
Virtualization......Page 139
Summary......Page 141
CHAPTER 5 ODBC APPLICATIONS: WRITING GOOD CODE......Page 143
Using Connection Pooling......Page 144
Using One Connection for Multiple Statements......Page 145
Obtaining Database and Driver Information Efficiently......Page 146
Managing Commits in Transactions......Page 147
Choosing the Right Transaction Model......Page 155
Using Stored Procedures......Page 156
Using Statements Versus Prepared Statements......Page 158
Using Arrays of Parameters......Page 159
Using the Cursor Library......Page 161
Retrieving Long Data......Page 162
Limiting the Amount of Data Retrieved......Page 164
Using Bound Columns......Page 165
Using SQLExtendedFetch Instead of SQLFetch......Page 167
Determining the Number of Rows in a Result Set......Page 168
Using SQLSpecialColumns to Optimize Updates and Deletes......Page 169
Minimizing the Use of Catalog Functions......Page 171
Avoiding Search Patterns......Page 172
Using a Dummy Query to Determine Table Characteristics......Page 173
Summary......Page 175
CHAPTER 6 JDBC APPLICATIONS: WRITING GOOD CODE......Page 177
Using Connection Pooling......Page 178
Using One Connection for Multiple Statements......Page 179
Disconnecting Efficiently......Page 180
Obtaining Database and Driver Information Efficiently......Page 182
Managing Commits in Transactions......Page 183
Choosing the Right Transaction Model......Page 189
Using Stored Procedures......Page 190
Using Statements Versus Prepared Statements......Page 192
Using Batches Versus Prepared Statements......Page 193
Using getXXX Methods to Fetch Data from a Result Set......Page 195
Retrieving Auto-Generated Keys......Page 196
Retrieving Long Data......Page 197
Limiting the Amount of Data Retrieved......Page 199
Choosing the Right Data Type......Page 201
Choosing the Right Cursor......Page 202
Using getBestRowIdentifier() to Optimize Updates and Deletes......Page 206
Avoiding Search Patterns......Page 208
Using a Dummy Query to Determine Table Characteristics......Page 209
Summary......Page 211
CHAPTER 7 .NET APPLICATIONS: WRITING GOOD CODE......Page 213
Using Connection Pooling......Page 214
Establishing Connections One at a Time......Page 215
Disconnecting Efficiently......Page 216
Managing Commits in Transactions......Page 218
Choosing the Right Transaction Model......Page 225
Executing SQL Statements that Retrieve Little or No Data......Page 226
Using the Command.Prepare Method......Page 228
Using Arrays of Parameters/Batches Versus Prepared Statements......Page 229
Using Pure Managed Providers......Page 232
Avoiding the CommandBuilder Object......Page 233
Choosing Between a DataReader and DataSet Object......Page 234
Using GetXXX Methods to Fetch Data from a DataReader......Page 235
Retrieving Long Data......Page 236
Limiting the Amount of Data Retrieved......Page 237
Choosing the Right Data Type......Page 239
Updating Data......Page 240
Summary......Page 241
Connection Pool Model for JDBC......Page 243
Configuring Connection Pools......Page 245
Guidelines......Page 246
Connection Pooling as Defined in the ODBC Specification......Page 248
Configuring Connection Pools......Page 249
Connection Pool Model for ADO.NET......Page 250
Guidelines......Page 251
Using Reauthentication with Connection Pooling......Page 252
Configuring Connection Pooling with Reauthentication in a JDBC Environment......Page 255
Using Statement Pooling......Page 256
Using Statement Pooling with Connection Pooling......Page 258
Guidelines......Page 259
Summary: The Big Picture......Page 260
CHAPTER 9 DEVELOPING GOOD BENCHMARKS......Page 263
Define Benchmark Goals......Page 264
Reproduce the Production Environment......Page 266
Isolate the Test Environment......Page 271
Measure the Right Tasks......Page 272
Measure over a Sufficient Duration of Time......Page 274
Make Changes One at a Time......Page 277
Benchmark Example......Page 278
Summary......Page 284
CHAPTER 10 TROUBLESHOOTING PERFORMANCE ISSUES......Page 285
Where to Start......Page 286
Changes in Your Database Application Deployment......Page 288
The Database Application......Page 289
The Database Driver......Page 290
Architecture......Page 291
The Environment......Page 292
Network......Page 293
Hardware......Page 294
Case Study 1......Page 297
Case Study 2......Page 301
Case Study 3......Page 302
Case Study 4......Page 305
Case Study 5......Page 307
Case Study 6......Page 311
Case Study 7......Page 313
Case Study 8......Page 315
Summary......Page 317
CHAPTER 11 DATA ACCESS IN SERVICE-ORIENTED ARCHITECTURE (SOA) ENVIRONMENTS......Page 319
What Is Service-Oriented Architecture (SOA)?......Page 320
Involve Data Experts in Addition to SOA Experts......Page 322
Decouple Data Access from Business Logic......Page 323
Consider Data Integration......Page 325
Summary......Page 327
B......Page 329
C......Page 330
D......Page 331
H–I......Page 332
L......Page 333
P–Q......Page 334
R......Page 335
S......Page 336
T......Page 337
W–Z......Page 338
A......Page 339
B......Page 340
C......Page 341
D......Page 342
E......Page 344
I - J......Page 345
M......Page 346
O......Page 347
P......Page 348
Q - R......Page 350
S......Page 351
T......Page 352
W - X - Y - Z......Page 353