20 million records: Too many for an MS Access app?

[PeteCresswell]

This is pursuant to another thread I tried to start, but can't find on
my server.

I've got to store rolling rates of return for investment funds -
calendar year, quarterly, and monthly

To cut to the chase, with a normal data structure, my estimates are
coming out around 20 million recs max.

They'd be indexed on FundID, BeginDate, EndDate, and ReturnType
(Monthly or Quarterly).

Seems to me like I read somewhere that when indexing is used, the
number of records isn't all that big a performance factor.

Can anybody agree or disagree with this?

My alternative is a Pivot Table strategy

For annual returns, we'd have a record for Fund/Year" and 9
RateOfReturn columns - one for each of the rolling periods we need:

- This year and last year
- This year and the previous 2 years
- This year and the previous 3 years
- This year and the previous 4 years
- This year and the previous 5 years
- This year and the previous 6 years
- This year and the previous 7 years
- This year and the previous 8 years
- This year and the previous 9 years
For quarterlies, we'd have a record for each Fund/Year/Quarter and 10
rate of return columns:

- Current quarter back 4 quarters
- Current quarter back 8 quarters
- Current quarter back 12 quarters
- Current quarter back 16 quarters
- Current quarter back 20 quarters
- Current quarter back 24 quarters
- Current quarter back 28 quarters
- Current quarter back 32 quarters
- Current quarter back 36 quarters
- Current quarter back 40 quarters
For monthlies, we'd have a record for each Fund/Year/Month and 8
columns:

- Previous 3 months
- Previous 6 months
- YTD
- Previous 12 months
- Previous 24 months
- Previous 36 months
- Previous 48 months
- Previous 60 months

This would cut the number of records down by a factor of 8-10, giving
us more like 2 million instead of 20 million records.

But would it be worth the loss in flexability/aesthetics?

[Tom van Stiphout]

On Wed, 16 Feb 2005 14:27:20 -0500, PeteCresswell <x@y.z> wrote:

It is true: indexing essentially makes the performance independent of
record count for some kinds of queries. For example selecting one
record out of 2E4 records or out of 2E7 records takes about the same
amount of time: way less than a second.
But this is not true for all queries, e.g. those that require a
tablescan.
Sounds like you are working on a serious project. I would spend some
time on a mockup of the database, filled with 2E7 fake records. It
takes little time to write some code to fill a table with
pseudo-random records mimicking the records that will actually happen.
For a similar situation I wrote functions like GetRandomNumber(intLow,
intHigh) and GetRandomText(intLength). It probably needs to run
overnight to fill with 2E7 records.
There is nothing like running some performance stats on the real
network using the real database (or as real as it gets).

When designing a db, I always gravitate to 3rd normal form first, and
will only deviate from that once I prove to myself that this structure
is time-prohibitive. I have made the mistake in the past to guess
that some query would be too slow and thus a denormalized design was
in order. I'm not making that mistake anymore. I let performance speak
for itself.

Another rule of thumb: every factor of 1000 more records, all rules
change. So if you know how to deal with thousands of records, you know
nothing about dealing with millions. If you know millions, nothing
about billions. Anecdote: I once exported 35E7 records to text using
BCP, and ran a custom multithreaded C application on it to manipulate
the data on a 4-way processor machine, and then BCP'ed it back into
the table. That was faster than having SQL Server 4.2 (?) do it.

-Tom.

This is pursuant to another thread I tried to start, but can't find on
my server.

I've got to store rolling rates of return for investment funds -
calendar year, quarterly, and monthly

To cut to the chase, with a normal data structure, my estimates are
coming out around 20 million recs max.

They'd be indexed on FundID, BeginDate, EndDate, and ReturnType
(Monthly or Quarterly).

Seems to me like I read somewhere that when indexing is used, the
number of records isn't all that big a performance factor.

Can anybody agree or disagree with this?

My alternative is a Pivot Table strategy

For annual returns, we'd have a record for Fund/Year" and 9
RateOfReturn columns - one for each of the rolling periods we need:

- This year and last year
- This year and the previous 2 years
- This year and the previous 3 years
- This year and the previous 4 years
- This year and the previous 5 years
- This year and the previous 6 years
- This year and the previous 7 years
- This year and the previous 8 years
- This year and the previous 9 years
For quarterlies, we'd have a record for each Fund/Year/Quarter and 10
rate of return columns:

- Current quarter back 4 quarters
- Current quarter back 8 quarters
- Current quarter back 12 quarters
- Current quarter back 16 quarters
- Current quarter back 20 quarters
- Current quarter back 24 quarters
- Current quarter back 28 quarters
- Current quarter back 32 quarters
- Current quarter back 36 quarters
- Current quarter back 40 quarters
For monthlies, we'd have a record for each Fund/Year/Month and 8
columns:

- Previous 3 months
- Previous 6 months
- YTD
- Previous 12 months
- Previous 24 months
- Previous 36 months
- Previous 48 months
- Previous 60 months

This would cut the number of records down by a factor of 8-10, giving
us more like 2 million instead of 20 million records.

But would it be worth the loss in flexability/aesthetics?

[(Pete Cresswell)]

Per Tom van Stiphout:

Sounds like you are working on a serious project. Very serious. New client. Beeeeeg client. If I can make these guys happy,
I'm "in"....if not....well, windsurfing season is just around th corner...
I would spend some
time on a mockup of the database, filled with 2E7 fake records.
That was my initial feeling. Now that you've said it, I guess I'll get on with
it.

When designing a db, I always gravitate to 3rd normal form first, and
will only deviate from that once I prove to myself that this structure
is time-prohibitive. I have made the mistake in the past to guess
that some query would be too slow and thus a denormalized design was
in order. I'm not making that mistake anymore. I let performance speak
for itself.

Thanks for the insights....
--
PeteCresswell