SQL Server statement takes forever while running instantly in Oracle Please help me interpreting this statement and plan:

https://www.brentozar.com/pastetheplan/?id=Bysy6YtEV

We migrated from Oracle to SQL Server and we have some quite strange behaviours. It might be related to a problem during migration.

I found it hard to interpret the execution plans. Both environments should have the same structure and indexes. Statistics should be up to date.
Settings in SQL Server: 


Create Auto Statistics Enabled
optimize for Ad Hoc Queries = true 
Snapshot Isolation enabled
Max Parallel = 4 
Threshold 50 


DB is 600 Gb in size, 16 cores, 160 Gb memory.



The query:

SELECT
    COUNT ( t_01.rsecondary_objectu ) AS selectExpr
FROM
    PIMANRELATION t_01 ,
    PITEM t_02 ,
    PITEMREVISION t_03
WHERE
    (   (   (   ( t_01.rprimary_objectu = t_02.puid )
            OR  ( t_01.rprimary_objectu = t_03.puid )
            )
       AND
            ( t_01.rrelation_typeu = 'w8INy241VJFL2B' )
        )
   AND  t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
    )


Oracle



We found out that the issue is / was related and depend on the data. If I select a different item to copy in the GUI (it's actually a copy thing and any how the app does these statements) it works instant. The query then once working fine looks a little different: https://www.brentozar.com/pastetheplan/?id=SJo-h2c44

SELECT  COUNT ( t_01.rsecondary_objectu ) AS selectExpr FROM PIMANRELATION t_01 , PITEM t_02 , PITEMREVISION t_03 WHERE ( ( ( ( t_01.rprimary_objectu = t_02.puid ) OR ( t_01.rprimary_objectu = t_03.puid ) )  AND ( t_01.rrelation_typeu = 'w8INy241VJFL2B'  ) ) AND t_01.rsecondary_objectu IN  ('wBHZpD0uVJFL2B' , 'V2PlBGlAVJFL2B' , 'lnHlBGlAVJFL2B'  ) )


SQl Server seems to completely struggle with the one I mentioned before (running infinite) while providing the second one almost instantly. That's so crazy weird. Like a buggy product.



I don't have influence on the statements as they are produced by the app.

CE 110 / 70 results in the same plan. Even if disable all custom indexes and only keep the ones suggested by the app it behaves the same. All primary keys are clustered indexes. But maybe something messed during our migration. But it's quite strange. Most stuff runs fine, but the query at topic is extreme. I let it run für 45 minutes in SQL Server. It simply does not complete.



An other example. Our Prod Environment gets unuable once the highest rated index is created:

Index:
CREATE INDEX EN_PIPRELEASESTATUS_1 ON [TCEUP01].[dbo].[PRELEASESTATUS] ([pname], [pdate_released]) INCLUDE ([puid])

Results in this query:
https://pastebin.com/Ax3qTUjd
===>Took 137.142 seconds

our Test Environment, which should be the same, behaves differently:
https://pastebin.com/0PTZTJpr
===>Took 3.884 seconds

the problematic plan looks like this:
https://www.brentozar.com/pastetheplan/?id=Skvy0qRNE

and it seems it can run like infinite.
The Oracle optimizer uses OR Expansion to improve efficiency of the query. Quoting from the documentation:


  In OR expansion, the optimizer transforms a query with a WHERE clause
  containing OR operators into a query that uses the UNION ALL operator.
  
  The database can perform OR expansion for various reasons. For
  example, it may enable more efficient access paths or alternative join
  methods that avoid Cartesian products.


You can think of the new query as being written like this:

SELECT
(
    SELECT
        COUNT ( t_01.rsecondary_objectu ) AS selectExpr
    FROM
        PIMANRELATION t_01
        INNER JOIN PITEMREVISION t_03 ON t_01.rprimary_objectu = t_03.puid
        CROSS JOIN PITEM t_02    
    WHERE
    t_01.rrelation_typeu = 'w8INy241VJFL2B'
    AND t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
) 
+ 
(  
    SELECT
        COUNT ( t_01.rsecondary_objectu ) AS selectExpr
    FROM
        PIMANRELATION t_01
        INNER JOIN PITEM t_02 ON t_01.rprimary_objectu = t_02.puid
        CROSS JOIN PITEMREVISION t_03
    WHERE
    t_01.rrelation_typeu = 'w8INy241VJFL2B'
    AND t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
    AND LNNVL(t_01.rprimary_objectu = t_03.puid)
)
from dual;


Now there is an equality condition for both parts of the query, so Oracle can use indexes to perform an efficient nested loop join for both. It still needs to do a cross join for both parts of the query, but the size of the intermediate result sets is significantly reduced compared to doing two cross joins in the same query. For example, if PIMANRELATION has 1 relevant row and PITEMREVISION and PITEM both have a million rows then you get a trillion rows if you cross join them together. However, if you split the queries up then you only end up with a million rows for both.

The SQL Server query optimizer has a rule that can transform OR into UNION ALL: SelToIdxStrategy. There's no documentation for this and the only reference I could find is this answer. However, that rule will not be applied in this context. Instead, you get two cross joins which can only be implemented through nested loop joins. For each relevant row in PIMANRELATION, SQL Server will cross join to all rows in PITEM, then cross join to all rows in PITEMREVISION, and will finally filter out rows after that. You can easily end up with trillions of rows getting filtered down.

I have bad news for you. If you truly cannot change any part of the query text and you need that query to perform well then SQL Server probably isn't the right platform for your application. Databases have different strengths and weaknesses and you may need to change your queries to accommodate those differences.