Posts: 5,094
Threads: 1,252
Joined: Sep 2012
Reputation:
0
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
Posts: 37,103
Threads: 2,599
Joined: May 2025
Reputation:
0
you have 2GB of straight data?
PLEASE move to a real database!
even MySQL!
Posts: 5,094
Threads: 1,252
Joined: Sep 2012
Reputation:
0
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
Posts: 4,930
Threads: 69
Joined: May 2025
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.
Posts: 5,094
Threads: 1,252
Joined: Sep 2012
Reputation:
0
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...
Posts: 4,930
Threads: 69
Joined: May 2025
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.
Posts: 37,103
Threads: 2,599
Joined: May 2025
Reputation:
0
>>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?
Posts: 5,094
Threads: 1,252
Joined: Sep 2012
Reputation:
0
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.
Posts: 5,094
Threads: 1,252
Joined: Sep 2012
Reputation:
0
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.
Posts: 37,103
Threads: 2,599
Joined: May 2025
Reputation:
0
>>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?
