Transact SQL – Missing Join Predicate Event – No Join Predicate

Introduction

In the last few months, I have had my SQL Developer hat on.  And admittedly it is a bit difficult to move the needle when one can not just throw hardware at the problem.

I have a query and I am reviewing the Query Plan and noticed a warning indicating “No Join Predicate“.

Query

It is a simple query:

declare @SalesPersonID int

SELECT
	  Invoice.OrderNumber
	, InvoiceDetail.ItemNumber
FROM dbo.Invoice

	INNER JOIN dbo.InvoiceDetail

		ON Invoice.OrderNumber = InvoiceDetail.OrderNumber

WHERE   Invoice.SalesPersonID = @SalesPeronID
;

Query Plan – Problematic

QueryProblem

I struggled with this all day.  There is quite a bit of postings on the Internet.  Here are some of the suggestions:

  • Re-writing Query
  • Rebuilding Indexes & Updating Statistics

Possible Fixes

Possible Fixes – Optimize for Hint

Add Optimize for Hint:

Add an Optimize for Hint – Indicating a value for the argument\variable.

declare @SalesPersonID int

SELECT
	  Invoice.OrderNumber
	, InvoiceDetail.ItemNumber
FROM dbo.Invoice

	INNER JOIN dbo.InvoiceDetail

		ON Invoice.OrderNumber = InvoiceDetail.OrderNumber

WHERE   Invoice.SalesPersonID = @SalesPeronID

OPTION (OPTIMIZE FOR (@SalesPersonID = 10191));

;

Query Plan:

Pasted below are the Query Plans which shows that with the introduction of Optimize for we are able to rid ourselves of “No Join Predicate“.

QueryFix

Microsoft Connect Items

Relevant Connect Items

Find Similar Queries in Query Plans

Here is a Mat’s answer for digging into our Query Plans and finding queries suffering from “NoJoinPredicate“.

Find “No Join Predicates” in your Query Plans DMV
 http://dba.stackexchange.com/questions/35082/what-exactly-does-no-join-predicate-mean-in-sql-server

 


WITH XMLNAMESPACES
(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    st.[text] as [actualSQL],
    pl.query_plan,
    ps.execution_count,
    ps.last_execution_time,
    ps.last_elapsed_time,
    ps.last_logical_reads,
    ps.last_logical_writes
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
WHERE pl.query_plan.value('(//Warnings/@NoJoinPredicate)[1]', 'bit') = 1
Order By last_execution_time desc
OPTION (RECOMPILE);

 

 

References

One thought on “Transact SQL – Missing Join Predicate Event – No Join Predicate

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s