What happens to temporary table on another query

Miksu

08-05-2020

I'm curious what happens to the temporary table of a query, when there is another query in the pipe:

 

I have a query (Q1) that has its results in a temporary table (T1).

The flow then continues to another query (Q2) that is NOT quering the T1 or even same dimension as T1, it's a completely new, separate query, that just is in the same flow.

It will have its results in a new Temporary table (T2).

 

But what exactly happens to T1? 

Is it dropped?

Does it stay in memory until the whole WF is stopped?

Something else?

 

I did test this, by storing the name of T1 to a variable, and then after the second query (Q2), tried to do a sql-query on T1.

var count = sqlGetInt("SELECT COUNT(*) from "+vars.tempName+"");
logInfo(count);

 

This crashed with an error: PGS-220000 PostgreSQL error: ERROR:  relation "wkf1793215150_24_1" does not exist LINE 1: SELECT COUNT(*) from wkf1793215150_24_1 LIMIT 1 OFFSET 0   

 

So, from this, I assume that the T1 was dropped after the Q2. But I would like to have a confirmation that it's not still in anyway stored in memory or anything, and using resources.

 

*****

Background:

The reason for all this is, that we have workflows that are build so that there are two queries which then are excluded from each other. And in order to not have both of the queries running at the same time, the flow is built so there is Q1, which is then forked and one branch of the fork is going to the Q2 and the second is going to the exclusion, which also is receiving the result branch of Q2... 

And since fork is duplicating the tempTable of Q1, I was worried, that this is not actually very good practice as the duplicated tempTable is unnecessary (The fork is just to trigger the second query, the data is not used). 

 

Personally I would rather use signals... but feel free to comment the above and let me know if what you think would be the "best practice":

 

- Miksu -

 

ACC Temporary tables

Accepted Solutions (1)

Accepted Solutions (1)

MarcelSzimonisz

MVP

08-05-2020

Hello @Miksu

Both queries create temporary table which is dropped when the next activity is executed. If you need all the tables you will need to check the option in workflow's properties "Keep the result of interim population between two executions"

 

But be advised to use it wisely as when you run out of database space no campaign will run 🙂 

 

image.png

Answers (1)

Answers (1)

Jyoti_Y

MVP

08-05-2020

Hi,

Temp Table of Query Q1 will be dropped as soon as you start quering on different context, i.e Query Q2. Your all data coming previously will no longer be available. This so happens because while quering you chose to select some other Targeting Dimension, instead of Temp Table.

 

Thanks,

Jyoti.