<?xml version="1.0" encoding="UTF-8"?>
<rss xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns:taxo="http://purl.org/rss/1.0/modules/taxonomy/" version="2.0">
  <channel>
    <title>idea Re: Updating the documentation with a good practice. in Adobe Campaign Classic Ideas</title>
    <link>https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-ideas/updating-the-documentation-with-a-good-practice/idc-p/412186#M201</link>
    <description>&lt;P&gt;Hi,&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;The queryDef's translate into ordinary sql, js isn't used (outside interaction which doesn't have startLine attrib).&lt;/P&gt;&lt;P&gt;For most* rdbms' this would add 'limit n &lt;EM&gt;&lt;STRONG&gt;offset m&lt;/STRONG&gt;&lt;/EM&gt;' along with whatever optimizations available, such as 'top' hint for ms sql server.&lt;/P&gt;&lt;P&gt;The reason your queries are running faster when you add indexed conditions is they aren't scanning the full table for results.&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;Thanks,&lt;/P&gt;&lt;P&gt;-Jon&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;*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?&lt;/P&gt;</description>
    <pubDate>Mon, 14 Jun 2021 05:22:16 GMT</pubDate>
    <dc:creator>Jonathon_wodnicki</dc:creator>
    <dc:date>2021-06-14T05:22:16Z</dc:date>
    <item>
      <title>Updating the documentation with a good practice.</title>
      <link>https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-ideas/updating-the-documentation-with-a-good-practice/idi-p/412175</link>
      <description>&lt;P&gt;In the documentation, a bad practice for developper is unwantedly suggested:&lt;BR /&gt;&lt;A href="https://experienceleague.adobe.com/docs/campaign-classic/using/configuring-campaign-classic/api/data-oriented-apis.html?lang=en" target="_blank" rel="noopener"&gt;https://experienceleague.adobe.com/docs/campaign-classic/using/configuring-campaign-classic/api/data-oriented-apis.html?lang=en&lt;/A&gt;&lt;/P&gt;&lt;P&gt;&lt;BR /&gt;To limit the number of records returned by the query to 100:&lt;/P&gt;&lt;P&gt;&amp;lt;queryDef schema="nms:recipient" operation="select" lineCount="100"&amp;gt;&lt;BR /&gt;...&lt;BR /&gt;To retrieve the next 100 records, run the same query again, adding the&amp;nbsp;&lt;STRONG&gt;startLine&lt;/STRONG&gt;&amp;nbsp;attribute.&lt;/P&gt;&lt;P&gt;&amp;lt;queryDef schema="nms:recipient" operation="select" lineCount="100" startLine="100"&amp;gt;&lt;BR /&gt;...&lt;/P&gt;&lt;P&gt;&lt;BR /&gt;In fact, a dev should never use the &lt;STRONG&gt;startLine&lt;/STRONG&gt; attribute as it contribute to a loss of performance:&lt;/P&gt;&lt;P&gt;exemple with a SQL translation of the 1rst example:&lt;/P&gt;&lt;P&gt;&lt;EM&gt;select top 100 from nmsRecipient&lt;/EM&gt;&lt;/P&gt;&lt;P&gt;You'll get the 1rst 100 records... No issue about that&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;Now is the SQL translation of the 2nd querydef:&lt;/P&gt;&lt;P&gt;&lt;EM&gt;select top 200 from nmsRecipient&lt;/EM&gt;&lt;/P&gt;&lt;P&gt;=&amp;gt; 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&lt;/P&gt;&lt;P&gt;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:&lt;/P&gt;&lt;P&gt;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).&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;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:&lt;/P&gt;&lt;P&gt;&lt;EM&gt;&amp;lt;queryDef schema="nms:recipient" operation="select" lineCount="100"&amp;gt;&lt;/EM&gt;&lt;BR /&gt;&lt;EM&gt;&amp;lt;where&amp;gt; &lt;/EM&gt;&lt;BR /&gt;&lt;EM&gt;&amp;lt;condition expr="[@id] &amp;gt; " var.lastProcessedId /&amp;gt;&lt;/EM&gt;&lt;BR /&gt;&lt;EM&gt;&amp;lt;/where&amp;gt;&lt;/EM&gt;&lt;BR /&gt;&lt;EM&gt;&amp;lt;/queryDef&amp;gt;&lt;/EM&gt;&lt;BR /&gt;I observed in the past some workflow JS activity with the "startLine" implementation that were optimized from 4h down to 2 min.&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;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.&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;Ps: TY&amp;nbsp;&lt;LI-USER uid="17446130"&gt;&lt;/LI-USER&gt;&amp;nbsp; for this tip months ago&lt;/P&gt;</description>
      <pubDate>Sun, 13 Jun 2021 23:37:12 GMT</pubDate>
      <guid>https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-ideas/updating-the-documentation-with-a-good-practice/idi-p/412175</guid>
      <dc:creator>LaurentLam</dc:creator>
      <dc:date>2021-06-13T23:37:12Z</dc:date>
    </item>
    <item>
      <title>Re: Updating the documentation with a good practice.</title>
      <link>https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-ideas/updating-the-documentation-with-a-good-practice/idc-p/412186#M201</link>
      <description>&lt;P&gt;Hi,&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;The queryDef's translate into ordinary sql, js isn't used (outside interaction which doesn't have startLine attrib).&lt;/P&gt;&lt;P&gt;For most* rdbms' this would add 'limit n &lt;EM&gt;&lt;STRONG&gt;offset m&lt;/STRONG&gt;&lt;/EM&gt;' along with whatever optimizations available, such as 'top' hint for ms sql server.&lt;/P&gt;&lt;P&gt;The reason your queries are running faster when you add indexed conditions is they aren't scanning the full table for results.&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;Thanks,&lt;/P&gt;&lt;P&gt;-Jon&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;*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?&lt;/P&gt;</description>
      <pubDate>Mon, 14 Jun 2021 05:22:16 GMT</pubDate>
      <guid>https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-ideas/updating-the-documentation-with-a-good-practice/idc-p/412186#M201</guid>
      <dc:creator>Jonathon_wodnicki</dc:creator>
      <dc:date>2021-06-14T05:22:16Z</dc:date>
    </item>
    <item>
      <title>Re: Updating the documentation with a good practice.</title>
      <link>https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-ideas/updating-the-documentation-with-a-good-practice/idc-p/412218#M202</link>
      <description>&lt;P&gt;Hi&amp;nbsp;&lt;LI-USER uid="11280283"&gt;&lt;/LI-USER&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;Didn't know for this syntax on most RDBMS.&lt;/P&gt;&lt;P&gt;But carefull: there is no index on WKF worktables, even PK, (checked on PostGres &amp;amp; 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&lt;/P&gt;</description>
      <pubDate>Mon, 14 Jun 2021 10:48:53 GMT</pubDate>
      <guid>https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-ideas/updating-the-documentation-with-a-good-practice/idc-p/412218#M202</guid>
      <dc:creator>LaurentLam</dc:creator>
      <dc:date>2021-06-14T10:48:53Z</dc:date>
    </item>
    <item>
      <title>Re: Updating the documentation with a good practice.</title>
      <link>https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-ideas/updating-the-documentation-with-a-good-practice/idc-p/412427#M203</link>
      <description>&lt;P&gt;Hi,&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;&lt;LI-USER uid="17453484"&gt;&lt;/LI-USER&gt;&amp;nbsp; you forgot something very important in your good practice example : the order by clause &lt;img class="lia-deferred-image lia-image-emoji" src="https://experienceleaguecommunities.adobe.com/html/@05C0D9C61C55A19E315F9F671AC49C5A/emoticons/1f609.png" alt=":winking_face:" title=":winking_face:" /&gt;&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;LI-CODE lang="markup"&gt;&amp;lt;queryDef schema="nms:recipient" operation="select" lineCount="100"&amp;gt;
&amp;lt;where&amp;gt;
&amp;lt;condition expr={"[@id] &amp;gt; " + var.lastProcessedId} /&amp;gt;
&amp;lt;/where&amp;gt;
&amp;lt;orderBy&amp;gt;
&amp;lt;node expr="[@id]" /&amp;gt;
&amp;lt;/orderBy&amp;gt;
&amp;lt;/queryDef&amp;gt;&lt;/LI-CODE&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;Cédric&lt;/P&gt;</description>
      <pubDate>Tue, 15 Jun 2021 07:27:34 GMT</pubDate>
      <guid>https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-ideas/updating-the-documentation-with-a-good-practice/idc-p/412427#M203</guid>
      <dc:creator>CedricRey</dc:creator>
      <dc:date>2021-06-15T07:27:34Z</dc:date>
    </item>
    <item>
      <title>Re: Updating the documentation with a good practice.</title>
      <link>https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-ideas/updating-the-documentation-with-a-good-practice/idc-p/412577#M204</link>
      <description>&lt;P&gt;&lt;LI-USER uid="17446130"&gt;&lt;/LI-USER&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;You're right about specifying it in general (so using a work table).&lt;/P&gt;&lt;P&gt;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)&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;&lt;LI-USER uid="11280283"&gt;&lt;/LI-USER&gt;&amp;nbsp;, 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...&lt;/P&gt;&lt;P&gt;&amp;nbsp;&lt;/P&gt;&lt;P&gt;So using this technic trick to loop on a table is not a good practice&lt;/P&gt;</description>
      <pubDate>Tue, 15 Jun 2021 18:34:41 GMT</pubDate>
      <guid>https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-ideas/updating-the-documentation-with-a-good-practice/idc-p/412577#M204</guid>
      <dc:creator>LaurentLam</dc:creator>
      <dc:date>2021-06-15T18:34:41Z</dc:date>
    </item>
    <item>
      <title>Re: Updating the documentation with a good practice. - Status changed to: Investigating</title>
      <link>https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-ideas/updating-the-documentation-with-a-good-practice/idc-p/419621#M223</link>
      <description />
      <pubDate>Wed, 18 Aug 2021 12:43:46 GMT</pubDate>
      <guid>https://experienceleaguecommunities.adobe.com/t5/adobe-campaign-classic-ideas/updating-the-documentation-with-a-good-practice/idc-p/419621#M223</guid>
      <dc:creator>Sukrity_Wadhwa</dc:creator>
      <dc:date>2021-08-18T12:43:46Z</dc:date>
    </item>
  </channel>
</rss>

