Sql queries extremley slow on .qea (SQLite)

[amacara1]

We switched from eapx to qea (64bit EA) format and SQL select queries seem extremely slow. It is much slower than same database exported as MySQL (and accessed via VPN).
Shall I take care of any special thing when using qea, please?

[Geert Bellekens]

Different databases respond differently to certain queries.
Sometimes you need to write things just a bit different. An example is replacing OR statements in a where clause by unions of different subqueries.

My (limited) experiments showed SQLite to be faster than .eapx though.

Can you share such a query and indicate what you consider slow?

Geert

[amacara1]

Here is a typical query taking less than 1 second on remote MySQL and 45 seconds locally (qea):

select t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE, t_object.Name
FROM (
    (((t_object as MetaObj) inner join t_objectproperties as MetaOpPhase on (MetaOpPhase.Object_ID = MetaObj.Object_ID and MetaObj.Name = 'MetaSql_#UserName#' and MetaOpPhase.Property = 'ReqPhase')) inner join t_objectproperties as MetaOpSwArchPkg on (MetaOpSwArchPkg.Object_ID = MetaObj.Object_ID and MetaOpSwArchPkg.Property = 'SwArchPkgGUID'))
    inner join t_object on (t_object.Phase = MetaOpPhase.Value))
WHERE
 (t_object.Stereotype like 'SwArchConsumptionObjective' and t_object.Status not like 'Obsolete') AND

t_object.Object_ID not in (select link.End_Object_ID from (t_connector link inner join t_object srcNeed on srcNeed.Object_ID = link.Start_Object_ID) where link.Connector_Type = 'Realisation' and srcNeed.Stereotype in ('SwArchNeed', 'SwArchConsumptionObjective') AND
 srcNeed.Package_ID in
  (select p.Package_ID
   from ((((((((t_package p
   left join t_package p2 on p.Parent_ID = p2.Package_ID )
   left join t_package p3 on p2.Parent_ID = p3.Package_ID )
   left join t_package p4 on p3.Parent_ID = p4.Package_ID )
   left join t_package p5 on p4.Parent_ID = p5.Package_ID )
   left join t_package p6 on p5.Parent_ID = p6.Package_ID )
   left join t_package p7 on p6.Parent_ID = p7.Package_ID )
   left join t_package p8 on p7.Parent_ID = p8.Package_ID )
   left join t_package p9 on p8.Parent_ID = p9.Package_ID )
   WHERE (p.ea_guid=MetaOpSwArchPkg.Value or p2.ea_guid=MetaOpSwArchPkg.Value or p3.ea_guid=MetaOpSwArchPkg.Value or p4.ea_guid=MetaOpSwArchPkg.Value or p5.ea_guid=MetaOpSwArchPkg.Value or p6.ea_guid in (MetaOpSwArchPkg.Value) or p7.ea_guid in (MetaOpSwArchPkg.Value) or p8.ea_guid in (MetaOpSwArchPkg.Value) or p9.ea_guid=MetaOpSwArchPkg.Value)
   )
) AND

 t_object.Package_ID in
 (select p.Package_ID
 from ((((((((t_package p
 left join t_package p2 on p.Parent_ID = p2.Package_ID )
 left join t_package p3 on p2.Parent_ID = p3.Package_ID )
 left join t_package p4 on p3.Parent_ID = p4.Package_ID )
 left join t_package p5 on p4.Parent_ID = p5.Package_ID )
 left join t_package p6 on p5.Parent_ID = p6.Package_ID )
 left join t_package p7 on p6.Parent_ID = p7.Package_ID )
 left join t_package p8 on p7.Parent_ID = p8.Package_ID )
 left join t_package p9 on p8.Parent_ID = p9.Package_ID )
 WHERE (p.ea_guid=MetaOpSwArchPkg.Value or p2.ea_guid=MetaOpSwArchPkg.Value or p3.ea_guid=MetaOpSwArchPkg.Value or p4.ea_guid=MetaOpSwArchPkg.Value or p5.ea_guid=MetaOpSwArchPkg.Value or p6.ea_guid=MetaOpSwArchPkg.Value or p7.ea_guid=MetaOpSwArchPkg.Value or p8.ea_guid=MetaOpSwArchPkg.Value or p9.ea_guid=MetaOpSwArchPkg.Value)
 )

[Geert Bellekens]

I can imagine that a query optimizer could have serious issues with a query like that.

I think you can rewrite it to do a left join from you MetaOpSwArchPkg to Package using something like

Code: [Select]

   left join t_package p on srcNeed.Package_ID
   left join t_package p2 on p.Parent_ID = p2.Package_ID
   left join t_package p3 on p2.Parent_ID = p3.Package_ID
   left join t_package p4 on p3.Parent_ID = p4.Package_ID
   left join t_package p5 on p4.Parent_ID = p5.Package_ID
   left join t_package p6 on p5.Parent_ID = p6.Package_ID
   left join t_package p7 on p6.Parent_ID = p7.Package_ID
   left join t_package p8 on p7.Parent_ID = p8.Package_ID
   left join t_package p9 on p8.Parent_ID = p9.Package_ID
  inner join t_package ArchPkg on ArchPkg.ea_guid = MetaOpSwArchPkg.Value
                                                and ArchPkg.Package_ID in (p.Package_ID, p2.Package_ID, p3.Package_ID,<up to to p9>....)It's stuff like this that can sometimes make a very big difference.

Geert

[amacara1]

I have reduces the problem to this query:
select t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE, t_object.Name
FROM (
    (((t_object as MetaObj) inner join t_objectproperties as MetaOpPhase on (MetaOpPhase.Object_ID = MetaObj.Object_ID and MetaObj.Name = 'MetaSql_#UserName#' and MetaOpPhase.Property = 'ReqPhase')) inner join t_objectproperties as MetaOpSwArchPkg on (MetaOpSwArchPkg.Object_ID = MetaObj.Object_ID and MetaOpSwArchPkg.Property = 'SwArchPkgGUID'))
    inner join t_object on (t_object.Phase = MetaOpPhase.Value))
WHERE
  t_object.Stereotype like 'SwArchNeed' AND
-- search for links of type realisation
t_object.Object_ID not in (select link.Start_Object_ID from (t_connector link inner join t_object srcNeed on srcNeed.Object_ID = link.End_Object_ID) where link.Connector_Type = 'Realisation' and
-- these are Needs from Arch package linked by realisation
(srcNeed.Stereotype in ('SwArchNeed') AND
 srcNeed.Package_ID in
  (select p.Package_ID
   from ((((((((t_package p
   left join t_package p2 on p.Parent_ID = p2.Package_ID )
   left join t_package p3 on p2.Parent_ID = p3.Package_ID )
   left join t_package p4 on p3.Parent_ID = p4.Package_ID )
   left join t_package p5 on p4.Parent_ID = p5.Package_ID )
   left join t_package p6 on p5.Parent_ID = p6.Package_ID )
   left join t_package p7 on p6.Parent_ID = p7.Package_ID )
   left join t_package p8 on p7.Parent_ID = p8.Package_ID )
   left join t_package p9 on p8.Parent_ID = p9.Package_ID )
   WHERE (p.ea_guid=MetaOpSwArchPkg.Value)
   )
)
)

If I replace MetaOpSwArchPkg.Value on last line with a fixed value like '123', then everything runs in a blink. Mind this MetaOpSwArchPkg.Value 'comes' from the very top select and it is used in the inner-most condition

[Geert Bellekens]

You might need to avoid subqueries like that.
Pretty sure that's where it's choking on.

Now it needs to build bottom subquery, that joins t_package, 10 times, for each of your tag values.

What I'm guessing is that something like MySQL can optimize that and internally rewrites it as one big join, whereas the optimizer of SQLite can't, and does this subquery for each and every result in the main query.

I think, if you rewrite it as one big query, this will perform just fine.

Geert

[amacara1]

Hoisting inner-most select worked like a charm, thank you!