Why does this SQL statement blocks my EA?

[siaccarino]

Intention: Get all objects with a tag XYZ or that are connected to objects with a tag XYZ

SELECT DISTINCT t_object.Object_ID
FROM t_objectproperties,t_object,t_connector
WHERE
   t_objectproperties.Property = "MustHaveThisTag"
AND
   ((t_connector.Start_Object_ID = t_objectproperties.Object_ID AND t_object.Object_ID = t_connector.Start_Object_ID)
OR
   (t_connector.End_Object_ID = t_objectproperties.Object_ID AND t_object.Object_ID = t_connector.End_Object_ID))

[Geert Bellekens]

Because of they way you are writing the query.

Write you query using the join syntax and you'll get a whole lot further.
This query selects all objects that have the tagged value, and a connector.

Code: [Select]

SELECT DISTINCT o.Object_ID
FROM ((t_objectproperties tv
inner join t_object o on o.Object_ID = tv.Object_ID)
inner join t_connector c on (o.Object_ID = c.Start_Object_ID or o.Object_ID = c.End_Object_ID))
WHERE
tv.Property = 'MustHaveThisTag'
Geert

[siaccarino]

This query does not block EA - but it returns only objects that have the tag - and not the objects that are connected to objects with this tag.

The query shall fake somehow "tag inheritance" (EA.Element.TaggedValuesEx)

[Geert Bellekens]

This query does not block EA - but it returns only objects that have the tag - and not the objects that are connected to objects with this tag.

The query shall fake somehow "tag inheritance"

I know, that is exactly what I wrote:

This query selects all objects that have the tagged value, and a connector.

but it should give you a place to start from.

If you would like me to write the whole query for you according to your requirements, you can always hire me as a consultant.

Geert

[Mauricio Moya (Arquesoft)]

In my experience, EA locks when the query uses too much memory (and it is worse if your connection is via Cloud Server), but in most cases you just have to wait for the response. Just be patient. For certain size of the memory consumption in the database server, the Cloud Server could throws an error and no data is returned to the client.

The best way to solve it is, as they said in the previous posts, to optimize the query.