How does the front end of the portal look up the message head ID so quickly

Question

Question

Mark OReilly · Jul 3, 2023

#Message Search #SQL #InterSystems IRIS for Health

In show Query messages in the message viewer the head.%Id is always used. How do you do this via your own sql/objectscript as fast as the portal does a search as using dates searching Ens.MessageHeader on portal is slow.

For instance if you try do a search saying (TimeProcessed >='2023-06-01 00:00:00.000' and TimeProcessed <'2023-06-02 00:00:00.000') it is slow but using the portal the search would know this is head.%ID >= 5344549861 AND head.%ID <= 5347641372. How do you utilize this in your own queries as can't see the logic in EnsPortal.MsgFilter.Assistant

$ZV: IRIS for Windows (x86-64) 2022.1.2 (Build 574U)

Discussion (3)1

Log in or sign up to continue

Guillaume Rongier · Jul 3, 2023

SELECT COUNT(*)
from Ens.MessageHeader
WHERE SourceConfigName = 'EPIC_SIU_IN'
AND TO_NUMBER(SessionId) = %ID
AND %ID >= (SELECT TOP 1 %ID FROM Ens.MessageHeader a WHERE TimeCreated >= '2016-07-27 05:00:00.000' ORDER BY TimeCreated ASC)
AND %ID <= (SELECT TOP 1 %ID FROM Ens.MessageHeader a WHERE TimeCreated < '2016-07-28 05:00:00.000' ORDER BY TimeCreated DESC)SQL
SQL

Just change the date here and see how fast it is.

The idea is to use index and the fastest index in MessageHeader is %ID.

2 0

score 0 · Answer 1 · 2023-07-03T12:34:12-04:00

Have you tried looking at the query plan for your query to see which indexes it is using and whether these appear to make sense?

It appears that the generated query will often ignore the sessionId index as below:

%IGNOREINDEX Ens.MessageHeader.SessionId

score 0 · Answer 2 · 2023-07-03T12:54:44-04:00

Thanks yes this is it, i thought i had tried this combination but think i had the sql on the wrong setting, thank you that's the speed i was looking for