QueryDef returns empty results when conditions are met

[dwnuk]

Hi,

I have the workflow that insert a personalized string for each recipient, based on the provided value. Personalized strings and values are columns from the table xxx:userStrings. I wrote the function getRandomID(value) which looks for all records from userStrings table with provided value, adds their IDs to an array and then randomly returns one of IDs. After IDs is returned, the flag @used is set to be true.

function getRandomId(val) {   

      var q = xtk.queryDef.create(
      <queryDef schema="xxx:userStrings" operation="select">
        <select>
          <node expr="@id"/>
        </select>
        <where>
          <condition boolOperator="AND" expr={"@stringValue = '" + val + "'"}/>
          <condition boolOperator="AND" expr={"@used = 0"}/>
        </where>
      </queryDef>
      );
    var results = q.ExecuteQuery();
    logInfo("-------------Query results: " + results);
    
    for each (var a in results) {
      outputArr.push(a.@id);
    }
    
    randomId = outputArr[Math.floor(Math.random()*outputArr.length)];
    return randomId;
}

For the smaller number of records (< 1000) it works fine but if it needs to run for larger batches, after some time it starts to return variables results (from above block of code) as empty. Clearly, it is not a true because I see and can query (using Query activity) records that fullfill above conditions.

Do you have any idea how to make it running?

Thanks,

Dominik

[akshaaga · Accepted solution]

Hi @dwnuk,

You can use the top attribute of the select element to limit the number of records returned by the query. For example, you can set top="100" to limit the result set to the top 100 records that match the query conditions.

You can also add a condition that limits the query to only check for records that have not been used in a certain amount of time, such as the last 24 hours.

Another reason, when working with many records, can be that the outputArr array may grow too large and consume too much memory, leading to a memory overflow.

You can modify your function to use a cursor the below way:

function getRandomId(val) {
var randomId;
var q = xtk.queryDef.create(
<queryDef schema="xxx:userStrings" operation="select">
<select>
<node expr="@id"/>
</select>
<where>
<condition boolOperator="AND" expr={"@stringValue = '" + val + "'"}/>
<condition boolOperator="AND" expr={"@used = 0"}/>
</where>
</queryDef>
);
var cursor = q.ExecuteCursor();
var hasResults = cursor.Next();
var outputArr = [];
while (hasResults) {
var result = cursor.Current;
outputArr.push(result.@id);
hasResults = cursor.Next();
}
if (outputArr.length > 0) {
randomId = outputArr[Math.floor(Math.random()*outputArr.length)];
var updateQuery = xtk.queryDef.create(
<queryDef schema="xxx:userStrings" operation="update">
<where>
<condition expr={"@id = " + randomId}/>
</where>
<update>
<node expr="@used">1</node>
</update>
</queryDef>
);
updateQuery.ExecuteQuery();
}
return randomId;
}

You can then check if the array is non-empty, and if so, randomly select an ID from the array and update the corresponding record in the table to set the flag to 1.

[dwnuk]

Thanks, @akshaaga. So the behavior I have experienced is caused by memory overflow?

I will check if using top attribute or the approach with using cursor. 

[akshaaga]

Hi @dwnuk ,

It's possible that the behavior you're seeing is caused by memory overflow, particularly if the function is processing a large number of records and storing all the IDs in memory at once.