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

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 '20 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 '20 at 21:38
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  */
1
  • Thanks! This helps a lot, I manage to optimize the times – Sihaya0627 Jan 8 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.