>>> On Mon, Aug 13, 2007 at 4:00 PM, in message
<1806D1F73FCB7F439F2C842EE0627B18065F7A86(at)USA0300MS01(dot)na(dot)xerox(dot)net>, "Relyea,
Mike" <Mike(dot)Relyea(at)xerox(dot)com> wrote:
>
> Re-writing the view like this maybe bought me something.
> Tough to tell because I also increased some of the statistics.
I don't know whether it was the finer-grained statistics or the simplification,
but it bought you a new plan. I don't know if the seven second improvement
is real or within the run-to-run variation, though; it could be because you
happened to be better-cached at the time.
> From what Tom
> says, it sounds like if I want the data returned faster I'm likely to
> have to get beefier hardware.
That's not what he suggested. If you introduce redundancy in a controlled
fashion, you could have a single table with an index to more quickly get you
to the desired set of data. That can be maintained on an ongoing basis
(possibly using triggers) or could be materialized periodically or prior to
running a series of reports or queries.
Such redundancies violate the normalization rules which are generally used
in database design, but some denormalization is often needed for acceptable
performance.
-Kevin