massive performance issues

we are fighting more or less since version 2.9 with the performance of our systems. We are completely unclear if indexes or statistics are really used. 

sometimes a query like this takes takes around 500 ms, sometimes it takes 20 seconds 

select distinct o from ObjectNode o join o.properties p1 join o.properties p10 join o.properties p20 join o.properties p21 where o.parentNode.classIdentifier = '(PD)' and o.classIdentifier = '(OP)' and (o.linkedObjects.classIdentifier = '(TC)' and o.linkedObjects.objectNameUpper = 'ASSEMBLY-ENGINE' and o.linkedObjects.type = 1 and o.linkedObjects.state = 0) and (((p1.name = 'scheduledSlot' and p1.doubleValue <= 2898)) and (p10.name = 'scheduledState' and p10.doubleValue = 2) and (o.properties.name = 'feedbackState' and o.properties.doubleValue != 13) and (p21.name = 'globalFeedback' and p21.doubleValue = 0) and (p20.name = 'roughPlanning' and p20.doubleValue = 0))
  

attached you find  

- odb20250725.log 

>>> this is after doctor and running statistics. You can see that suddenly on starting some index is cleared (doctor should correctly create that ???) and then there is a mixture of creating indexes and statistics 

- ISAR-before-doctor.odb.zip, the initial database   

- ISAR-after-doctor.odb.zip, the database after doctor 

- ISAR-after-statistics.odb.zip, the database after statistics 

https://www.dropbox.com/scl/fi/gqfubwbpnqmmi2lq0cjnd/odb20250725.log?rlkey=v68xvvd7eiovhtbstrzcj9vjs&dl=0 

https://www.dropbox.com/scl/fi/uk183qekfemvjogjv2ys2/ISAR-after-doctor.odb.zip?rlkey=x84wu8ewtwz4en1wvjddwomze&dl=0 

https://www.dropbox.com/scl/fi/29j4w5mym7ydbyavfpf9m/ISAR-after-statistics.odb.zip?rlkey=vhhn84apfz976cjdbg1deh3xd&dl=0 

https://www.dropbox.com/scl/fi/0q04cot7u50s7nghafq3r/ISAR-before-doctor.odb.zip?rlkey=s2en8w2ok7h0rbavago41pbds&dl=0

In order to examine the issue, please provide a database on which that query, when running in the the Explorer, is very slow. Is any or all the databases that you made available can demonstrate the slow query?

see all the links that we provided in #1, also the description and logs of all these problems

Sure, but you wrote:

> sometimes a query like this takes takes around 500 ms, sometimes it takes 20 seconds

Can you confirm that the provided databases demonstrate the ~20 seconds in a consistent way? It is just not clear from post #1.

the problem is

- we do not understand if the statistics/indexes are really used (see problem with the deleting of indexes and mixed index/statistics creating what can be seen in the log)

- the behaviour 500 ms versus 20 seconds is completely random and not predictable/reproducable

> we do not understand if the statistics/indexes are really used (see problem with the deleting of indexes and mixed index/statistics creating what can be seen in the log)

The log file indicates that index activations and statistics update completed successfully. There are many messages in the log probably due to setting logging to verbose in the configuration.

If you experience issues you may try: (1) Repairing the database with the Doctor - that will rebuild all indexes offline. (2) Running Statistics Update on the result database - as indexes are expected to be up to date you should not see mixed messages.

You may also run statistics update again after all the indexed have been built and check if it helps.

To check if indexes are used - you can run the query in the Explorer and look at the query plan that was executed. It should show you whether an index was used or not. You can also get that information as XML in the log.

> the behaviour 500 ms versus 20 seconds is completely random and not predictable/reproducable

This is very strange and more information is needed. For the same query structure on the same database it is unexpected that different query plans will be used. So this may also indicate a different issue. You can track query execution in the log and see for differences, if any. Maybe the issue is not related to the query plans but to something else. If you run your application in a container - check the link that was mentioned in a reply to your question in another thread. We treat every reported issue very seriously and would like to solve this one as soon as possible, but if the databases that you made available do not demonstrate the issue (i.e. for query execution speed), we will need to look elsewhere.