Forcing Access to join tables in RAM as opposed to HDD

[tdion]

Hello.

I have a huge SQL statement that joins 13 tables together. This statement is part of a query builder that I wrote to search a 3rd normalized schema.

ANYWAY...

When it executes, I get a "[Microsoft][Access Driver]Not enough temporary drive space." error.

I have 2 Gigs of RAM in my server, and my database is only 8 megabytes big. Is there a way to have all table join processing done in memory instead of to the hard drive? I think this may be a MaxBufferSize issue in the registry, but I would like to get away from using the harddrive for processing as much as possible. Thanks.

 

[willir]

tdion

Can you use the RAM on the server - not really. The server manages it's RAM, and not the end user.

Unless you are running a stroed procedure or ADP, what happens when you run a query against the server is that all data is copied to your local computer. If you are short on space - kabam!

This may help
On your short cut for Access (or create a new short cut), in the properties, have the "Start in" field point to a location where you have more disk space room.

Also, some routine PC therapy may be in order - clean out Temp files, Temp Internet (including off-line). If you have not done this for a while, then you may obtain huge space savings.

By the way, the fastest way I know of to fill a Temp file system is to exit ungracefully when performing a large data task such as running a large report, or editing a large spreadsheet - since the system crashed, the application will not have deleted any temp files it created.

Richard

 

[tdion]

If what you say is true, then Access is forced to write a temporary file every time a join is created.

Why can't I turn my 2 gigs of RAM into a RAMDrive? Call it h: or something. Is there any way to force the temporary files created in Access to be written to a specific drive? If so, I will point Access to my RAMDrive and my queries should run super quick.

 

[willir]

You can create a RAM drive. This is done at boot up. But then you have to allocate the RAM on your local workstation for the RAM drive - not the server RAM. Here is the rub.

The operating system on the server manages the it's own memory. As an end user, you can run tasks which will use resources on the server, but you can not, for example, tell it to assign 2 GB of RAM drive for your use and end user.

(I am not sure if this is a good analogy, but pretend you moved to a new address and you have advised the local post office company of your new address. You can not then go and tell the post office that they need to make one or two of their trucks at your disposal so that any mail, newspaper and junk mail delivered to your old address is picked up and re-delivered to your new address. They will not allocate these resources to your needs. They will however flag your old mailing address as moved, and redirect your mail at soruce to the new address. You tell the post office what you want for the outcome, but you can not tell the post office how to achieve the outcome.)

But I dont think a RAM drive will resolve your issue. Do you have 2 GB of memory on your local desktop? And when you have xxx MB of RAM, you need about the same amount of free space on your local desktop for the workstation to function properly.

Have you considered making the working directory / start in a folder on the server? For example, the database is located on F:\DB\YourDB. Have the "Start in" directory use the same location. This may help.

And if I was the desktop working your case, the first thing I would check is to see how much free space you have on your local hard drive.

One last thing. When performing queries, sometimes by accident or by design, a query that joins two tables results in a multiplication of the two tables, and filters out the requried results. 100 records x 100 recrds = 10,000 rows -> filter -> 400 rows. In this case, you need to have room to accommodate the 10,000 rows. This example does not happen all the time, but per theory, it does happen for some types of joins.

The other problem is if you have an incoorect join that gives you 10,000 records.

You might want to break down your query to ensure your joins are okay.