SQL Query Performance : No Join Predicate in Estimated Query Plan

 What is the issue ?

Found in one of the projects that a "view"  while querying took so much time. It was taking hours and hours when SELECT field1, field2 FROM view vs. getting executed within few seconds when SELECT * FROM view.

Example:

SELECT field1, field2 FROM view1 => Takes hours to execute

SELECT * FROM view1 => Executes within a minute 

Little Background: 

It was really surprising that view select was taking huge time when field names were specified in the select query. On the contrary when "*" was specified , it was taking very less time. It is usually believed and practiced to always use field names in select statements. 

• The view was huge , it was selecting records from around 7 tables and one more view.
• The view was joined with table1 with INNER join 
• Many of the tables were joined "LEFT OUTER JOIN"
• This view was returning around 15 fields and over 1 million records
• We could not replace this view with some stored procedure as this view was used in join with other tables. (that means , the view code could not be ported to a sql server stored procedure)
• This view could not be indexed (for many other reasons). One of the reasons that this view could not be indexed was , the dependent (the view inside this view) was not SchemaBIND. And this required lots of code changes which could impact other view and tables as well

How was the root cause diagnosed?

• We tried to break this view into parts. We executed each part one by one to see the time being taken by each one of them. Finally one block was identified
• Placed both the execution code in two different query editor window (in SQL server management studio).
• Placed the first SELECT query in query editor and then clicked over "Display Estimated Execution Plan" 

        

• We can see a query plan generated for the same
• We did the same for another query in another query window 

    

• We found a significant clue in the query plan. We found that the query plan was talking about "No Join Predicate"

    

• There are couple of reasons why SQL server gives the hint of "No Join Predicate"
• When the joins are not written properly. For example , we have simply written the cross join or many other insignificant joins. Then the SQL server marks it as "No Join Predicate"
• While joining the tables/view together , if by chance any of the joining fields has NULL values.
• In our case, we found that one of our joining fields could contain some null values. We fixed this and the query executed at the fastest (within 15 seconds).

Solution:

We introduced ISNULL with the join and the problem was resolved. 

Happy Coding!!!