optimizing a query

[GhisBntly]

I am trying to optimize the way I am querying Elements data by checking whether the tables involved are indexed by the key property I am using to JOIN them, but copilot-suggested instructions to determine how tables are indexed do not seem to work (using the imodelconsole):

• pragma help confirmed that I cannot use pragma index_list,
• and EXPLAIN QUERY PLAN SELECT e.ECInstanceId FROM bis.Element e gets me "Error: Failed to parse ECSQL 'select * from (EXPLAIN QUERY PLAN SELECT e.ECInstanceId FROM bis.Element e ) limit :sys_ecdb_count offset :sys_ecdb_offset': syntax error"

My current query:

SELECT e.ECInstanceId, b.BBoxLow, b.BBoxHigh, e.Parent.Id, e.FederationGuid, a.Identifier
FROM bis.Element e
LEFT JOIN bis.ExternalSourceAspect a ON a.Element.Id = e.ECInstanceId
LEFT JOIN bis.GeometricElement3d b ON b.ECInstanceId = e.ECInstanceId

Note: I'm aware it doesn't scale, but for the moment I really need the data for all Elements of the iModel. Restricting the query(-ies) with WHERE clauses is another lead I can pursue but this would add several difficulties in our code logic.

Thanks for advising,

[ben-polinsky]

@khanaffan @MichaelSwigerAtBentley

[khanaffan]

You can use PRAGMA ### explain_query

But just looking at your query I can see it's just a SCAN over bis.Element generally the largest table. What do you want to accomplish with the query? I see you want source aspect for geom3d but I am not sure why you have bis.Element. Do you intent to read all element of all type but grab additional data for geometric elements?

SELECT g.ECInstanceId, g.BBoxLow, g.BBoxHigh, g.Parent.Id, g.FederationGuid, a.Identifier
FROM bis.GeometricElement3d g 
JOIN bis.ExternalSourceAspect a ON a.Element.Id = e.ECInstanceId

[GhisBntly]

Thanks @khanaffan ,
When we designed the query I guess we did not realize there could be many non-geometrical elements...

Besides bboxes, FedGUIDs and "Source IDs", we need the whole parent-child hierarchy in order to interpret the data served from the 4D api, and I don't know whether all parent/ancester elements are necessarily geometrical elements? If so, your version of the query would indeed suffice!

[khanaffan]

Note that BisCore classes is polymorphic and so might want to look at schema

To test your queries you can use https://imodelconsole.bentley.com

Click this to get to schema explorer

Here you can check hierarchy

[GhisBntly]

Do I understand correctly that the Parent of a GeometricElement3d can be any type of Element, not necessarily another GeometricElement3d?
In that case, I will need to get the Parent-ing hierarchy for all Elements, as in my original query, unless I can have some sort of guarantee that SYNCHRO schedules can only assign GeometricElement3d to tasks (at least those I care for, ie. that are visually "animated" with an appearance profile). I think I can test that in a project I have where I know non-leaf Elements are assigned.

[MichaelSwigerAtBentley]

I believe you are right in that the parent of a GeometricElement3d can be a different class of element - the only restriction by default is that the parent and child reside in the same model. So I believe you would need to look at the acceptable classes for the model the element resides in.

[DanRod1999]

Do I understand correctly that the Parent of a GeometricElement3d can be any type of Element, not necessarily another GeometricElement3d

According to schema explorer, bis.Element is the parent of GeometricElement3d, so I think the parent could possibly be any other bis.Element type
https://imodelschemaeditor.bentley.com/?stage=browse&elementtype=entityclass&id=BisCore.GeometricElement3d