Re: Temp table or normal table for performance?

Peter Hunsberger wrote:
> On Wed, Aug 19, 2009 at 2:03 AM, Stephen Cook<sclists(at)gmail(dot)com> wrote:
>> Let's say I have a function that needs to collect some data from various
>> tables and process and sort them to be returned to the user.
>>
>> In general, would it be better to create a temporary table in that function,
>> do the work and sorting there, and return it... or keep a permanent table
>> for pretty much the same thing, but add a "user session" field and return
>> the relevant rows from that and then delete them?
>>
>> Sorry this is vague, I know it most likely depends on the workload and such,
>> but I'm just putting this together now. I could go either way, and also
>> switch it up in the future if necessary. Is there a rule of thumb on this
>> one? I'm a bit biased against temporary tables, but then again if the
>> normal table gets a lot of action it might not be the optimal choice.
>>
>
> This completely depends on the specifics, there's no way anyone can
> give you a general answer for this kind of problem. However, why do
> you think you will need a temp or permanent table? Why can't you just
> use your function to compute the answers at the time the user needs
> the data?
>

I figured that would be the response I'd get :)

I've decided on some type of table storage because basically I'm
combining information from several different tables (some of which need
to recursively get other rows) and massaging it and sorting it in ways
far too convoluted to use a single query with UNION and ORDER BY, and
then returning the results.