Very Poor "WHERE" performance with Oracle and a "OR" operator on joined table

11-22-2022 03:28 AM
Regular Contributor II

We are moving our services from Desktop/Server to Pro/Portal.  Publishing in Pro 3.0 to Portal/Server 10.9

I have layers with queries like

table2.field1 in ('01', '02', '04', '03') And table2.field2 IN ('01', '02', '03', '05', '04', '06', '07') And (table2.field3 IS NOT NULL Or table2.field3 IN ('AB', 'AC', 'AD', 'AD', 'RC'))

Table2 is a Join on the primary key from table1 and is fully indexed.

This works fine in Pro and Desktop and worked fine in Desktop published to Server 10.8

In the new world the layer draws slow and almost hits the 60 second time out when an ID task is sent via a web request.

I have narrowed the issue to the OR part - and then ONLY when the SQL is looking at the related table.  When the SQL looks at just fields in the parent table or when there is no link, or when there is no 'OR'  everything works

The data live in an Oracle 19C Enterprise database and the server is using matching 64bit client

Any ideas?



0 Kudos
0 Replies