yuuvis® RAD Q&A

0 votes
by (210 points)
edited by

Hi,

a custom microservice executes ESQL queries to identify documents with specific conditions.

A document was created by the user ROOT.
Then indexdata is updated by user DEMO.

Yuuvis webclient and DmsService.getItem are showing ROOT as creator (dmsobject.created.by.name)

ESQL query to select objects by creator (as described here: https://help.optimal-systems.com/yuuvis_develop/display/onpremise/Archiving Statements)

SELECT u.sysname, d.*
FROM dms:etcmdocument d join ecm:syscreationtraceowner2user c2u join org:sysuser u
WHERE d.sysitemid = id'13B3A9CF64B7448A91ADDD19AB6A0AAF' and d.conditionField = false;

returns DEMO as u.sysname

I understand I could set d.sysversionnumber = 1 to get the creator of the first version.

Is it somehow possible to query conditions on active version and creator condition on initial version (as shown by client and returned by DmsService.getItem)?

Regards,

Marco (OSVK)

1 Answer

+1 vote
by (14.7k points)

Hello Marco,

each version of a dms-object is considered as its own "object". So in the esql query you have to add a condition that limits the returned objects to only those where there is another object that has the same item-id and is in the first version and where the creator (of this version) is ROOT - like this:

SELECT u.sysname, d.*
FROM dms:etcmdocument d join ecm:syscreationtraceowner2user c2u join org:sysuser u
WHERE d.sysitemid = id'13B3A9CF64B7448A91ADDD19AB6A0AAF' and d.conditionField = false 
AND 'root' = (SELECT u.sysname FROM dms:etcmdocument (all versions) d join ecm:syscreationtraceowner2user c2u join org:sysuser u 
                      WHERE d.sysitemid = id'13B3A9CF64B7448A91ADDD19AB6A0AAF' 
		      and d.conditionField = false 
		      and d.sysversionnumber = 1)

Best regards
Nicolai

by (210 points)
Hi Nicolai,

thanks for the answer.

That works if I want to query one object. My example query had the ID just set to illustrate the problem.

In my case I have a more generic approach. But your answer helped and I'm positive I now achive what I.

But I'm not sure if such a query is still performant if there are millions of objects.

What do you think, should this work or do you know a better approach?


SELECT    u.sysname,
        d1.sysid, d1.sysitemid, d1.sysversionnumber, d1.sysversionmostrecent, d1.fieldCondition,
        d2.sysid, d2.sysitemid, d2.sysversionnumber, d2.fieldCondition
FROM    dms:etcmdocument d1,
        dms:etcmdocument (all versions) d2 join ecm:syscreationtraceowner2user c2u join org:sysuser u
WHERE    (d1.fieldCondition = false or d1.fieldCondition = null)
        and d1.sysitemid = d2.sysitemid
        and d2.sysversionnumber = 1
        and d1.sysversionmostrecent = true
        and u.sysname in ('root', 'technical')
       

The query might even be more complex setting conditions on contextfolder:


SELECT    u.sysname,
        d1.sysid, d1.sysitemid, d1.sysversionnumber, d1.sysversionmostrecent, d1.fieldCondition,
        d2.sysid, d2.sysitemid, d2.sysversionnumber, d2.fieldCondition,
        f.sysid, f.sysitemid, f.sysversionnumber, f.sysversionmostrecent
FROM    dms:sapalcomponent d1,
        dms:sapalcomponent (all versions) d2 join ecm:syscreationtraceowner2user c2u join org:sysuser u,
        dms:sapaldocument f
WHERE    f isParentOf d1
        and f.barcode in ('4711','4712')
         and (d1.fieldCondition = false or d1.fieldCondition = null)
        and d1.sysitemid = d2.sysitemid
        and d2.sysversionnumber = 1
        and d1.sysversionmostrecent = true
        and u.sysname in ('root', 'technical')
by (14.7k points)
+1
Hi Marco,

the statements look good to me. You can omit the sysversionmostrecent condition - if you don't specify "(all versions)" the most recent version is automatically selected. But of course it propably doesn't hurt either.

For the performance, please consider the following points:
 - all fields that are part of the conditions should have an index on the database. You can control that in the designer, in the properties of the field. (System-Fields will most probably already have an index, so you shouldn't have to care about that.)
 - it is important to keep the result set size as small as possible. So, if you can, add limiting conditions to reduce the size. If nothing else is there, you could at least limit on the creation- and/or modification-date of the objects.

In general, polling is always less favorable than reacting to events. The core-service sends out messages to the messaging-service (ActiveMQ Topic) whenever a dms-object is created, changed or deleted. You could configure your microservice to listen to these events (messages) and react if the affected dms-object matches your conditions. Then you can get rid of the esql polling - or at least only do it once a day / week to make sure you haven't missed any events (due to, for example, restarts, network problems, or alike).

Best regards
Nicolai
by (210 points)
Hi Nicolai,

that's what we do. We have two services. The first listens for certain messages and does things.

The second service looks for objects missed by the first service due to missed messages or whatever reasons.

So the queries result set size should not be too huge.
We even limit the result set to "TOP confValue."

I guess we should set the index on the condition fields even if the result set is small but the total amount of objects is huge?
by (14.7k points)
+1
Yes, absolutely. Without an index, the database has to scroll through all rows of the table to identify matching rows. That's the worst case for the performance.

Related questions

...