Query too long to execute or server is too busy

[showgata-code]

We recently migrated from 3.7 to 4.2.3. We had this large ecsql query that was previously supported.

async function getAspectsAttributes(iModel) {
    const aspectsAttributesQuery = 
        "SELECT DISTINCT a.classname AS classname,
        a.classlabel AS classlabel,
        b.name AS Name,
        b.displayLabel AS AttributeDisplayLabel
    FROM (
            SELECT DISTINCT ec_classname(q.ecclassid) AS classname,
                scdef.displayLabel AS schemaDisplayLabel,
                def.displaylabel AS classlabel
            FROM biscore.Element c
                JOIN (
                    SELECT eou.sourceecinstanceid,
                        eu.ecclassid
                    FROM bis.elementownsuniqueaspect eou
                        JOIN bis.elementuniqueaspect eu ON eou.ecinstanceid = eu.ecinstanceid
                ) AS q ON q.sourceecinstanceid = c.ecinstanceid
                JOIN ecdbmeta.ecclassdef def ON def.name = substr(
                    ec_classname(q.ecclassid),
                    instr(ec_classname(q.ecclassid), ':') + 1,
                    length(ec_classname(q.ecclassid))
                )
                JOIN ecdbmeta.ecschemadef scdef ON scdef.name = substr(
                    ec_classname(q.ecclassid),
                    0,
                    instr(ec_classname(q.ecclassid), ':')
                )
            UNION
            SELECT DISTINCT ec_classname(q.ecclassid) AS classname,
                scdef.displayLabel AS schemaDisplayLabel,
                def.displaylabel AS classlabel
            FROM biscore.Element c
                JOIN (
                    SELECT eom.sourceecinstanceid,
                        em.ecclassid
                    FROM bis.elementownsmultiaspects eom
                        JOIN bis.elementmultiaspect em ON eom.ecinstanceid = em.ecinstanceid
                ) AS q ON q.sourceecinstanceid = c.ecinstanceid
                JOIN ecdbmeta.ecclassdef def ON def.name = substr(
                    ec_classname(q.ecclassid),
                    instr(ec_classname(q.ecclassid), ':') + 1,
                    length(ec_classname(q.ecclassid))
                )
                JOIN ecdbmeta.ecschemadef scdef ON scdef.name = substr(
                    ec_classname(q.ecclassid),
                    0,
                    instr(ec_classname(q.ecclassid), ':')
                )
        ) a
        JOIN (
            SELECT p.Name AS name,
                propc.Name AS classname,
                sch.Name AS schema,
                p.DisplayLabel AS displayLabel,
                p.Description AS description
            FROM ECDbMeta.ECClassDef c
                JOIN ECDbMeta.ClassHasAllBaseClasses abc ON abc.SourceECInstanceId = c.ECInstanceId
                JOIN ECDbMeta.ECPropertyDef p ON abc.TargetECInstanceId = p.Class.Id
                JOIN ECDbMeta.ECClassDef propc ON propc.ECInstanceId = p.Class.id
                JOIN ECDbMeta.ECSchemaDef sch ON sch.ECInstanceId = propc.Schema.Id
                JOIN ECDbMeta.ECSchemaDef AS clsch ON clsch.ECInstanceId = c.Schema.Id
        ) b ON b.classname = substr(
            a.classname,
            instr(a.classname, ':') + 1,
            length(a.classname)
        )";
    const attributesData = iModel.query(aspectsAttributesQuery, undefined, { rowFormat: QueryRowFormat.UseJsPropertyNames });
    const aspectsAttributes = [];
    for await (const attribute of attributesData) {
        const displayLabel = `${attribute.attributeDisplayLabel ?? attribute.name} | ${attribute.classlabel ?? attribute.classname.split(":"[1])}`;
        const updatedAttribute = {...attribute, displayLabel, fromAspect: true};
        aspectsAttributes.push(updatedAttribute);
    }
    return aspectsAttributes;
}

After migrating, to 4,2 and updating "iModelConnection.query" to "iModelConnection.createQueryReader" we get 504 timeouts and "Query too long to execute or server is too busy" error. Tried setting the quota: { time: 10000 }, didn't help. Is there any configuration/properties we need to add to support such queries or can it be a regression?

[grigasp]

I can't say why it worked in 3.7.x and stopped working in 4.x - tagging @khanaffan for that. But here are a few changes that you can try making to the query to make it more performant:

1. Instead of joining aspects to elements through a relationship, you can use navigation property - from my experience that makes a much more efficient query. So instead of:

   JOIN (
       SELECT eou.sourceecinstanceid,
           eu.ecclassid
       FROM bis.elementownsuniqueaspect eou
           JOIN bis.elementuniqueaspect eu ON eou.ecinstanceid = eu.ecinstanceid
   ) AS q ON q.sourceecinstanceid = c.ecinstanceid

   Try this:

   FROM biscore.Element c
   JOIN bis.elementuniqueaspect q ON q.Element.Id = c.ECInstanceId

   The above change can also be applied to multi aspects.

2. Looks like you're joining ECClassDef and ECSchemaDef to get their display labels. Why are you joining them by name? I think you can replace the following:

   JOIN ecdbmeta.ecclassdef def ON def.name = substr(
       ec_classname(q.ecclassid),
       instr(ec_classname(q.ecclassid), ':') + 1,
       length(ec_classname(q.ecclassid))
   )
   JOIN ecdbmeta.ecschemadef scdef ON scdef.name = substr(
       ec_classname(q.ecclassid),
       0,
       instr(ec_classname(q.ecclassid), ':')
   )

   with this:

   JOIN ecdbmeta.ecclassdef def ON def.ECInstanceId = q.ecclassid
   JOIN ecdbmeta.ecschemadef scdef ON scdef.ECInstanceId = def.Schema.Id

   The above suggestion also applies to the last part of the query where you're also joining b with a on class name rather than id.

3. I see a UNION that you use for unique and multi aspects. It doesn't look like the results between the two queries could intersect, so by using UNION you're unnecessarily making SQLite check for those intersections. Instead, I recommend switching to UNION ALL.

[khanaffan]

By looking at the query it has a lot of unnecessary joins and string processes. I read the query as follows

1. Get Classes of all the Aspects (unique + multi)
2. Get all base classes of classes returned in step 1
3. Geta all the properties of classes in step 2
4. return className, classLabel, propertyName & propertyLabel.

If the above is correct then you can reduce the query to following. It is still a bit slower you might want to exclude BisCore.ExternalSourceAspect

SELECT  
	c.Name AS className,
	c.DisplayLabel AS classLabel,		
	p.Name AS Name,
	p.DisplayLabel AS AttributeDisplayLabel
FROM ECDbMeta.ECClassDef c
	JOIN ECDbMeta.ECPropertyDef p ON c.ECInstanceId = p.Class.Id
JOIN (
	SELECT DISTINCT cac.TargetECInstanceId classId FROM ECDbMeta.ClassHasAllBaseClasses cac
	WHERE cac.SourceECInstanceId IN (SELECT DISTINCT ECClassId FROM [BisCore].[ElementAspect])
) ea ON ea.classId = c.ECInstanceId 

A much faster version of query with the same result will be a query that excludes BisCore.ExternalSourceAspect and takes UNION with it.

SELECT  
	c.Name AS className,
	c.DisplayLabel AS classLabel,		
	p.Name AS Name,
	p.DisplayLabel AS AttributeDisplayLabel
FROM ECDbMeta.ECClassDef c
	JOIN ECDbMeta.ECPropertyDef p ON c.ECInstanceId = p.Class.Id
JOIN (
	SELECT DISTINCT cac.TargetECInstanceId classId FROM ECDbMeta.ClassHasAllBaseClasses cac
	WHERE cac.SourceECInstanceId IN (SELECT DISTINCT ECClassId FROM [BisCore].[ElementAspect] WHERE ECClassId <> ec_classId('BisCore:ExternalSourceAspect'))
	UNION
	SELECT ec_classId('BisCore:ExternalSourceAspect')
) ea ON ea.classId = c.ECInstanceId 

[ColinKerr]

@khanaffan's answer is great but I wanted to mention that ec_classname can return just the schema name if that is what you want. See the documentation here: https://www.itwinjs.org/learning/ecsqltutorial/builtinfunctions/#ec_classnameecclassidformat-string--format-id