topic Re: Optimization of data step that updates Login Account in Metadata in SAS Programming

Optimization of data step that updates Login Account in Metadata

FK21 — Tue, 18 Mar 2025 15:41:04 GMT

Hi Folks,

I have a piece of SAS code that updates for a given user in Metadata (and a given Authentication doman of this particular user) its Login-Credentials:

 %macro update_login_in_metada(name, pwd, auth);

    data _null_;
        length uri_domain $256.;
        length uri_user $256.;
        length uri_login $256.;
        length name_domain $200.;
        length name_user $200.;
        
        * initialize "uri*" and "name_*" variables;
        call missing(of uri_: name_:);
  
        * initialize variable "nobj1";
        nobj1 = -1;

        * write number of person objects found in metadata into "nobj1";
        nobj1 = metadata_getnobj("omsobj:Person?@Id contains '.'", 1, uri_user);
        
        * loop through all found person objects;
        if nobj1 > 0 then
            do;
                do i = 1 to nobj1;
                    nobj2 = metadata_getnobj("omsobj:Person?@Id contains '.'", i, uri_user);
                    rc1 = metadata_getattr(uri_user, "Name", name_user);
                    
                    * lookup person object;
                    if name_user = "&name." then
                        do;
                            * get corresponding Login-Account of Auth-Domain;
                            nobj3 = metadata_getnasn(uri_user, "Logins", 1, uri_login);

                            * loop through all Login-Accounts of particular user;
                            if nobj3 > 0 then
                                do;
                                    do j = 1 to nobj3;
                                        nobj4 = metadata_getnasn(uri_user, "Logins", j, uri_login);

                                        nobj5 = metadata_getnasn(uri_login, "Domain", 1, uri_domain);

                                        rc2 = metadata_getattr(uri_domain, "Name", name_domain);
                                        
                                        if name_domain = "&auth." then  /* update password for selected Auth-Domain*/
                                            do;
                                                rc3 = metadata_setattr(uri_login, "Password", "&pwd.");
                                            end;
                                    end;
                                end;
                        end;
                end;
            end;
    run;
%mend update_login_in_metada;

It takes approximately 50 seconds for one user to run this step:

%update_login_in_metada(johndoe, abc123, DBAuth)

To me it seems, that the Data Step Metadata Functions are very slow...

I know of the Metadata Procedure as a potential alternative way to update metadata. However, I do not know, how to set up/write the appropriate xml file structure as input to the procedure.

Also, is the Metadata Procedure faster than the Data Step Metadata Functions?

Any help would be highly appreciated,

FK21

Re: Optimization of data step that updates Login Account in Metadata

SASKiwi — Tue, 18 Mar 2025 19:45:37 GMT

I think you could improve on the logic you have posted by dropping out of your looping once the password has been updated. Try this:

%macro update_login_in_metada(name, pwd, auth);

    data _null_;
        length uri_domain $256.;
        length uri_user $256.;
        length uri_login $256.;
        length name_domain $200.;
        length name_user $200.;
        
        * initialize "uri*" and "name_*" variables;
        call missing(of uri_: name_:);
  
        * initialize variable "nobj1";
        nobj1 = -1;

        * write number of person objects found in metadata into "nobj1";
        nobj1 = metadata_getnobj("omsobj:Person?@Id contains '.'", 1, uri_user);

		Password_Updated = 'N';
        
        * loop through all found person objects;
        if nobj1 > 0 then
            do;
                do i = 1 to nobj1 until (Password_Updated = 'Y');
                    nobj2 = metadata_getnobj("omsobj:Person?@Id contains '.'", i, uri_user);
                    rc1 = metadata_getattr(uri_user, "Name", name_user);
                    
                    * lookup person object;
                    if name_user = "&name." then
                        do;
                            * get corresponding Login-Account of Auth-Domain;
                            nobj3 = metadata_getnasn(uri_user, "Logins", 1, uri_login);

                            * loop through all Login-Accounts of particular user;
                            if nobj3 > 0 then
                                do;
                                    do j = 1 to nobj3;
                                        nobj4 = metadata_getnasn(uri_user, "Logins", j, uri_login);

                                        nobj5 = metadata_getnasn(uri_login, "Domain", 1, uri_domain);

                                        rc2 = metadata_getattr(uri_domain, "Name", name_domain);
                                        
                                        if name_domain = "&auth." then  /* update password for selected Auth-Domain*/
                                            do;
                                                rc3 = metadata_setattr(uri_login, "Password", "&pwd.");
                                                Password_Updated = 'Y';
                                            end;
                                    end;
                                end;
                        end;
                end;
            end;
    run;
%mend update_login_in_metada;

Re: Optimization of data step that updates Login Account in Metadata

FK21 — Thu, 20 Mar 2025 09:34:45 GMT

@SASKiwi

Thanks, this is definitely an improvement in logical terms!

However, the runtime wasn't really significantly lowered (24 seconds versus 23 seconds).

But I wonder, why is there such a big margin of error regarding the runtime? Yesterday I ran the exaxt same code and it took 47 seconds. Today it is only 24/23 seconds.

Of course, there could be different amounts of traffic regarding the server, but I do have the feeling, that the metadata handling is slow. I already ran the CLI admin tools to reorg and clean the metadata, but still it is very slow.

Is there another action I could pursue to speed up metadata access?

Re: Optimization of data step that updates Login Account in Metadata

andreas_lds — Thu, 20 Mar 2025 12:03:10 GMT

You could simplify your macro:

 %macro update_login_in_metada(name, pwd, auth);
    data _null_;
        length uri_login $ 250;
        uri_login = cats("omsobj:Login?Login[AssociatedIdentity/Person[@Name = '&name']]",
               "[Domain/AuthenticationDomain[@Name = '&auth']]"
            );
        rc = metadata_setattr(uri_login, "Password", "&pwd.");
        put rc=;
    run;
 %mend;

if rc ^= 0 then no user exists with a login in the given auth-domain.

Re: Optimization of data step that updates Login Account in Metadata

FK21 — Thu, 20 Mar 2025 14:39:26 GMT

@andreas_lds : this is awesome code! The only problem I have at the moment:

when executing this:

%update_login_in_metada(TESTUSR7, someString1234, SPDSAuth);

I get

rc=-3 (meaning that no objects match the URI)

However, I know, that this object with the given Authdomain exists as you can see in the scrennshot below:

So I guess, the omsobj-query string does not fully fit yet or am I missing something?

Re: Optimization of data step that updates Login Account in Metadata

andreas_lds — Thu, 20 Mar 2025 15:12:43 GMT

Interesting, omsobj-query should work, but this was a rather long day for me, so maybe this hides the error.

Some ideas to debug:

num_users = metadata_getnobj("omsobj:Login?Login[AssociatedIdentity/Person[@Name = '&name']]", 1, uri);

Returns the number of login-objects for the person. Using your screenshot, should be 3 in your case.

You should check the number of logins in the auth-domain, too:

logins_in_domain = metadata_getnobj("omsobj:Login?Login[Domain/AuthenticationDomain[@Name = '&auth']]", 1, uri);

Re: Optimization of data step that updates Login Account in Metadata

SASKiwi — Thu, 20 Mar 2025 19:14:35 GMT

@FK21 - The speed of reading a SAS metadata repository will likely depend on a number of factors. How big is your repository? The bigger it is the slower it will be to read it. Do you regularly compact its size? There is a tool in SAS Management Console for doing this. Also the repository is stored on your metadata server, but you are reading it from your application server so there is likely a lot of network traffic necessary to read it. And the busier your SAS installation is, the slower it will be to read the repository. How long does an after-hours read take?

Re: Optimization of data step that updates Login Account in Metadata

FK21 — Fri, 21 Mar 2025 08:17:33 GMT

@SASKiwi : In deed, there are several factors to consider. I am under the impression, though, that we do have an issue with our production metadata.

As you suggested, I ran the code after the usual business hours in two of our environments: production and test

Statistics of Test environment:

NOTE: DATA statement used (Total process time):
real time 2.58 seconds
user cpu time 0.01 seconds
system cpu time 0.01 seconds
memory 1132.59k
OS Memory 20892.00k
Timestamp 03/21/2025 06:45:31 AM
Step Count 5 Switch Count 164
Page Faults 20
Page Reclaims 281
Page Swaps 0
Voluntary Context Switches 975
Involuntary Context Switches 3
Block Input Operations 0
Block Output Operations 0

Statistics of Production environment:

NOTE: DATA statement used (Total process time):
real time 24.40 seconds
user cpu time 0.12 seconds
system cpu time 0.02 seconds
memory 7216.50k
OS Memory 27548.00k
Timestamp 03/21/2025 06:47:40 AM
Step Count 5 Switch Count 1364
Page Faults 0
Page Reclaims 1763
Page Swaps 0
Voluntary Context Switches 7944
Involuntary Context Switches 104
Block Input Operations 0
Block Output Operations 0

As you can see, in production it took ~12 times the amount of time it took in test.

Also, I compared the size of the <config-dir>/SASMeta/MetadataServer/MetadataRepositories/Foundation/ directories for PROD and TEST: 250 MB (PROD) vs. 248 MB (TEST).

Regarding the factor of network traffic, I also ran this code directly in a Workspaceserver session on the MetadataServer machine on PROD and TEST, respectively. The "deviation" of roughly 12 times remains!

Therefore, I would rule out the following factors:

size of Repo
traffic on network
load on the server

This only leaves me with the topic of "compacting the size of the metadata repo". What excatly do you mean with "tool in SMC"? Do you mean the Metadata Analysis/Repair Tools?

Re: Optimization of data step that updates Login Account in Metadata

FK21 — Fri, 21 Mar 2025 10:32:19 GMT

Hi @andreas_lds ,

I ran your debug lines within the macro and put them into the log:

uri_login=omsobj:Login?Login[AssociatedIdentity/Person[@Name = 'TESTUSER7']][Domain/AuthenticationDomain[@Name = 'SPDSAuth']]
num_users=-4
logins_in_domain=1

As you said, the expected value for "logins_in_domain" should equal "1", which it does. But the value of "num_users" indicates, that "n is out of range." according to the SAS documentation .

Does this translate to "TESTUSR7" cannot be found in the person objects of the metadata tree or is the syntax

Login?Login[AssociatedIdentity/Person[@Name = '&name']]

maybe skewed?

Re: Optimization of data step that updates Login Account in Metadata

andreas_lds — Fri, 21 Mar 2025 10:43:29 GMT

Strange. Looks as if TESTUSER7 is not the exact string in the name filed of a person-object.

Re: Optimization of data step that updates Login Account in Metadata

FK21 — Tue, 25 Mar 2025 10:26:15 GMT

Hi @andreas_lds : your code is correct!

It turned out, that I had forgotten to explicitly code the metadata connection information, i.e.:

options metaserver=<FQDN>
metaport=<Metaport number>
metaprotocol='bridge'
metauser='sasadm@saspw'
metapass='<encoded passwort>'
metarepository='Foundation'
metaconnect='NONE'
;

Therefore, it threw "rc = -3".

Thank's a lot for your help @SASKiwi , @andreas_lds

Re: Optimization of data step that updates Login Account in Metadata

SASKiwi — Tue, 25 Mar 2025 19:04:05 GMT

@FK21 - I'm curious to know if @andreas_lds 's version runs any faster than your version. I suspect it probably does as there is a lot less looping.

Re: Optimization of data step that updates Login Account in Metadata

SASKiwi — Fri, 28 Mar 2025 00:22:34 GMT

@FK21 - I was referring to the "Reorganize" option when you run a manual backup. This is supposed to compact the repository.

Re: Optimization of data step that updates Login Account in Metadata

FK21 — Mon, 31 Mar 2025 09:44:08 GMT

Hi @SASKiwi :

I ran my, as well as @andreas_lds 'code a couple of times (10 times each) in our production as well as our test environment.

Here are the average run times (standard deviation in paranthesis):

Average run times	FK21	andreas_lds
TEST	2.68 (0.08)	2.62 (0.45)
PROD	22.89 (0.39)	23.39 (0.5)

One striking observation, which I already wrote about in this post, is the fact, that in production it takes about 10 to 12 times longer than in test (we do a Reorg of the metadata once every week)!
Between the two code versions, there is merely any difference run time wise, which is surprising to me!
Aside from the fact that in TEST it runs roughly 10 times faster, it also runs more consistent, meaning lower standard deviations - for both code versions!

Re: Optimization of data step that updates Login Account in Metadata

SASKiwi — Mon, 31 Mar 2025 20:15:31 GMT

@FK21 - what's the size difference of the repository PROD versus TEST? A much larger PROD repository might explain some of the difference in processing time.

Re: Optimization of data step that updates Login Account in Metadata

FK21 — Mon, 07 Apr 2025 06:58:07 GMT

@SASKiwi : I also suspected, that the file size of the Foundation folder of the metadata repository could be part of the explanation, but there is only a difference of 2 MB

PROD:

TEST: