Re: Adding Indexes drastically slows down App

"E Elliott" <emelliott_99_at_yahoo.com> wrote in message news:9c4485cf.0106201328.2087f7e7_at_posting.google.com...
> Well...shame on me for not running an explain plan! We ran one on the
> query with and with out the indexes and found that the query plan was
> drastically different.
>
> Upon further investigation, I found that even though we were
> re-running stats after the new indexes were built, we were using
> estimate with no sample size. Well, I found out this only samples the
> first 1064 rows. Definitely not a good sample. I reran the stats
> with a 20% sample size(as suggested in one blurb I was reading) and
> this once again changed the query plan. Its still not as good as the
> original, but it was better. I added a hint to use the index it was
> using when it was fast...but it ignored it. Any ideas why it might do
> that?
>
> Currently, I am rerunning the stats as compute to see if that makes a
> difference. But, on our ~19 million row table with lots of indexes,
> its taking a while to complete.
>
> Thanks for all the hints and ideas. I'd still appreciate more esp
> about why it might ignore my hint.

Well, it's a hint, not a command. And the optimiser is a law unto itself, even on sunny Wednesdays, with a following wind, fingers crossed and copious quantities of Irish stout and shamrock juice.

I'd love to see the explain plans! You must have some wildly skewed data in there for two pathetic indexes to make such a wild difference... and for an estimate statistics to be hopeless, too.

Regards
HJR
>
> Thanks,
>
> Elizabeth
>
>
>
> emelliott_99_at_yahoo.com (E Elliott) wrote in message
 news:<9c4485cf.0106200724.3a57e40e_at_posting.google.com>...
> > We are seeing drastic slowdowns in our system after adding 2 indexes
> > to the largest table in our DB.
> >
Received on Thu Jun 21 2001 - 04:51:54 CDT