Re: how to improve perf of 131MM row table?

On Wed, Jun 25, 2014 at 2:40 PM, Aaron Weber <aweber(at)comcast(dot)net> wrote:
> I will gather the other data tonight. Thank you.
>
> In the meantime, I guess I wasn't clear about some other particulars
> The query's where clause is only an "IN", with a list of id's (those I
> mentioned are the PK), and the join is explicitly on the PK (so, indexed).

The PK of the master table and the PK of the detail table cannot be
the same thing, or they would not have a master-detail relationship.
One side has to be an FK, not a PK.

>
> An IN with 50 int values took 23sec to return (by way of example).

If that is 50 PKs from the master table, it would be about 1000 on the
detail table. If you have 5600 rpm drives and every detail row
requires one index leaf page and one table page to be read from disk,
then 23 seconds is right on the nose. Although they shouldn't require
a different leaf page each because all entries for the same master row
should be adjacent in the index, so that does sound a little high if
this is the only thing going on.

Cheers,

Jeff