0
0

Written by

Farman Ullah
Follow
Question Farman Ullah · Mar 4

Message Count

#Caché #Ensemble #HealthShare

Looking for a SQL query or any other method to find the Unique/distinct message counts for all productions or at least per production namespace within a given time frame. For e.g TimeCreated = January 2025 (Whole month)

I have used the following, but its not restricting the numbers based on the TimeCreated filter. Every time a new message is processed by system, its added to the total. I am running the query in today's date

Select Sum(MsgCount)

From

(Select DISTINCT TargetConfigName, count(DISTINCT SessionID) as MsgCount

FROM ENS.MessageHeader 

Where TimeCreated >='2025-01-01 00:00:00' and TimeCreated<='2025-01-31 23:59:59'

Group By TargetConfigName) as Table

Product version: Caché 2018.1
$ZV: 2018.1.8 Cache for Windows
Discussion (0)0
Log in or sign up to continue

Comments

Enrico Parisi · Mar 4

Can you please define "Unique/distinct message counts"?

0
Enrico Parisi  Mar 4 to Farman Ullah

select count(*) from (
select SessionId
from Ens.MessageHeader
where TimeCreated between '2025-02-01 00:00:00' and '2025-02-28 23:59:59'
group by SessionId
)

0
Enrico Parisi  Mar 5 to Enrico Parisi

Well...if fact, a better query would be:

select count(DISTINCT SessionId)
from Ens.MessageHeader
where TimeCreated between '2025-02-01 00:00:00' and '2025-02-28 23:59:59'

Note: while the query is different, it ends running pretty much the same code as the previous query, but I like this better 😊

0
Jeffrey Drumm  Mar 7 to Enrico Parisi

Likely significantly faster:

SELECTCOUNT(DISTINCT SessionId)
  FROM Ens.MessageHeader
  WHEREID >= (SELECT TOP 1IDFROM Ens.MessageHeader WHERE TimeCreated >='2025-02-01 00:00:00.000'ORDERBY TimeCreated ASC)
    ANDID <= (SELECT TOP 1IDFROM Ens.MessageHeader WHERE TimeCreated <='2025-02-28 23:59:59.999'ORDERBY TimeCreated DESC)

In my crude and hasty benchmarking, twice as fast on a sampling of  2.7M message headers.

0
Farman Ullah  Mar 11 to Enrico Parisi

As a result of your query I'm getting a different value with respect to if I run the same time frame with Jeffery Drumm query. For e.g I'm getting a value of 52890 for both of your quries (same count). But if I use Jeffery query I am getting a total of 136,175

0
Farman Ullah  Mar 11 to Farman Ullah

I have manually checked on the Management portal (Message View) and getting same or close count of messages with Enrico query. I have tried this for a day timeframe.

0
Jeffrey Drumm  Mar 11 to Farman Ullah

That's odd. I obtained the same results from both queries on my system; the only difference was the speed of execution.

The subquery model from my example is the same one used behind the scenes by InterSystems to select messages by date range in the message viewer. I can't imagine why you'd be getting different results.

0
Jeffrey Drumm  Mar 11 to Farman Ullah

Do you get a different result with this?

SELECTCOUNT(*)
  FROM Ens.MessageHeader
  WHEREID >= (SELECT TOP 1IDFROM Ens.MessageHeader WHERE TimeCreated >='2025-02-01 00:00:00.000'ORDERBY TimeCreated ASC)
    ANDID <= (SELECT TOP 1IDFROM Ens.MessageHeader WHERE TimeCreated <='2025-02-28 23:59:59.999'ORDERBY TimeCreated DESC)
    ANDID = SessionId
0
Oliver Thompson · Mar 6

This is a mix of the 2 other suggestions:


select count(*)
from Ens.MessageHeader
where TimeCreated between '2025-02-01 00:00:00' and '2025-02-28 23:59:59'
group by sessionid

All 3 options will work, always more than 1 way to skin a cat.

I would look a the SQL Show Plan to see which one is the most efficient.

0
Enrico Parisi  Mar 6 to Oliver Thompson

I'm afraid that the query you suggest does not returns the same result.

It returns one row per session, each with the number of messages for the session.

0