By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,363 Members | 3,622 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,363 IT Pros & Developers. It's quick & easy.

table update with or without indexes enabled?? 390/V7

P: n/a
my colleagues are convinced that having more than an index or
two destroys performance on their batch runs. could be. i don't
have the data, and i'm wondering whether there might be some
publicly available. haven't found such.

we have a new requirement, which goes about like this.

single table access. in the jsp part, the users get to choose how
they want the data sorted. it's a 6 column access, and they get to
choose which column comes first, at query time. this argues for 6
indexes, with each column successively in position 1 of a compound
index, remaining columns in a fixed order.

so the question: is there either analytical or empirical evidence for
where the turnover point is between leaving the indexes in place for
the batch runs, and taking the performance hit on index maintenance
(index columns not likely to change, just insert penalty), as
opposed to disabling during the batch run, then re-enabling after the
run??

factors i assume relevant: number of indexes, number of columns/index,
number of rows inserted/changed, percent of table rows inserted/changed

i know, only really testing with our data, etc. but these guys don't
like to do that sort of thing.

thanks,
robert
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"robert" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...
my colleagues are convinced that having more than an index or
two destroys performance on their batch runs. could be. i don't
have the data, and i'm wondering whether there might be some
publicly available. haven't found such.

we have a new requirement, which goes about like this.

single table access. in the jsp part, the users get to choose how
they want the data sorted. it's a 6 column access, and they get to
choose which column comes first, at query time. this argues for 6
indexes, with each column successively in position 1 of a compound
index, remaining columns in a fixed order.

so the question: is there either analytical or empirical evidence for
where the turnover point is between leaving the indexes in place for
the batch runs, and taking the performance hit on index maintenance
(index columns not likely to change, just insert penalty), as
opposed to disabling during the batch run, then re-enabling after the
run??


Experimentation on your actual environment is best, since the turnover point
depends on your hardware, the schema, and the queries that users are running
(to get sorted order) and/or the queries run by the batch run.

Dropping and recreating the indexes before/after every batch run is not a
good idea -- as your tables get larger, the time and resources required to
rebuild the indexes will grow and eventually you'll reach the situation
where you need to start rebuilding the indexes as soon as you drop them.

Generally, though, for any performance-sensitive table, minimizing the
number of indexes is the proper course of action.

For the users running queries, all you need to do is alter the "order by"
clause on their query to sort by their desired column. Yes, this will cause
a sort to occur (instead of using the appropriate index to retrieve the rows
in sorted order), but you're only going to sort the result set (which
hopefully is a small subset of the entire table), and this will have a much
smaller performance effect as opposed to maintaining 6 indexes on a table.

--
Matt Emmerton
Nov 12 '05 #2

P: n/a
You haven't mentioned how the data is being filtered by the where
clause.
Its nearly impossible to make general statements about indexes without
knowing the specific design, data volumes and access pattern. However,
more often than not, an index on the filtered-columns would be more
important than an index on the sort columns.

Nov 12 '05 #3

P: n/a
"Darius" <da**********@gmail.com> wrote in message news:<11**********************@c13g2000cwb.googleg roups.com>...
You haven't mentioned how the data is being filtered by the where
clause.
Its nearly impossible to make general statements about indexes without
knowing the specific design, data volumes and access pattern. However,
more often than not, an index on the filtered-columns would be more
important than an index on the sort columns.


same set of columns. what it boils down to is whether the engine is
more efficient doing a partial re-build after the updates, than it
is in maintaining the indexes during inserts. in theory, i suppose,
one could imagine that the mass index update could be more efficient
than doing it one at a time. so total time the database is unavailable to
terminal users is less doing the batch in two parts.
Nov 12 '05 #4

P: n/a
Robert,

My post didn't make it clear, but what I was questioning was whether
you needed the 6 indexes in the first place. For instance, do you know
if the use of the index is really avoiding the sort. If so, do you know
if it is really faster than letting the sort take place as a separate
step? What is the typical numbr of rows returned?

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.