Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

What happens to temporary table on another query

Avatar

Avatar
Validate 1
Level 1
Miksu
Level 1

Like

1 like

Total Posts

3 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile

Avatar
Validate 1
Level 1
Miksu
Level 1

Like

1 like

Total Posts

3 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile
Miksu
Level 1

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)

Avatar

Avatar
Validate 10
MVP
MarcelSzimonisz
MVP

Likes

102 likes

Total Posts

224 posts

Correct Reply

56 solutions
Top badges earned
Validate 10
Validate 1
Shape 1
Boost 50
Boost 5
View profile

Avatar
Validate 10
MVP
MarcelSzimonisz
MVP

Likes

102 likes

Total Posts

224 posts

Correct Reply

56 solutions
Top badges earned
Validate 10
Validate 1
Shape 1
Boost 50
Boost 5
View profile
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)

Avatar

Avatar
Shape 1
MVP
Jyoti_Y
MVP

Likes

87 likes

Total Posts

123 posts

Correct Reply

55 solutions
Top badges earned
Shape 1
Give Back 5
Give Back 3
Give Back 10
Give Back
View profile

Avatar
Shape 1
MVP
Jyoti_Y
MVP

Likes

87 likes

Total Posts

123 posts

Correct Reply

55 solutions
Top badges earned
Shape 1
Give Back 5
Give Back 3
Give Back 10
Give Back
View profile
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.