Handling large temporary tables

[foxtrot123]

I have a database split into a frontend and backend.

The front end has several make-table queries that create tables of data on which pivot tables and other analyses queries are based. The make-table queries get re-run every now and then as new data get entered.

The resulting tables are huge - 500K records in some cases. It seems like a bad idea to store them in either the front and back end.

Should I create a second "backend" that holds just these temporary tables? If so, what's the best way to design the make-table queries so they make the table in this "backend"?

Thanks for any pointers.

 

[plog]

If so, what's the best way to design the make-table queries so they make the table in this "backend"?

The best design is on that doesn't make tables. Why does this need to be done? Whats wrong with SELECT queries?

 

[foxtrot123]

Solely for performance reasons. The queries that make the table include several calculated variables and sub-queries that make the query very slow, especially when I'm dealing with thousands of records. Consequently, any filtering done on the query (e.g., via pivot table) takes forever to process.

 

[Galaxiom]

You are correct. Alhough many developers insist on the abhorant practice of writing to the Front End, temporary data should not be written to either the Front End or the Back End.

I call what you are referring to a "Side End". Search the forum for that term to see previous discussions on the subject.

Put it on the local machine in the users' profile under temp. You can get this with the expression Environ("temp").

In Windows7 it is in the users' AppData\Local subfolder. I use a separate folder in there.