WEB-530011 Error

Avatar

matt-gardner

23-10-2017

When using the Adobe Campaign Data API I have a long running query that eventually seems to time out with this error "WEB-530011 Service temporarily unavailable.".  Is there a system setting that controls when this error is returned? I cannot find it in the docs anywhere.

This timeout happens when I run a query that needs to hit a large Message Center table and take a small 1% sample of the email addresses in that table - since there is too much data to return (> 25M records).  This is the query - it is designed to returns the count of sends by delivery label by address:

       <urn:sessiontoken>$sessionToken</urn:sessiontoken>

         <urn:entity>

            <queryDef schema="nms:broadLogEventHisto" operation="count">

            <!-- fields to retrieve -->

              <select>

                <node expr="@address" groupBy="true"/>

                <node expr="[delivery/@label]" groupBy="true"/>

                <node expr="count(@id)" alias="sends"/>

              </select>

              <where>

                <condition expr="@eventDate>='2017-06-01'"/>

              </where>

              <having>

                <condition expr="sha1_mod(digest(@address, 'sha1'), 100)=1"/>

              </having>

            </queryDef>

         </urn:entity>

Is there a more efficient way to randomly sample 1% of the email addresses?  I tried moving the hashing of the email address inside the where and having blocks.  I would like to be able to do this from the API side rather than having to go into Adobe Campaign and run the query in a workflow since I am building data automation that I want to control from outside of Campaign.  I am on Adobe Campaign v7.

Any ideas?

Matt

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Jean-Serge_Biro

MVP

25-10-2017

Hello Matt,

First of all, I don't understand your <having> condition, please may you explain it, what is meant for?

FYI, there are security flaws with SHA-1, so it is deprecated.

And normally, in Query activity, you should see only SHA-2 algorithms (in addition of MD5), so Sha256Digest(string) and Sha512Digest(string).
In the JSAPI, due to backwards compatibility, there are JS functions such as digestStrSha1 and sha1() but please use rather digestStrSha256 (string) (or 512).

Then, the time-out/SQL performance issue:

It is always better, for SQL engine performance, to have filter (where, having) on indexes columns, and if not, in "raw" data (without SQL functions such as uppercase, trim, date format manipulation, etc), so the SQL engine can optimize the execution plan.

In case of you can't, define SQL hint to do the having function after the other merge/hash joins in SQL. But it is a bit tricky, I recommend to analyze what you want to do with the having condition and rewrites it for avoiding the string manipulation/conversion.

Regards.
J-Serge