querying iModel Elements properties

[GhisBntly]

Hello, in the context of our iTwin plugin for Unreal, we are currently querying some properties for all Elements of a displayed iModel using requests to the RPC interface (IModelReadRpcInterface-3.6.0-queryRows). This is because all the other data we need are streamed from dedicated services - 3DTiles, 4D schedules, etc. - so we don't have any other access to the iModel.

Currently we are doing 2 chains of requests, with these queries:

• to get parent-child relationships of iModel Elements: SELECT ECInstanceID, SourceECInstanceID FROM bis.ElementOwnsChildElements
• to find Elements originating from the same source Element: SELECT Element.Id, Identifier FROM bis.ExternalSourceAspect

I am now looking at retrieving the FederationGuid as well, and it would seem I can have all 3 properties with a single query: SELECT ECInstanceId,Parent.Id,FederationGuid FROM BisCore.Element?! Note that I am not familiar with SQL/ECSQL, being a purely C++ developer, I just found this out by looking at the BIM database table and extending on example commands on the itwinjs.org tutorial pages...
Do you think this approach is valid to replace the earlier 2 queries, or am I overlooking something?
Any drawback (in performance or otherwise) in querying BisCore.Element rather than the more specific ElementOwnsChildElements and ExternalSourceAspectTables?
Thanks for any advice,

[GhisBntly]

To clarify, I know the Identifier (from bis.ExternalSourceAspect) is not the same as the FederationGuid I am now looking to retrieve.
I just expect duplicated Elements to have the same FederationGuid, the way they have the same source Identifier, which would serve my purpose just as well.

[grigasp]

I just expect duplicated Elements to have the same FederationGuid, the way they have the same source Identifier

@diegoalexdiaz can you confirm this is the case?

[GhisBntly]

I can actually now answer that, because I checked in an iModel with known duplicates: duplicate Elements with the same Identifier do not have the FederationGuid, so I will need to keep querying Identifier.

[grigasp]

Hi, one piece of information that I'm missing is why you need the parent id - can you explain that?

A couple of query-related comments:

• For navigation property - based relationships it's more performant to query the class that has the navigation property, than the relationship. So instead of SELECT ECInstanceID, SourceECInstanceID FROM bis.ElementOwnsChildElements, it's better to do SELECT ECInstanceId, Parent.Id FROM bis.Element.

• You can select element aspects' information in the same query, too:

  SELECT e.ECInstanceId, e.Parent.Id, a.Identifier
  FROM bis.Element e
  LEFT JOIN bis.ExternalSourceAspect a ON a.Element.Id = e.ECInstanceId

[GhisBntly]

Thanks for the suggestions, we are indeed aware that our implemented solution is heavy-handed. In our case, I think we would need to traverse from task to geometric elements but, with tens of thousands of tasks and 770K animation bindings in one of the largest project I am testing, I don't know whether it would really be faster...

I think the current loading times are acceptable for the time being because it happens only once per project/imodel/changeset (everything is cached locally) and we have to download the full 4D schedule up-front as well anyway: we had started by querying the 4D API on demand using "spatial" filtering (actually, filtering by Element IDs found in the received geometries), but it ended up being much, much slower than downloading everything in batch requests.
So the way to reduce initial loading times is a broader undertaking that I'm not sure I can discuss here, but when it happens we probably won't need any (or that much) iModel data.

[GhisBntly]

I should also mention that the crux of the issue is that, to render the schedule properly for the first time (after which point we can and do indeed process tasks incrementally as the schedule time changes), we need to consider all tasks, because Elements affected by a task may have their aspect changed before/after the task has started/ended. This makes it impossible to think "on-demand" here. The only way out is to not need this whole correlation work at all.

[grigasp]

Just wanted to make sure you consider performance when implementing your stuff. From your comments it sounds like you do - great!

[diegoalexdiaz]

Re: I just expect duplicated Elements to have the same FederationGuid, the way they have the same source Identifier

BIS offers multiple ways to capture Element-identity in an iModel.

• ElementId: "Internal" Id of an Element in an iModel, exposed as ECInstanceId in ECSQL. It doesn't have any business-meaning or mapping outside of an iModel.
• FederationGuid: Used to federate (i.e. correlate) an Element with other Repositories capturing the same Guid for the same "Entity" that an Element represents. It is ensured to be unique in an iModel (i.e. unique-index on the associated db column).
• Code: Used to capture a Human-friendly Identifier that captures Business-meaning and it is unique in some context defined by the Business or Application creating them. It sounds you guys are not basing any logic on Codes.
• ExternalSource Identifier: Elements that were synchronized from an External Source carry their Provenance via an Aspect called "ExternalSourceAspect". Such Aspect has a string-property called "Identifier" that the application synchronizing the external data uses to capture an Id from the external repository that such app (i.e. typically a Connector) uses to compare an Element with the corresponding "external record" it came from (to detect changes) during subsequent runs. Each app (Connector) stores something different in that "Identifier" property - thus, an "External Source Identifier" is only meaningful in context of the External DataSource it is associated with.

ElementOwnsChildElements vs. Parent.Id

BIS is a conceptual data-model that abstracts away how data is laid-out/persisted in terms of the physical tables in the database. ECSQL queries are based on BIS classes/properties/relationships and when they are executed, they are translated into raw SQL targeting the corresponding Physical db tables. Thus, targeting the "ElementOwnsChildElements" BIS relationship class or the "Parent.Id" BIS property in your ECSQL will probably lead to very similar raw SQL since both BIS concepts map to the same physical db-table/column.

Having said that, as with any database, it is more efficient to bulk-load data in fewer "chunky" queries than several "chatty" queries. Thus, the single query that @grigasp suggested is more efficient than reading the same information with two or three queries.

Re: table-scans

It sounds your query(ies) don't have any WHERE criteria filtering out what you get back. If your use-case (still a bit fuzzy to me) requires you to traverse all Elements in a single pass in order to cache the pieces that you need, that's fine. However, if your use-case involves any "criteria" or "batches of information", you can provide it as part of your ECSQL query and it will scale much better than if you retrieved everything and try to apply the same "filter criteria" by hand on your side.

cc: @khanaffan in case there are any other tips to consider.

[GhisBntly]

Thanks a lot for the detailed answer!
We use the ExternalSource Identifier indeed to identify duplicate Elements produced by the 4D schedule synchronization process in some particular cases (overlapping task assignments for the same Element, for example). Now I realize it was actually obvious those would have distinct FederationGuid, by definition.

We are not filtering anything because we need information on all geometric Elements in the iModel and all Elements which have geometric Elements in their "children subtree", in order to correlate with the schedule's animation bindings (which alter the visual aspect of the geometric elements depending on the current schedule time).
I don't know whether it would be worth adding a WHERE clause to filter out any unneeded Elements, nor how to formulate such a filter. Probably not, considering that this is not the only aspect of the current solution that is non scalable, as explained above (having to download the full 4D schedule up-front).