• State Not Answered
  • Replies 6 replies
  • Subscribers 93 subscribers
  • Views 1613 views
  • Users 0 members are here
Options
Related

DB Connector Object Lookup Returning Large result Set

gwickert
over 2 years ago

Hi All,

Running 8.1.4 using the DB connector to connect UNSAccountBInUNSGroupB to a target table containing a roleid and userid. When trying to provision a record from OneIM to the target, I noticed it would take almost 15 minutes for the AdHoc prejection to complete.

Upon further investigation, I found in the trace logs that the following statement is executed, presumably when trying to check for existence of the record in the target DB:

2021-06-10 14:31:46.3557 DEBUG (SqlLog SF_IAM@NZCHCGLOTABIZ1.fh.local) : select "UserId", "RoleId" from "dbo"."User_IAM_RoleMember_View" where (("UserId"=N'0051O00000ClpyAQAR') or ("RoleId"=N'a7V0n0000000os1EAA'))
2021-06-10 14:31:48.7845 TRACE (SqlLog Database Session GC) : Cleanup physical connection pool
2021-06-10 14:31:48.7845 TRACE (SqlLog Database Session GC) : 0 connections disposed.
2021-06-10 14:31:48.7845 TRACE (SqlLog Database Session GC) : Available = 4
2021-06-10 14:31:55.9015 TRACE (SqlLog Database Session GC) : Cleanup physical connection pool
2021-06-10 14:31:55.9015 TRACE (SqlLog Database Session GC) : 0 connections disposed.
2021-06-10 14:31:55.9015 TRACE (SqlLog Database Session GC) : Available = 1
2021-06-10 14:31:55.9484 DEBUG (SqlLog SF_IAM@NZCHCGLOTABIZ1.fh.local) : Done in 9586ms

EDIT: Running the query in SSMS shows that it is the query taking a long time to return - the 'Done in xxxxms' message seems wrong. Either way, the query generated it not correct.

The problem here is that the query uses OR and not AND when looking up the object.

I'm stumped as to why it would be searching like this - why would it not use AND to find a single unique User_IAM_RoleMember_View record?

Matching rules I figured was the obvious place to start - I've tried:

  • Logical expression rule - using the mapping for RoleID and UserId - i.e. vrtUNSAccountB___UserId AND vrtUNSGroupB___RoleId - doesn't seem to make a difference.
  • Using a value comparison matching rule - built a virtual property on either side, concatenating the userid/roleid values (+ translating the UIDs on OneIM side to the roleid/userid values) - still no luck.

All of that said, I'm fairly confident that the matching rule is correct - both ways I would have though were suitable.

What am I missing here? How do I make the above query generated by the connector do a lookup using UserId AND RoleId rather than UserId OR RoleId like it is in the above log?

Glen. 

Parents
  • 0 over 2 years ago

    I think you need to create a column group in the database connector that combines the columns UserID and RoleID to one single unique key. 

  • 0 over 2 years ago in reply to Markus Weiss-Ehlers

    Hi Markus,

    Apologies for the delay here - I have already tried what you mentioned and it gives me the same generated query. I.e. In the DB connection wizard where you can set primary keys for tables, I made a synthentic compound key based on roleid and userid.

    Looks like this may be a bug. To get around this I had the DBA team improve performance of the query (from 15 mins down to 5 secs) and it all works fine - functionally, anyway. It does what I need but it does it in a poor way. It gets all possible matches using the OR logic (for a large role, up to 10k records), then uses the matching rules in memory on the OneIM side (I can see in the trace it doing this) to determine if a userid+roleid record is in the large result set or not.

    Glen.

Reply
  • 0 over 2 years ago in reply to Markus Weiss-Ehlers

    Hi Markus,

    Apologies for the delay here - I have already tried what you mentioned and it gives me the same generated query. I.e. In the DB connection wizard where you can set primary keys for tables, I made a synthentic compound key based on roleid and userid.

    Looks like this may be a bug. To get around this I had the DBA team improve performance of the query (from 15 mins down to 5 secs) and it all works fine - functionally, anyway. It does what I need but it does it in a poor way. It gets all possible matches using the OR logic (for a large role, up to 10k records), then uses the matching rules in memory on the OneIM side (I can see in the trace it doing this) to determine if a userid+roleid record is in the large result set or not.

    Glen.

Children