Slow harmonization when using wildcard in MarkLogic

Question

I am harmonizing documents in MarkLogic that completes within 26 mins without the snippets of wildcard code not being executed (commented out). When I execute the code below, the harmonization time is completed in a much longer time, 1 hour and 50 mins.

This snippet of code is my attempt of translating the SQL code below:

DECLARE @GETPRODLOSS decimal(18,2)
IF @ASSETNUMBER = NULL
    SET @GETPRODLOSS = NULL
ELSE
    SELECT @GETPRODLOSS = CONVERT(decimal(18,2), SUM(GLU/100)) 
        FROM AccountLedgerTable 
        WHERE GLDCT = 'MU' AND 
        GLSBLT = 'W' AND 
        LTRIM(SUBSTRING(GLASID,2,25)) LIKE concat('%',@assetNumber) AND 
        GLSBL LIKE concat('%',@workOrder) 
        GROUP BY GLANI
    RETURN(@GETPRODLOSS)
END

I wonder why it is taking that long to execute. Below is the equivalent javascript code snippets for this in MarkLogic:

function getAffectedProduction(assetNumber, workOrder) {
  let accountLedger =  cts.search(cts.andQuery([
     cts.collectionQuery("JDEdwards"),
     cts.collectionQuery("JDEAccountLedger"),
     cts.elementWordQuery(fn.QName("http://www.example.com/ads/JDEdwards/document","GLDCT"), "MU"),
     cts.elementWordQuery(fn.QName("http://www.example.com/ads/JDEdwards/document","GLSBLT"), 'W'),
     cts.elementWordQuery(fn.QName("http://www.example.com/ads/JDEdwards/document","GLASID"), fn.concat("*", assetNumber), "wildcarded"),
     cts.elementWordQuery(fn.QName("http://www.example.com/ads/JDEdwards/document","GLSBL"), fn.concat("*", workOrder), "wildcarded")
   ]))


   let affectedProduction = new Number();

   if(fn.count(accountLedger) <= 0) return "";

   affectedProduction = 0;

   let docXML = new String();
   for (const item of accountLedger) {
    
     affectedProduction += fn.number(`${fn.normalizeSpace(hl.elementText(item, "GLU"))}`);

   }

   return fn.string(fn.abs(affectedProduction/100));

 }

Note: I have setup element range index for elements GLDCT, GLSBLT, GLASID, GLSBL

Is there something wrong with my code? Or is there an admin interface setting I need to turn on when using wildcards?

score 1 · Answer 1 · answered Oct 03 '18 at 06:06

1

Take a look at MarkLogic's recommended wildcard index settings. I would also set your cts.search call to run as unfiltered once you have followed the recommendations.

answered Oct 03 '18 at 06:06

Rob S.

3,599
6
30
39

Hey @Rob thanks for the response. What do you mean by cts.search call unfiltered? – Ryan Oct 03 '18 at 08:28
@Ryan-- By "unfiltered" he means that once you will follow the recommendations then in your cts:search put the option values as "unfiltered" search. – Shalini Oct 03 '18 at 13:56
@Ryan, in your development, it may be a good idea to run your harmonization flow in both filtered and unfiltered and compare the results, to ensure the correctness of your results. – Fan Li Oct 03 '18 at 15:23
Thanks! i tried to use the "unfiltered" option but it took my harmonization a longer time to complete, more than 2 hours. – Ryan Oct 08 '18 at 04:13
@Ryan you might want to check and see how many results your cts.search call is returning. If unfiltered is returning more than filtered you might be seeing false positives which could increase the processing time. – Rob S. Oct 08 '18 at 17:08

Slow harmonization when using wildcard in MarkLogic

1 Answers1