Test the Impact of Indexes on Your Load Times
Every insert, update, or delete of an indexed column may be slower than the same transaction
against an unindexed table. There are some exceptions??”sorted data has much less of an impact,
for example??”but the rule is generally true. The impact is dependent on your operating
environment, the data structures involved, and the degree to which the data is sorted.
How many rows per second can you insert in your environment? Perform a series of simple
tests. Create a table with no indexes and insert a large number of rows into it. Repeat the tests to
reduce the impact of physical reads on the timing results. Calculate the number of rows inserted
per second. In most environments you can insert tens of thousands of rows per second into the
database. Perform the same test in your other database environments so you can identify any that
are significantly different from the others.
Now consider your application. Are you able to insert rows into your tables via your application
at anywhere near the rate you just calculated? Many applications run at less than 5 percent of the
rate the environment will support. They are bogged down by unneeded indexes or the type of
code design issues described earlier in this chapter. If your application??™s load rate decreases??”say,
from 40 rows per second to 20 rows per second??”your tuning focus should not be solely on how
that decrease occurred but also on how the application managed to get only 40 rows per second
inserted in an environment that supports thousands of rows inserted per second.
Pages:
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244