How does the front end of the portal look up the message head ID so quickly
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
Comments
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
SELECTCOUNT(*)
from Ens.MessageHeader
WHERE SourceConfigName = 'EPIC_SIU_IN'AND TO_NUMBER(SessionId) = %IDAND %ID >= (SELECT TOP 1 %IDFROM Ens.MessageHeader a WHERE TimeCreated >= '2016-07-27 05:00:00.000'ORDERBY TimeCreated ASC)
AND %ID <= (SELECT TOP 1 %IDFROM Ens.MessageHeader a WHERE TimeCreated < '2016-07-28 05:00:00.000'ORDERBY TimeCreated DESC)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.
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