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

Broad Log Data Retrieval Issue

Avatar

Avatar
Affirm 5
Level 3
adithyacs86
Level 3

Likes

22 likes

Total Posts

110 posts

Correct Reply

5 solutions
Top badges earned
Affirm 5
Validate 10
Validate 1
Give Back 5
Give Back 3
View profile

Avatar
Affirm 5
Level 3
adithyacs86
Level 3

Likes

22 likes

Total Posts

110 posts

Correct Reply

5 solutions
Top badges earned
Affirm 5
Validate 10
Validate 1
Give Back 5
Give Back 3
View profile
adithyacs86
Level 3

21-12-2020

Hi Team, 

 

When we try to run any query against broadlog RCP table, we always see that the data retrieval is very slow, for example :- 

adithyacs86_0-1608546243117.png

 

above is the only query to read the data from the broadlogRCP table, which takes long hours to get the data and sometimes even times out. 

 

We also have checked if there are any parallel jobs running on the same table or any other through the query monitoring, but did not see any except this workflow. 

Since it is Adobe Hosted environment we are not able to see the logs properly. 

 

I was just wondering should this table be optimized or analysed before we start the run the query activity. 

 

Thanks,
Adithya

Adobe Campaign Classic
Llamour

Hi Adita,
Could you use text to describe your query instead of a screenshot?
I see 2 kind of queries and one is not relevant:
1- Primary key is not empty and Delivery Channel is Email and Address does not contain ',' and status is sent
2- Primary key is not empty and Delivery Channel is Email and Address does not contain '.' and status is sent

Only second is correct but has poor performances:
1- you'll never find a sent email with a ',' in its address as this is a forbidden caracter in email address... So you don't need to use a "does not contain" operator that is really consuming DB ressources on a such table
2- if the character to search is a ".", then the result is 0 as you'll always have this caracter in any email address

Anyway, broadlogRcp table is usually the table with the greater amounts of records in your database. Query it carefully and if performance are not relevant according to your needs, you should perhaps reduce its Purge retention delay in the deployment wizzard

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Boost 250
MVP
DavidKangni
MVP

Likes

255 likes

Total Posts

372 posts

Correct Reply

150 solutions
Top badges earned
Boost 250
Validate 1
Give Back 5
Give Back 3
Give Back 25
View profile

Avatar
Boost 250
MVP
DavidKangni
MVP

Likes

255 likes

Total Posts

372 posts

Correct Reply

150 solutions
Top badges earned
Boost 250
Validate 1
Give Back 5
Give Back 3
Give Back 25
View profile
DavidKangni
MVP

21-12-2020

Hi Aditya,

 

I will just query on channel equal to Email and status equal Sent.

Reason:

  • primary key can not be null
  • if email address contains , it will return a bounce with status failed

If your query is a frequent query and is taking long add db index on channel and status fields in the broadlog rcp schema.

<dbindex name="status">
<keyfield xpath="@status"/>
</dbindex>

Thanks,

David

 

Answers (2)

Answers (2)

Avatar

Avatar
Affirm 5
Level 3
adithyacs86
Level 3

Likes

22 likes

Total Posts

110 posts

Correct Reply

5 solutions
Top badges earned
Affirm 5
Validate 10
Validate 1
Give Back 5
Give Back 3
View profile

Avatar
Affirm 5
Level 3
adithyacs86
Level 3

Likes

22 likes

Total Posts

110 posts

Correct Reply

5 solutions
Top badges earned
Affirm 5
Validate 10
Validate 1
Give Back 5
Give Back 3
View profile
adithyacs86
Level 3

21-12-2020

@_Manoj_Kumar , 

 

I tried the way you mentioned but the result is still the same, the workflow is still running now. 

 

Avatar

Avatar
Validate 1
MVP
_Manoj_Kumar
MVP

Likes

111 likes

Total Posts

310 posts

Correct Reply

97 solutions
Top badges earned
Validate 1
Contributor
Ignite 1
Shape 1
Give Back 5
View profile

Avatar
Validate 1
MVP
_Manoj_Kumar
MVP

Likes

111 likes

Total Posts

310 posts

Correct Reply

97 solutions
Top badges earned
Validate 1
Contributor
Ignite 1
Shape 1
Give Back 5
View profile
_Manoj_Kumar
MVP

21-12-2020

Hello @adithyacs86 ,

 

There are two conditions which I think are not required.

 

channel fo delivery equal to Email

and status equal to sent

 

Use these two conditions only and you will have the same result and will surely take less time to execute.