Since I'm not part of any database community and most of you here deal with databases I'm seeking some advice. I need a database that will grow to hold 20,000,000 plus records within 8 years. How do you design such a database system that will not slow down as it grows?

I think any major DB will handle 20 mil rows

Today I found out that a simple query that does a sum aggregate takes on average about 700ms and in the first report I have to run this query 448 times (each with varying where clauses). To load in a webpage it took over 5 mins. I don't want a similar kind of report on the new database to take that long.

Adding to Brad’s comments: recognize, too, that databases are not a “fire-and-forget” thing. What works for initial small datasets can degrade badly as they grow and as the nature of the data in them evolves. Be ready to periodically review indexing and even initial design decisions to see if things need to change.

I recommend highly against running any query 450 times on the same page! How long the query takes will depend on how many rows being processed internally and the indexes. I would look for a way to get your data in one go and separate it on the display.

denormalization strategies? don't know what that is.

Then you need to talk to your DBA )

Having access to a good DBA and someone with experience trouble-shooting performance issues and helping optimize performance on the database side gets really important.

Don't have one.

Big learning opportunity, it seems. 🙂

ok well. 1 query with the subset of data and several cfloops to do the sums and aggregates we brought down the execution time to 22 seconds. From 5-6 minutes down to 22 seconds!!!

Would recommend. SQL sentry plan explorer (free) can give really good insights into your query

Thanks for that