Memory Issues - Access Query, Based on queries, based on queries

[xcelguy]

I'm nearly done with the backend dashboard I am working on developing. The problem is I am running into a problem with the last (bottom) level of the the dashboard.

Gen 5 Database Structure.jpg

Basically the dashboard looks at a bunch of raw data (close to 4 million rows) and uses other tables to do two things:
1) convert a nonplottable financial period into a plottable calendar period.
2) To aggregate at the smallest and next to smallest grain (top level green).

The results of these two grains are then combined for each window in order to create indices and measure comparative performance (2nd level green).

I am now working on combining the results of the 2nd level of green blocks in order to create comparisons between time periods in order to look for products that are trending in the wrong direction. However when I try to combine the results of the three queries (2nd level) that comprise this one query (3rd level) Access has a problem. Specifically "The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2GB), or there is not enough temporary storage space on the disk to store the query result".

Since I have 104 GB of free memory the latter option is probably not the case. This would mean that Access is saying there is not enough storage space inside the database to store the result. My understand was that queries do not substantially increase the size of the database as I was able to cut hundreds of MB by removing tables and keeping the format in a query.

So my question is this: "Does access need to have enough memory to store the datasets from all the queries that feed the ultimate 3rd level query"? My database is sitting at 1.5 GB at the moment. When the 2nd level queries were tables instead it was sitting at around 1.85 GB.

What is the ideal solution to this problem? Is there one?



Please advise,

[AlexHedley]

Would it not be worth using SQL Server if it's getting up to those sizes?

SQL Server - Compact
http://www.microsoft.com/en-us/sqlse...s/compact.aspx

There is a 2GB limit per Access db.

[xcelguy]

I am a bit constrained by the tools that I am allowed to use for work. I am looking for a database to store data in that can be tapped and preloaded by excel.

My current excel file is driven by pivots driven by queries and access tables. I preload the data in excel before I send out the excel file (~250Meg). Would SQL Server allow that same functionality?

[ssanfu]

My database is sitting at 1.5 GB at the moment. When the 2nd level queries were tables instead it was sitting at around 1.85 GB.

Is the dB split into a FE and BE?
As Alex said, Access has a file size of 2GB. But that is per file. If you split the dB, the FE has a max size of 2GB and the BE has a file size of 2GB.
You could also have multiple BEs linked to the FE. If you had 3 BEs each with 1 table, each of the BEs has a max file size of 2 GB. In effect, you would have a 6 GB BE.

-----------------------
Or there is: MS SQL Server Express
MS SQL Server Express is free and has a max size of 10 GB .
SQL Express will run on your workstation; Excel should be able to connect to SQL Express as will Access.

[xcelguy]

There is 1 primary back and 2 other backends that maintain additional data that feed into the primary back end (at the 2nd level) and for certain views feed directly into the front end.

I will take a look at SQL server express but multiple back ends may solve this issue if i turn the queries into tables in a different BE DB.

[xcelguy]

I took a look at SQL Server Express and it looks like it is memory capped at 1GB of memory. http://msdn.microsoft.com/en-us/libr...=SQL.110).aspx

From my experience in excel this is well below the amount of memory needed to manipulate the large dataset that I am working with. Does SQL server do a better job of handling the memory so that it could handle large datasets more slowly? If I pursue this path I do not want to end up with a program that is capable of storing but not analyzing the data as is needed.

[ssanfu]

Maybe I don't understand.
Currently you have an Access FE with 3 Access BEs with each Be having a 2 GB limit.

If you have An Access FE with a SQL Express BE with a limit of 10 GB, how is that different?

The processing/queries in done in the Access FE. The BE(s) just have tables.



I have been googling "Maximum memory utilized" for SQL Server Express......apparently I have a LOT more reading/research to do before moving to SQL Server.

I found "Each instance can use up to 1GB of memory for the buffer pool". Now to find out what and why....