Same query taking 0.2 sec in Oracle database but 4 mins in SQL Server

Question

SELECT A.Field1 FROM Table1 A INNER JOIN Table1 B 
ON A.Field2 = B.Field2 
AND A.Field3 = B.Field3 
AND A.Field4 = B.Field4 
AND B.Field1 = "string1"
AND B.Field2 = "string2" 
AND A.Field5 = (SELECT MAX(C.Field5) FROM Table1 C INNER JOIN Table2 D 
                 ON 
                 (C.Field5 < B.Field5 
                 AND B.Field6 = D.Field6 
                 AND C.Field2 = B.Field2 
                 AND C.Field3 = B.Field3
                 AND C.Field4 = B.Field4
                 AND D.Field7 = 'Y') 
              OR 
                 (C.Field5 < B.Field5
                 AND B.Field6 = D.Field6 
                 AND C.Field2 = B.Field2 
                 AND C.Field3 = B.Field3
                 AND C.Field4 = B.Field4
                 AND D.Field7 = 'N') 
              )

This query takes 0.2 sec in an Oracle database but 4 mins in SQL Server database.

Both the databases have the same indexes, the same records structure and the same column structure.

Can anyone tell me why?

Answer 1

There's a chance that the SQL server table or index statistics are out of date, aren't as accurate as they could be (both of which can affect how the SQL query plan is put together) or SQL could be reusing an old query plan which was based on a smaller data set.

If the statistics are out of date, or aren't accurate, then SQL will generate the query plan based on flawed data, so you'll get poorly performing code (looking specifically at your SQL Query plan and the 98% on the nested loop, it might just be a single index, or one of the tables affected by that part of the query plan).

If the statistics are up to date and you're still getting poor performance and the query plan is pushing everything through a Nested Loop, you could try forcing it to use something else like using an OPTION query hint, although try it out on a dev system first. MSDN - Query Hints article

This quote posted on SQLServerCentral by Kevin Boles is a something to keep in mind when looking at inefficient query plans.

One thing to watch out for here is when the optimizer THINKS there will be few rows (check the estimated rows in the popup for the estimated query plan graphic) but in reality there are LOTS of rows (thousands or even millions).

Specifically regarding the nested joins, Gail Shaw wrote this article on execution plan operations joins, which is a good reference on the physical join types and where/how they're normally used.

Answer 2

I would definitely make sure indexing and statistics are up to date. Check out this fantastic script. http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Answer 3

Those "Nested Loops" may not be the optimal plan. Have you tried rewriting the correlated subquery into a non-correlated one?:

SELECT A.Field1 
FROM 
    Table1 A 
  INNER JOIN 
    ( SELECT B.Field2 
           , B.Field3 
           , B.Field4
           , MAX(C.Field5) AS Field5
      FROM 
          Table1 B 
        INNER JOIN 
          Table1 C   ON  C.Field2 = B.Field2 
                     AND C.Field3 = B.Field3
                     AND C.Field4 = B.Field4 
                     AND C.Field5 < B.Field5
        INNER JOIN 
          Table2 D   ON  D.Field6 = C.Field6 

      WHERE D.Field7 IN ('N', 'Y')
        AND B.Field1 = 'string1'
        AND B.Field2 = 'string2'

      GROUP BY B.Field2 
             , B.Field3 
             , B.Field4 
    ) g
  ON  g.Field2 = A.Field2 
  AND g.Field3 = A.Field3 
  AND g.Field4 = A.Field4 
  AND g.Field5 = A.Field5 ;