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

Updating the documentation with a good practice.

Avatar

Avatar
Give Back 50
Level 4
LaurentLam
Level 4

Likes

23 likes

Total Posts

64 posts

Correct reply

10 solutions
Top badges earned
Give Back 50
Affirm 10
Give Back 25
Give Back 10
Give Back 5
View profile

Avatar
Give Back 50
Level 4
LaurentLam
Level 4

Likes

23 likes

Total Posts

64 posts

Correct reply

10 solutions
Top badges earned
Give Back 50
Affirm 10
Give Back 25
Give Back 10
Give Back 5
View profile
LaurentLam
Level 4

13-06-2021

In the documentation, a bad practice for developper is unwantedly suggested:
https://experienceleague.adobe.com/docs/campaign-classic/using/configuring-campaign-classic/api/data...


To limit the number of records returned by the query to 100:

<queryDef schema="nms:recipient" operation="select" lineCount="100">
...
To retrieve the next 100 records, run the same query again, adding the startLine attribute.

<queryDef schema="nms:recipient" operation="select" lineCount="100" startLine="100">
...


In fact, a dev should never use the startLine attribute as it contribute to a loss of performance:

exemple with a SQL translation of the 1rst example:

select top 100 from nmsRecipient

You'll get the 1rst 100 records... No issue about that

 

Now is the SQL translation of the 2nd querydef:

select top 200 from nmsRecipient

=> as the startLine begin at 100, the "top" will start at 100 and add the line count attributes (100) ... Then the JS engine will keep the last 100 records

This doc is used by developper to create code in order to loop over all the records of a table as queryDef are limited to 10000 records usually:

Imagine a loop with a lineCount of 10K... If you want to use a queryDef in order to operate on a 100K table: it will creates 10 sql queries, retrieving each time 10k more results leading to retrieve 550K results instead of 100K and each requests to be more greedy on performance (10+20+30+40+50+60+70+80+90+100).

 

The good practice should be then to use an Id (directly the id of the schema or a rowId from a wkf work table if possible) in the where clause in order to replace this "startLine" attribute:

<queryDef schema="nms:recipient" operation="select" lineCount="100">
<where>
<condition expr="[@id] > " var.lastProcessedId />
</where>
</queryDef>
I observed in the past some workflow JS activity with the "startLine" implementation that were optimized from 4h down to 2 min.

 

Placing a warning in the documentation in order to inform that they should never use startIndex as a way to loop over a schema would be really helpfull, specially for beginners.

 

Ps: TY @CedricRey  for this tip months ago

5 Comments

Avatar

Avatar
Give Back 1000
Community Advisor
wodnicki
Community Advisor

Likes

989 likes

Total Posts

1,097 posts

Correct reply

527 solutions
Top badges earned
Give Back 1000
Give back 900
Give Back 800
Give Back 700
Give back 600
View profile

Avatar
Give Back 1000
Community Advisor
wodnicki
Community Advisor

Likes

989 likes

Total Posts

1,097 posts

Correct reply

527 solutions
Top badges earned
Give Back 1000
Give back 900
Give Back 800
Give Back 700
Give back 600
View profile
wodnicki
Community Advisor

13-06-2021

Hi,

 

The queryDef's translate into ordinary sql, js isn't used (outside interaction which doesn't have startLine attrib).

For most* rdbms' this would add 'limit n offset m' along with whatever optimizations available, such as 'top' hint for ms sql server.

The reason your queries are running faster when you add indexed conditions is they aren't scanning the full table for results.

 

Thanks,

-Jon

 

*Most since mysql uses diff syntax but is long deprecated as an acc install option. I think db2 is also weird but also deprecated now?

Avatar

Avatar
Give Back 50
Level 4
LaurentLam
Level 4

Likes

23 likes

Total Posts

64 posts

Correct reply

10 solutions
Top badges earned
Give Back 50
Affirm 10
Give Back 25
Give Back 10
Give Back 5
View profile

Avatar
Give Back 50
Level 4
LaurentLam
Level 4

Likes

23 likes

Total Posts

64 posts

Correct reply

10 solutions
Top badges earned
Give Back 50
Affirm 10
Give Back 25
Give Back 10
Give Back 5
View profile
LaurentLam
Level 4

14-06-2021

Hi @wodnicki 

Didn't know for this syntax on most RDBMS.

But carefull: there is no index on WKF worktables, even PK, (checked on PostGres & msSQL RDBMS). Reason is that AC assume that there is no other columns than the PK columns and having an index on a single column table doesn't help

Avatar

Avatar
Shape 1
Level 5
CedricRey
Level 5

Likes

41 likes

Total Posts

78 posts

Correct reply

21 solutions
Top badges earned
Shape 1
Boost 25
Applaud 5
Affirm 5
Give Back 3
View profile

Avatar
Shape 1
Level 5
CedricRey
Level 5

Likes

41 likes

Total Posts

78 posts

Correct reply

21 solutions
Top badges earned
Shape 1
Boost 25
Applaud 5
Affirm 5
Give Back 3
View profile
CedricRey
Level 5

15-06-2021

Hi,

 

@LaurentLam  you forgot something very important in your good practice example : the order by clause 😉

 

<queryDef schema="nms:recipient" operation="select" lineCount="100">
<where>
<condition expr={"[@id] > " + var.lastProcessedId} />
</where>
<orderBy>
<node expr="[@id]" />
</orderBy>
</queryDef>

 

Cédric

Avatar

Avatar
Give Back 50
Level 4
LaurentLam
Level 4

Likes

23 likes

Total Posts

64 posts

Correct reply

10 solutions
Top badges earned
Give Back 50
Affirm 10
Give Back 25
Give Back 10
Give Back 5
View profile

Avatar
Give Back 50
Level 4
LaurentLam
Level 4

Likes

23 likes

Total Posts

64 posts

Correct reply

10 solutions
Top badges earned
Give Back 50
Affirm 10
Give Back 25
Give Back 10
Give Back 5
View profile
LaurentLam
Level 4

15-06-2021

@CedricRey 

You're right about specifying it in general (so using a work table).

But in my specific example, not needed: as this is a schema integer PK, the "order by" doesn't need to be used as it will automatically use the default index (ASC order by default)

 

@wodnicki , after several search the "offset" SQL format is not performant as using a condition in the where clause (and an order by if needed) even on PG/Oracle/MsSQL...

 

So using this technic trick to loop on a table is not a good practice

Avatar

Avatar
Give back 600
Employee
Sukrity_Wadhwa
Employee

Likes

216 likes

Total Posts

1,027 posts

Correct reply

45 solutions
Top badges earned
Give back 600
Give Back 500
Give Back 400
Give back 300
Give Back 200
View profile

Avatar
Give back 600
Employee
Sukrity_Wadhwa
Employee

Likes

216 likes

Total Posts

1,027 posts

Correct reply

45 solutions
Top badges earned
Give back 600
Give Back 500
Give Back 400
Give back 300
Give Back 200
View profile
Sukrity_Wadhwa
Employee

18-08-2021

 
Status changed to: Investigating