Any other options to Iterating entries in a global faster

Question

Question

Rathinakumar S · Jul 26, 2023

#Globals #Key Question #Caché #InterSystems IRIS for Health

Hi,

We have a global with 65 million entries. All we are doing is just iterating through each entry to find out the total no of entries. It is currently taking 110 minutes. Is this something normal? What can we do to speed up this process? Below is the program for just iterating each entry in the global.

s sub=" ,count=0
f{
s sub=$o(^YYY(sub)) q:sub=""
s count= count +1
}
w!, "Total Count:"_ count

$ZV: Cache for Windows (x86-64) 2018.1.8 (Build 766) Wed Mar 8 2023 22:01:03 EST

Discussion (19)2

Log in or sign up to continue

Dmitry Maslennikov · Jul 26, 2023

110 minutes, it seems impossible, or, it's something way too wrong

USER>set ts = $zh f i=1:1:65000000 { set ^YYY(i)="somedata" } write !,"elapsed: ", $zh-ts 

elapsed: 11.126631

USER>s sub="", count = 0, ts = $zh for { set sub = $Order(^YYY(sub)) quit:sub=""  set count = count + 1 } write !,"elapsed: ", $zh-ts 

elapsed: 9.549079

Here result in seconds

Yes, for sure, my example is too simple, and too far from any real situation.

And there are multiple issues that may happen with your data, it can be how it is stored, where it is stored, how much data in values. And it's difficult to suggest how to check it

Have a look at my series of articles about globals in the database, just for information, it may help understand something, what may go wrong

In any case, there is a right way to count objects, without counting all of them this way. Is using bitmap index, which you can use even if you have own storage, and do not use objects yet. You still able to build own bitmap index, and count items by this index will be at least 64000 times faster, whereas 64000 is just chunk size for bitmap, and speed will vary depends if you don't have much empty spaces between id's, which needs to be numeric

1 0

Julius Kavay · Jul 27, 2023

Caution, backward $order() is always slower than forward $order(). If you need the last (hopefully few) entries, than it's OK to use a backward-$O() but running (counting) from the bottom to the top over several millions of nodes is definitely not an effective run.

1 0

score 0 · Answer 1 · 2023-07-26T08:39:52-04:00

Thank you @Dmitry Maslennikov

I have tried the same program and am getting an elapsed 15.1234 seconds to read.

But my real global has more than 6-8 subscripts and takes 110 minutes.

I'm creating a similar global with 6-8 subscripts and works fine and gets a faster response and takes only 15 to 16 seconds. But even though we traverse only one level in the $order program. So there are no issues in that subscript level.

Is there any other issue in my global?

score 0 · Answer 2 · 2023-07-26T08:45:30-04:00

As I mentioned above, it's a complicated question, and to be sure what's really happening, and why the speed is so slow, it requires a look deeply into the database file and into hardware.

score 0 · Answer 3 · 2023-07-30T13:25:35-04:00

my real global has more than 6-8 subscripts and takes 110

What is the result of

 write $$AllocatedSize^%GSIZE($name(^RealGlobal))/1024/1024

a similar global with 6-8 subscripts ... and takes only 15 to 16 seconds

What is the result of

 write $$AllocatedSize^%GSIZE($name(^SimilarGlobal))/1024/1024

score 0 · Answer 4 · 2023-07-31T09:18:51-04:00

Hi @Alexey Maslov

Thank you.

write $$AllocatedSize^%GSIZE($name(^realglobal))/1024/1024
46866.7890625
write $$AllocatedSize^%GSIZE($name(^test global))/1024/1024
4154.2734375

Real Global has a count of 30000000

Test global has a count of 45000000

Both global traverse only 1st level in $order.

Real global takes 90 minutes.

Test Global takes 15 sec

It traverses only the first node even though it takes more time.

score 0 · Answer 5 · 2023-07-31T11:40:24-04:00

Now it is obvious that ^realglobal has 11.3 times the size of ^testglobal
while your ^testglobal seems to have all blocks in buffers in memory
this is rather unlikely for ^realglobal.
So repeated reloading of blocks from disk seems to cause the delay
If you have the chance to double or triple your global buffers
you most likely may see improved processing of your $O()

score 0 · Answer 6 · 2023-08-01T03:54:34-04:00

Both global traverse only 1st level in $order

It seems that you are a bit confused with these levels. Globals are B* trees physically: all subscripts are concatenated into one key, therefore even when you logically traverse only 1st subscript, you traverse all of them physically.

score 0 · Answer 7 · 2023-08-07T15:05:53-04:00

This is an important point. Depending on the size of each node, this $order loop could be touching virtually every block in the global. If you read the 4GB test global after setting it, you're reading from a warm buffer pool, whereas the 40GB production global is less likely to be buffered—hence the greater than 10x difference in time.

I don't have a good suggestion for how to make this loop run faster. $prefetchon might help a little. Rather, depending on why you need to perform this operation, I'd either cache the record count (which could then become a hot spot of its own), or maintain an index global (possibly a bitmap).

score 0 · Answer 8 · 2023-07-26T11:02:48-04:00

I ran your code against a production snapshot and got:
elapsed: 7365.54051
w count
57434071

and

elapsed: 10925.91166
w count
43841315

As you pointed this is a problem in approach.

This is on enterprise hardware so that is not the issue.

I did change your code to use a second subscript ^XXX("YYY",sub)

as an example of a better approach
w $Order(^XXX("YYY",""),-1)
57435810

score 0 · Answer 9 · 2023-07-26T13:04:20-04:00

If your enterprise still uses mechanical disks, that's still a factor that has to be considered

All the blocks required to be read to get through the list can be placed all around the disk/s. And it takes time. On a live system, with many changes, and when a lot of different data is stored, the next block can be far from the previous one. So, on mechanical discs, defragmentation is matter, and may slow the speed.

I don't know the character of your data, but if you have a lot of data, stored in the globals, it will require to read much more blocks, to even just count the items.

And most probably the easiest way to solve it, is just to use bitmap index.

score 0 · Answer 10 · 2023-07-26T13:15:10-04:00

While my global is quite simple, and it's size is about 1.1 GB, or around 18 bytes per record

USER>do ##class(%GlobalEdit).GetGlobalSize("/usr/irissys/mgr/user","YYY",.all,.used)

USER>zw all
all=1109

USER>w all*1024*1024/65000000
17.89031975384615385

After restarting, with a cold cache, I got 17 seconds

USER>s sub="", count = 0, ts = $zh for { set sub = $Order(^YYY(sub)) quit:sub=""  set count = count + 1 } write !,"elapsed: ", $zh-ts 

elapsed: 16.994676

So, my disk can read around 65MB per sec

score 0 · Answer 11 · 2023-07-27T11:28:46-04:00

Hi Rathinakumar,

One reason may be process contention for same block.

Most application processes work by $Ordering forward from the top of the global down.

When doing table scans, this can results in processes waiting, or running behind another process.

As an alternative for Support and Large Reporting Jobs you can instead "$Order UP" instead.

s sub=$o(^YYY(sub),-1) q:sub=""

Interested if this may mitigate any performance issue caused by contention on a busy system.

score 0 · Answer 12 · 2023-07-28T08:26:54-04:00

I understand the objection. However this approach is used successfully on busy systems to side-step contention with large and deep global structures.

score 0 · Answer 13 · 2023-07-27T19:59:17-04:00

In the mean time the answer is already given by Mr Maslennikov and others, but to shorten your runtime, you could also try a "variablenless" loop, maybe this gives you some seconds more... The emphasis is on "maybe".

// if you have null-subscripts enabled
if $d(^YYY(""))!1 for i=0:1 {quit:$order(@$zr)=""} write i

// if null-subscripts are not enabled
if $d(^YYY(-9E18))!1 for i=0:1 {quit:$order(@$zr)=""} write i

// of course, you can do that on any arbitrary level too
if $d(^YYY(1,2,3,""))!1 for i=0:1 {...} write i

// the value of -9E19 ist just for explanation, use a value suitable for your needs

May I ask for the background, why do you need that node count?

score 0 · Answer 14 · 2023-07-28T03:45:58-04:00

Eduard Lebedyuk · Jul 28, 2023

Run MONLBL to see where your code spends time.

0 0

score 3 · Answer 15 · 2023-07-28T04:12:34-04:00

John Murray · Jul 28, 2023

How much memory have you allocated for database cache (global buffers)?

What do your GLOSTAT figures look like?

3 0

score 0 · Answer 16 · 2023-08-06T12:17:13-04:00

Developer Commu... · Aug 6, 2023

💡 This question is considered a Key Question. More details here.

0 0

score 0 · Answer 17 · 2023-08-08T00:03:09-04:00

Hi,

question is here how is that global being populated/created? Is this global defined by usage of a class inheriting from %Persistent?

If so, a fast way would be to be using %Extent to enumerate all instances and then look at the rowcount.

e.g.

set query = ##class(%SQL.Statement).%New()
set qStatus = query.%PrepareClassQuery("User.Person","Extent")
set rset=query.%Execute()
d rset.%Display()
zw %ROWCOUNT