Hi all,

Novice MUMPer here. I am trying to query a large dataset using M and was wondering how to efficiently query a global.

For example, how does one filter for 10,000 of the most recent records (by Unix Time) which have a pattern of ^table(*,"A","C")? For a small list size, one could write some kind of $data/$order counting loop, but is that really the most efficient way to
query something like this?

^table(1639615761233,"A","B")=1
^table(1639615761499,"A","C")=1
^table(1639615761761,"A","D")=0
^table(1639615762029,"A","E")=1
^table(1639615762298,"A","F")=1
^table(1639615762696,"A","G")=0
^table(1639615763508,"B","C")=0
^table(1639615763821,"B","D")=0
^table(1639615764099,"B","E")=0
^table(1639615764380,"B","F")=1
^table(1639615764640,"B","G")=1
^table(1639615768122,"D","C")=0
^table(1639615769781,"D","F")=1
^table(1639616032012,"A","B")=0
^table(1639616032294,"A","C")=0
^table(1639616032565,"A","D")=1
(...)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

пятница, 17 декабря 2021 г. в 02:15:14 UTC+2, KM:

Hi all,

Novice MUMPer here. I am trying to query a large dataset using M and was wondering how to efficiently query a global.

For example, how does one filter for 10,000 of the most recent records (by Unix Time) which have a pattern of ^table(*,"A","C")? For a small list size, one could write some kind of $data/$order counting loop, but is that really the most efficient way

to query something like this?

^table(1639615761233,"A","B")=1
^table(1639615761499,"A","C")=1
^table(1639615761761,"A","D")=0
^table(1639615762029,"A","E")=1
^table(1639615762298,"A","F")=1
^table(1639615762696,"A","G")=0
^table(1639615763508,"B","C")=0
^table(1639615763821,"B","D")=0
^table(1639615764099,"B","E")=0
^table(1639615764380,"B","F")=1
^table(1639615764640,"B","G")=1
^table(1639615768122,"D","C")=0
^table(1639615769781,"D","F")=1
^table(1639616032012,"A","B")=0
^table(1639616032294,"A","C")=0
^table(1639616032565,"A","D")=1
(...)

You can do something like this:

s (date,var1,var2)="",interval1="16396160....",interval2="16396161..." ; Your time intervals !!!
f s date=$o(^table(date)) q:date="" d
. f s var1=$o(^table(date,var1)) q:var1="" d
. . f s var2=$o(^table(date,var1,var2)) q:var2="" d
. . . i (interval1'="")&(interval2'="")&(date'<interval1)&(date'>interval2) d
. . . . s steps="Your next steps: ="_date
. . . . w steps,!
..................

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

пятница, 17 декабря 2021 г. в 02:15:14 UTC+2, KM:

Hi all,

Novice MUMPer here. I am trying to query a large dataset using M and was wondering how to efficiently query a global.

For example, how does one filter for 10,000 of the most recent records (by Unix Time) which have a pattern of ^table(*,"A","C")? For a small list size, one could write some kind of $data/$order counting loop, but is that really the most efficient way

to query something like this?

^table(1639615761233,"A","B")=1
^table(1639615761499,"A","C")=1
^table(1639615761761,"A","D")=0
^table(1639615762029,"A","E")=1
^table(1639615762298,"A","F")=1
^table(1639615762696,"A","G")=0
^table(1639615763508,"B","C")=0
^table(1639615763821,"B","D")=0
^table(1639615764099,"B","E")=0
^table(1639615764380,"B","F")=1
^table(1639615764640,"B","G")=1
^table(1639615768122,"D","C")=0
^table(1639615769781,"D","F")=1
^table(1639616032012,"A","B")=0
^table(1639616032294,"A","C")=0
^table(1639616032565,"A","D")=1
(...)

you can $order -1 and use the counter to read the last 10,000 records ...

s (date,var1,var2,cik)=""
f s date=$o(^table(date,-1)) q:date="" d
. f s var1=$o(^table(date,var1,-1)) q:var1="" d
. . f s var2=$o(^table(date,var1,var2,-1)) q:var2="" d
. . . s cik=cik+1 q:cik=10000 d
. . . . s steps="Your data: ="_date
. . . . w steps,!

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

To me, the most straightforward approach seems to be something like:

Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
. Set reference=$Query(@reference,-1) Quit:reference=""
. Quit:reference'["""A"",""C"")"
. Set count=count+1,result(reference)=1
. Quit
Quit

(please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit commands)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, December 17, 2021 at 11:44:35 AM UTC-5, ed de moel wrote:

To me, the most straightforward approach seems to be something like:

Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
. Set reference=$Query(@reference,-1) Quit:reference=""
. Quit:reference'["""A"",""C"")"
. Set count=count+1,result(reference)=1
. Quit
Quit

(please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit commands)

Ed wins! :-) I was putting together the same method, but he beat me to it.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, December 17, 2021 at 11:44:35 AM UTC-5, ed de moel wrote:

To me, the most straightforward approach seems to be something like:

Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
. Set reference=$Query(@reference,-1) Quit:reference=""
. Quit:reference'["""A"",""C"")"
. Set count=count+1,result(reference)=1
. Quit
Quit

(please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit com

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

OldMster schrieb am Freitag, 17. Dezember 2021 um 23:03:35 UTC+1:

On Friday, December 17, 2021 at 11:44:35 AM UTC-5, ed de moel wrote:

To me, the most straightforward approach seems to be something like:

Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
. Set reference=$Query(@reference,-1) Quit:reference=""
. Quit:reference'["""A"",""C"")"
. Set count=count+1,result(reference)=1
. Quit
Quit

(please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit commands)

Ed wins! :-) I was putting together the same method, but he beat me to it.

I don't agree.
I'd code:
S count=0,time="" F S time=$O(^table(time),-1) Q:time=""!(count>9999) D
.I $D(^table(time,"A","C")) W time,^table(time,"A","C"),! S count=count+1
Q

Or do I overlook something?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, December 17, 2021 at 5:35:03 PM UTC-5, Jens wrote:

OldMster schrieb am Freitag, 17. Dezember 2021 um 23:03:35 UTC+1:

On Friday, December 17, 2021 at 11:44:35 AM UTC-5, ed de moel wrote:

To me, the most straightforward approach seems to be something like:

Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
. Set reference=$Query(@reference,-1) Quit:reference=""
. Quit:reference'["""A"",""C"")"
. Set count=count+1,result(reference)=1
. Quit
Quit

(please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit commands)

Ed wins! :-) I was putting together the same method, but he beat me to it.

I don't agree.
I'd code:
S count=0,time="" F S time=$O(^table(time),-1) Q:time=""!(count>9999) D
.I $D(^table(time,"A","C")) W time,^table(time,"A","C"),! S count=count+1
Q

Or do I overlook something?

Thank you for the responses. I will look into the query examples given.

Being so use to SQL syntax, I would have thought loops would be a inefficient way to do querying such as this. I guess as long as one writes well using loops the O(n) factor may be managed.

Gratitude,
KM

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, December 17, 2021 at 5:35:03 PM UTC-5, Jens wrote:

OldMster schrieb am Freitag, 17. Dezember 2021 um 23:03:35 UTC+1:

On Friday, December 17, 2021 at 11:44:35 AM UTC-5, ed de moel wrote:

To me, the most straightforward approach seems to be something like:

Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
. Set reference=$Query(@reference,-1) Quit:reference=""
. Quit:reference'["""A"",""C"")"
. Set count=count+1,result(reference)=1
. Quit
Quit

(please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit commands)

Ed wins! :-) I was putting together the same method, but he beat me to it.

I don't agree.
I'd code:
S count=0,time="" F S time=$O(^table(time),-1) Q:time=""!(count>9999) D
.I $D(^table(time,"A","C")) W time,^table(time,"A","C"),! S count=count+1
Q

Or do I overlook something?

Thank you for the responses. I will look into the query examples given.

Being so use to SQL syntax, I would have thought loops would be a inefficient way to do querying such as this. I guess as long as one writes well using loops the O(n) factor may be managed.

Gratitude,
KM

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <ec592130-7d33-4d67-843e-0b3b3cd15dc8n@googlegroups.com>,
ed de moel <eddemoel@gmail.com> wrote:

To me, the most straightforward approach seems to be something like:

Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
. Set reference=$Query(@reference,-1) Quit:reference=""
. Quit:reference'["""A"",""C"")"
. Set count=count+1,result(reference)=1
. Quit
Quit

(please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit commands)

Shouldn't you also check that $QL(reference)=3

--
-- Rod --
rodd(at)polylogics(dot)com

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)