Temp tables...who needs them?

[NauticalGent]

That old saying "if your only tool is a hammer..." became relevant to me today.

In the past if I had to pull data from more than one table, my first and only solution was to use temp tables and populate them. If it was a recurring requirement, the old data had to either be updated, new data appended and so one and so forth.

Not a big deal, easily done with macros and/or some VBA, but it seemed inefficient and I could not believe there wasn't a better way. Today I was faced with a similar requirement and the task was daunting and would have meant duplicating objects which I HATE to do. For some reason an untidy navigation pane is something I lose sleep over even though I am the only one who sees it.

My predecessor had designed a few Union queries that had not been used in years. When I got things down to a dull roar, I was going to look into what they did and delete them if I could not find any use for them.

When I realized what they do I felt both elevated and stupid at the same time. All the time I have wasted in the past with temp tables, forms and reports and the queries to populate them...jeez.

Never too late to learn I reckon!

 

[sneuberg]

Yeah I've been there. What should be call these? Ambivalent epiphanies? This has happen a few times because of contributions of members on this forum. This simultaneous feeling of enlightenment and stupidity makes me want to hate the people that caused the condition. It's a nasty emotional state.

 

[The_Doc_Man]

A temp table is something I used all the time as a staging mechanism because I had to build various things like bulk updates to audit tables or bulk changes involving a complex JOIN that to be technically precise had to be a four-way hook-up. I hated them but given that I had to do my damnedest to assure atomicity of updates, it was "build the new records" then "do one humongous update query" - and then finally "erase it all and start all over again."

Temp tables were necessary for the free-for-all that I was running on a pure Access FE/BE. (Wouldn't have done it with an active SQL BE, but didn't have that at the time.)

Over the period between Ac97 (yes, you read that right) and Ac2013, I had so many epiphanies that I even learned to pronounce epiphany correctly.

 

[NauticalGent]

I have had an epiphany, or was it a girl named Tiffany...?

Either way, Doc, you're right there are times when temp objects are needed and the best COA. However, the majority of time that I have used them, a simple Union Query would have done the trick had I just taken the time to learn about them.

Makes me wonder what other methods I am doing the hard way when I could let Access do the heavy lifting?

 

[Minty]

Bizarrely I was the other way around - union queries that could take a long time to run.

I now use local temp tables in access to store complicated SQL stored procedure results for local digestion.

I can't make them available as a view as they are parameter based for the local user, and take too long to run if made to return a full dataset to be filtered out by local criteria.

 

[sxschech]

I usually end up using temp tables when the query takes too long to run (such as during testing and modification phases) or in the case of needing to update some data and due to the type of joins becomes non updateable.

 

[NauticalGent]

Minty, you are quite right. It is very curious to me though, when I run the query via the navigation pane, it seems to run instantaneously. When I open the form that uses it as a source however, it takes about a minute to load.

The tables are Sharepoint lists and one of them has over 30k records, but the performance hit still seems out of the ordinary.

Beginning to think it might not be so great a find...

 

[Galaxiom]

I now use local temp tables in access to store complicated SQL stored procedure results for local digestion.

I can't make them available as a view as they are parameter based for the local user, and take too long to run if made to return a full dataset to be filtered out by local criteria.

Querying would certainly be slow if you join to local tables or use custom functions because the View results would have to be downloaded. But if all the tables are linked from the server and the criteria are simple scalar values then ACE normally sends the query back to the server for processing returning just the selected results.

An alternative to views where parameters are essential is a pass through query to Exec a Stored Procedure or return results of a table valued function. The query itself can't accept parameters but the SQL property can be edited to change the stored procedure parameters.

 

[Galaxiom]

It is very curious to me though, when I run the query via the navigation pane, it seems to run instantaneously. When I open the form that uses it as a source however, it takes about a minute to load...

A query without ordering will begin displaying results as soon as the first page is available. A form with sorting has to wait for all the results before it can display anything.

 

[NauticalGent]

Makes sense, thanks Galaxiom. You just convinced me to stay the course and add a progress meter to keep the users occupied.