This particular database contains three tables.

The first is a combination of data imported from five CSV files, each representing a quarter-year. So, we're due for a new one, shortly.

The others are the results of two different make-table queries. These tables are used by Excel in two different PivotTables, which is much faster than drawing the information from the first, gigantic, table.

The 2GB limit is reached.

The workaround I've come up with (but don't like) is making those five CSV files into linked tables.

Now, the database is really small, but also really slow. I've forfeited the indexing that was made possible by my original table, so the daily 20-minute data crunching session is now a 2-hour daily data-crunching session.

I guess it's not really a workaround I'm seeking, as I've already got that; set it up to run overnight.

But is my only option for avoiding the 2GB wall to structure the database in such a way that I can no longer use many time-saving features?

Oh, and I can't use FileMaker. :-(

GtDS

---

---
Use Excel? Can I interest you in saving some time with Excel? Listifier.com!

you have 2GB of straight data?

PLEASE move to a real database!

even MySQL!

---

mattkime Wrote:
-------------------------------------------------------
> you have 2GB of straight data?
>
> PLEASE move to a real database!
>
> even MySQL!

That might be under the same category as trying to use FileMaker.

G

Have you optimized the data types for each field in the big table to reduce the storage requirements? I don't know much about Access but databases like MySQL let you choose what type of integer, string, and so on to constrain the storage size.

TheTominator Wrote:
-------------------------------------------------------
> Have you optimized the data types for each field
> in the big table to reduce the storage
> requirements? I don't know much about Access but
> databases like MySQL let you choose what type of
> integer, string, and so on to constrain the
> storage size.

Yup.

I think it's the indexes that are killing me. Am checking, now...

Another technique you can use is to create set of related tables out of a single table by identifying fields that can be excised from the big table and spun off into their own tables.

For example you might have a Notes field. Not every entry has a Note filled in. Notes are of varying lengths. If, on average, the data in the note (or Description) field is longer than an integer (considering storage requirements), then you can create a separate table of Notes with a NoteID field (or Descriptions with a DescriptionID field). Instead of the Note in the original table, make it a NoteID.

This makes the table more work to import but it reduces the net size of your table.

I am assuming that the 2GB size limit is for a table and not a database.



Edited 1 time(s). Last edit at 03/29/2006 09:34AM by TheTominator.

>>That might be under the same category as trying to use FileMaker.

I think you need to convince the client that they're using the wrong tool for the job - assuming the database is properly optimized.

What is taking up all the space?

---

mattkime Wrote:
-------------------------------------------------------
> >>That might be under the same category as
> trying to use FileMaker.
>
> I think you need to convince the client that
> they're using the wrong tool for the job -
> assuming the database is properly optimized.

Not feasible, in this particular situation.

> What is taking up all the space?

I believe it's the indexes.

TheTominator Wrote:
-------------------------------------------------------
> Another technique you can use is to create set of
> related tables out of a single table by
> identifying fields that can be excised from the
> big table and spun off into their own tables.

Agreed, all around.

> I am assuming that the 2GB size limit is for a
> table and not a database.

Nope; it's the limit for the .mdb file.

>>Not feasible, in this particular situation.

Then they want what they can't have.

$400 for a Mac Mini with MySQL on it.

How much is your time worth?

---

mattkime Wrote:
-------------------------------------------------------
> >>Not feasible, in this particular
> situation.
>
> Then they want what they can't have.
>
> $400 for a Mac Mini with MySQL on it.
>
> How much is your time worth?

You don't need to convince me there may be better software for this task.

What's the MySQL equivalent of doing a scripted VBA import in Access? For instance, I have a script that loops through every quarter from 2005_01 to current, and imports one file for each quarter. How would that by done with MySQL?

>>How would that by done with MySQL?

Command line with the mysql client.

OR

You can use MySQL as a data source in access

---

>>You don't need to convince me there may be better software for this task.

ahh....one of those clients

---

MS SQL Server 2005 Express edition has database limit of 4GB. This will double your available db space.

Maurycy