SQL Server statement takes forever while running instantly in Oracle

Question

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.

Answer 1

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.

Answer 2

This is a suggestion based solely on the query structure:

Before you start a goose chase of execution plans, table statistics and histograms, cardinality estimates, isolation levels, memory setting and many other possible reasons of the issue and ways to solve it, consider that it may be a bug in the application that produces the query.

My reasoning is simple: if it looks like junk and produced by an ORM, then it's likely junk.

I suggest you check:

• if the query runs exactly the same in the original Oracle database or it is different there (and the ORM/application) that produces it changes it slightly or more when the target is a SQL Server database.

• what tests there are that the query is consistent with the business logic / requirements that is meant to apply. Does the application have such tests and do they pass successfully in both (Oracle and SQL Server) environments?

My point is that there is no point in chasing a performance problem before establishing that there is no correctness problem with the queries.

---

In detail, the query makes very little sense. The OR condition - which is the only connecting filter for the secondary (PITEM and PITEMREVISION) tables - essentially introduces a cross join (which luckily for you had been kicking some optimization feature in Oracle but doesn't do in SQL Server. See Joe Obbish's answer for a detailed explanation of what happens).
To make it clearer, consider the following query which is equivalent to yours:

WITH
  t_01 AS
  ( SELECT rprimary_objectu
    FROM PIMANRELATION 
    WHERE rrelation_typeu = 'w8INy241VJFL2B'
      AND rsecondary_objectu = '2yLJkWqiVJFL2B'
  ),
  count_items AS
  ( SELECT COUNT(*) AS a
    FROM t_01
         JOIN PITEM t_02
         ON t_01.rprimary_objectu = t_02.puid
  ),
  count_revisions AS 
  ( SELECT COUNT(*) AS b
    FROM t_01
         JOIN PITEMREVISION t_03
         ON t_01.rprimary_objectu = t_03.puid
  ),
  count_all_items AS
  ( SELECT COUNT(*) AS aa
    FROM PITEM
  ),
  count_all_revisions AS
  ( SELECT COUNT(*) AS bb
    FROM PITEMREVISION
  )
SELECT
    (a * bb) + (b * aa) - (a * b) AS selectExpr
FROM
    count_items, count_all_items,
    count_revisions, count_all_revisions ;

Do you see why the above makes very little sense? Note how the count_all_items and count_all_revisions calculations count all the rows in those two tables. I can't see a business logic behind this.

^{(the above would off course be more efficient because it doesn't do any cross join but separates the table scans and transfers the calculations to simple multiplications in the main query. No matter how clever the optimizers have become, there are always limits to the possible transformations and optimizations they can provide.)}

The only case where this weird count might be used is if there is a check immediately after the query on whether the count is 0 or >= 1 - something that some ORMs seem to prefer over the more efficient EXISTS method. In that case, the count doesn't matter if it is 5 or 5 million since the objective is to find whether a related row exists in either of the two tables.

Answer 3

We just found the issue on our second statement, which gave this execution plan:

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

The problem was an unexpected value/param in the query which did not exist in the table PRELEASESTATUS. So I guess it needed to fully scan the table for that non existing value.

Anyhow, the SQL Server reacts quite strangely to this param, having the index on the table the server almost stops while without this index, it runs quite fine. So the param together with the index caused that issue.

I can reproduce it in our test environment that way. I would expect that it would be at least as fast as without that index if the provided value can't be found in the tables...but the SQL Server seems not to realize that its query doesn't make much sense and by sparing the new index it would be way faster.

Both Plans in comparison:

Without Index: https://www.brentozar.com/pastetheplan/?id=rylVsaCEV

With Index: https://www.brentozar.com/pastetheplan/?id=Skvy0qRNE