Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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') 
              )

MSSQL ActionPlan

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?

share|improve this question
    
Are both databases running on comparable hardware and disk systems? Is the data identical in each db? –  Stuart Moore Oct 29 '12 at 14:01
3  
Please add explain plans from both systems. –  Phil Oct 29 '12 at 14:09
2  
You're using Peoplesoft. No need to rename columns and tables in your question. –  Phil Oct 29 '12 at 14:34
5  
Your OR condition seems rather a complicated way to express: 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 IN ('Y', 'N') –  Vincent Malgrat Oct 29 '12 at 14:35
1  
@Zeutheus you'd be better off editing this question to be "why is this SQL server query slow?" And add the original query –  Phil Oct 29 '12 at 15:36
show 5 more comments

3 Answers 3

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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 ;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.