Aside from their impact on data load times, many of the indexes may never be
needed to support queries. In OLTP applications, you should not use bitmap indexes; if a column
has few distinct values, you should consider leaving it unindexed. The optimizer supports ???skipscan???
index accesses, so it may choose an index on a set of columns even if the leading column
of the index is not a limiting condition for the query.
Do It As Simply As Possible
Once you have eliminated the performance costs of unnecessary logical reads, unneeded database
trips, unmanaged connections, and inappropriate indexes, take a look at the commands that remain.
Go Atomic
You can use SQL to combine many steps into one large query. In some cases, this may benefit
your application??”you can create stored procedures and reuse the code and thus reduce the
number of database trips performed. However, you can take this too far, creating large queries
that fail to complete quickly enough. These queries commonly include multiple sets of grouping
operations, inline views, and complex multirow calculations against millions of rows.
If you are performing batch operations, you may be able to break such a query into its atomic
components, creating temporary tables to store the data from each step. If you have an operation
that takes hours to complete, you almost always can find a way to break it into smaller
component parts. Divide and conquer the performance problem.
Pages:
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233