How to find source of 'No Join Predicate'

Question

I've got a ridiculously massive query generated by business objects web intelligence against a SQL server database for a budget system. A report developer has created this query in the GUI and has found that it never completes. I let it run last night and it ran for 7 hours before our servers rebooted for windows updates.

I took a look at the explain plan in sql and found a few warning on a few 'Nested Loops (Inner Join) steps - two of these warnings fee into 3rd to last step. The warning is 'No Join Predicate'.

The other note I have on this is that the query contains this in the where clause:

 BF_FUND.CD  IN  ('0105','0101')

If you only put one fund code in there, it runs fine - so somehow adding the second code is making things go cartesian on us (maybe, this is what would happen with no join predicate).

Any recommendations on how to track this down? 790 lines of wonderful SQL to wade through, with nothing but join after join.

Could the explain plan point to a specific area?

Thanks for your assistance.

---

Edits:

Can't post the query, security thing and it's much to large to obfuscate. And I wouldn't want to make anyone look at it.

Answer 1

Use online SQL formatter to get clarity on your SQL joins; then split your query block by block to debug the issue.

http://www.dpriver.com/pp/sqlformat.htm

Then you can use graphical view of plan in mssql server; that will explain everything.

P.S: if you have a poorly performing query due to a nested loops join with no join predicate(s) it's not ncessarily the case that you're missing join criteria in your query. Check http://sqlserverselect.blogspot.com/2010/10/nested-loops-join-no-join-predicate.html