How to improve the performanc of even query based on DE and value filter?

[linxd (晓东 林)]

hi,all,
I found do event query based on filter of DE 's value will cost more time,is there some way to improve?

…/api/events?programStages=…&filter={de}:eq:{value}

for about 500000 events, query an eventts will cost about 10s.

Thanks

[Gassim (AL-Gassim Sharaf Addin)]

Hi @linxd,
Are you using the fields=[specific fields that you want] in your request?

As @austin always recommends to use fields to request a specific number of fields (which will surely improve the performance) ,

@Gassim

Q - Why is it not recommended to use fields=*?

@austin you can specify explicit fields within the api end point so you shouldn’t need fields=* (which can create a heavy request)

[linxd (晓东 林)]

used, but almost the same.

[linxd (晓东 林)]

Hi,sir,
If I modeling the search attribute as tracker program’s tracker type ,the searched attribute be setted as searchable, will this promote the search performance?
Thanks.

[Gassim (AL-Gassim Sharaf Addin)]

Hi @linxd,

Would it be possible for you to test the performance of the queries you are using in the play / demo instances? See if there’s any difference and make note please!

@linxd

If I modeling the search attribute as tracker program’s tracker type ,the searched attribute be setted as searchable, will this promote the search performance?

Interesting question, did you try to do it? If you do maybe you will tell us whether this method worked for you or not!

Thank you!

[Gassim (AL-Gassim Sharaf Addin)]

@linxd

used, but almost the same.

How about adding paging to the query? (:

[linxd (晓东 林)]

skippages? skipPaging=true&totalPages=false

almost just return one event. so ,paging is no more important ,I think, and I have a try ,almost the same.

I found add the event as an tracker entity , can gain high performance of query on entity’s attribute .

[Gassim (AL-Gassim Sharaf Addin)]

@linxd

almost just return one event. so ,paging

It’s supposed to return by default 50 (so how come it’s returning one event only?) You can choose the number of events when you use the pageSize Parameter such as pageSize=55

I would like to make a suggestion if you would like to try the different formats:
?format=xml
?format=json
?format=jsonp
(and maybe if getting the result as a file is useful too then you have these options):
?format=csv
?format=xls
?format=gzip

@linxd

I found add the event as an tracker entity , can gain high performance of query on entity’s attribute .

Please share an example! Do you mean requesting the events per TEI? Thanks!

[linxd (晓东 林)]

/api/33/events/?skipPaging=true&ouMode=DESCENDANTS&fields=event,orgUnitName,dataValues[dataElement,value]&programStage=vzuqikrBNfx&orgUnit=pJ0C3w6gmrK&filter=i16mT3FG6sw:eq:%E6%B3%BD%E5%BE%B7
will cost 6-7s for searching from about 400000 events.

/api/33/trackedEntityInstances?trackedEntityType=lZxRQd3LqDR&ou=pJ0C3w6gmrK&ouMode=DESCENDANTS&fields=orgUnit,trackedEntityInstance,trackedEntityType,attributes[code,attribute,value,displayName]&attribute=HMTa4VjEIaw:eq:%E5%A6%87%E4%BA%A7%E7%A7%91%E4%B8%93%E4%B8%9A

will cost 1-2s for searching from about 400000 trackedEntityInstances.

[Gassim (AL-Gassim Sharaf Addin)]

That’s a great comparison, thanks for sharing! Does it mean we can mark your post above as a solution to this particular issue? Thank you!

[linxd (晓东 林)]

I don’t think so, can every event program be map as an tracker program? and how about searching tracker’s events?

[Gintare]

Hey @linxd,

Only tracker programs have tracked entity instances, so /trackedEntityInstances endpoint won’t work for event programs. However, /events endpoint will work for both tracker and event programs as both tracker and event programs can have events. Regarding performance, we are working to improve it, and we have a jira issue for /events endpoint. Could you please write a comment on the issue describing your case?

Thanks,
Gintare

[linxd (晓东 林)]

Thanks.
I mean the performance of tracker program stages (i think as events in tracker program).