0

I'm facing an intermittent issue with one automation. A month ago I received a message from SFMC reporting a timeout error in one Automation, but then I re-run the automation the error doesn't show up...until yesterday.

I have three queries in the Automation, one export and one import activity. All the queries follow the this structure:

SELECT 
Sub_ID
,Field1
,Field2
,Field3
,Field4
,Field5
,Field6
,Field7
,Field8
,NEWID() AS PrimaryKey
,Field9
,Fielda
,date_1
,date_2
,date_3
,Fieldb
,Fieldc
,date_enter
,date_4
,date_5
FROM [Data_extension]

*I changed the names of the fields but that is the full structure. The amount of data is bigger than 50 million so I believe this needs to be optimized.

Any suggestion? Thanks a lot for the help

Improve this question
2
  • If this is your sql without any where clauses or join, I'm unsure why you are populating another DE. in addition there's nothing to optimize here
    – EazyE
    Dec 30, 2020 at 20:25
  • This is a process made by another team in the past, but that the flow they defined and I was afraid of the same, that there is anything we can do to optimize this. But maybe if we segment the info we can get rid of this timeout? And thanks for the help!
    – Sihaya0627
    Dec 30, 2020 at 21:38

1 Answer 1

Reset to default
3

I've previously outlined my go-to for timeout mitigation here.

You can use the hidden _customObjectKey column in every DE and the % mod operator to split the processing of your query into multiple queries. For your query, you could split it into 3 queries like this:

Query 1

SELECT 
  Sub_ID
, Field1
, Field2
, Field3
, Field4
, Field5
, Field6
, Field7
, Field8
, NEWID() AS PrimaryKey
, Field9
, Fielda
, date_1
, date_2
, date_3
, Fieldb
, Fieldc
, date_enter
, date_4
, date_5
FROM [Data_extension]
where _customObjectKey % 3 = 0
/* name: Data_Extension_1 */
/* target: Target_Data_Extension */
/* action: overwrite  */

Query 2

SELECT 
  Sub_ID
, Field1
, Field2
, Field3
, Field4
, Field5
, Field6
, Field7
, Field8
, NEWID() AS PrimaryKey
, Field9
, Fielda
, date_1
, date_2
, date_3
, Fieldb
, Fieldc
, date_enter
, date_4
, date_5
FROM [Data_extension]
where _customObjectKey % 3 = 1
/* name: Data_Extension_2 */
/* target: Target_Data_Extension */
/* action: append/update  */

Query 3

SELECT 
  Sub_ID
, Field1
, Field2
, Field3
, Field4
, Field5
, Field6
, Field7
, Field8
, NEWID() AS PrimaryKey
, Field9
, Fielda
, date_1
, date_2
, date_3
, Fieldb
, Fieldc
, date_enter
, date_4
, date_5
FROM [Data_extension]
where _customObjectKey % 3 = 2
/* name: Data_Extension_3 */
/* target: Target_Data_Extension */
/* action: append/update  */
Improve this answer
1
  • Thanks! This helps a lot, I manage to optimize the times
    – Sihaya0627
    Jan 8, 2021 at 22:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .