Message Count

Question

Question

Farman Ullah · Mar 4

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 (12)3

Log in or sign up to continue

Enrico Parisi · Mar 4

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
)

2 0

score 0 · Answer 1 · 2025-03-04T03:01:45-05:00

Enrico Parisi · Mar 4

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

0 0

score 0 · Answer 2 · 2025-03-04T09:34:02-05:00

Farman Ullah · Mar 4

distinct session ids

0 0

score 0 · Answer 3 · 2025-03-05T00:57:43-05:00

Farman Ullah · Mar 5

Thank you @Enrico Parisi
:-)

0 0

score 1 · Answer 4 · 2025-03-05T18:49:43-05:00

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 😊

score 1 · Answer 5 · 2025-03-07T08:33:21-05:00

Likely significantly faster:


SELECT COUNT(DISTINCT SessionId)
  FROM Ens.MessageHeader
  WHERE ID >= (SELECT TOP 1 ID FROM Ens.MessageHeader WHERE TimeCreated >='2025-02-01 00:00:00.000' ORDER BY TimeCreated ASC)
    AND ID <= (SELECT TOP 1 ID FROM Ens.MessageHeader WHERE TimeCreated <='2025-02-28 23:59:59.999' ORDER BY TimeCreated DESC)

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

score 0 · Answer 6 · 2025-03-11T00:57:53-04:00

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

score 0 · Answer 7 · 2025-03-11T01:00:40-04:00

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.

score 0 · Answer 8 · 2025-03-11T12:52:15-04:00

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.

score 0 · Answer 9 · 2025-03-11T12:57:59-04:00

Do you get a different result with this?


SELECT COUNT(*)
  FROM Ens.MessageHeader
  WHERE ID >= (SELECT TOP 1 ID FROM Ens.MessageHeader WHERE TimeCreated >='2025-02-01 00:00:00.000' ORDER BY TimeCreated ASC)
    AND ID <= (SELECT TOP 1 ID FROM Ens.MessageHeader WHERE TimeCreated <='2025-02-28 23:59:59.999' ORDER BY TimeCreated DESC)
    AND ID = SessionId

score 0 · Answer 10 · 2025-03-06T09:24:25-05:00

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.

score 0 · Answer 11 · 2025-03-06T17:09:30-05:00

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.