CROSS JOIN VS INNER JOIN

(0) Share

Report

Posted on by Brandon Wiese

17,788

Why does the kernel sometimes use CROSS JOIN and put the join condition in the WHERE clause, yet other times choose an INNER JOIN with an ON clause?

For example, the view SALESLINEEXPANDED joins 1:1 to CUSTPACKINGSLIPONTIMESTATUS and INVENTDIM. With a significant number of records and a CROSS JOIN and a complex WHERE clause, the performance is beyond terrible.

I assumed it would only do that when the relationships were both 1:n, but in the query for SALESLINEXPANDED, they are both 1:1 as are all of the other joined and left join views and tables.

Curious.

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Brandon Wiese 17,788 on at

Like (0)

Report

RE: CROSS JOIN VS INNER JOIN

With 400,000 records in SALESLINE and CUSTPACKINGSLIPTRANS, the out of the box SALESLINEEXPANDED runs for hours and never finished. After I converted the CROSS JOIN clauses to INNER JOIN and moved the WHERE clause snips to ON clauses, it finishes in a respectable 20 seconds and produces the expected 400,000 rows.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

RE: CROSS JOIN VS INNER JOIN

By default the inner joins you create in Ax are converted to cross joins when you look at the SQL statements between AOS and SQL server. But if you look at the execution plan of SQL you'll notice that SQL converts the cross joins to inner joins again. This is because a cross join with a where clause will act like a inner join with an ON clause. see: technet.microsoft.com/.../ms190690(v=sql.105).aspx

What I also found is that if you mix joins (inner joins, outer joins) and exists Ax will sometimes do something completely different than you would aspect. Creating an unwanted SQL statement.

So to fully understand what the difference is between the statement created by Ax and the one you created by hand. Please compare the execution plans.

Go To Sql Server Management Studio.

Create a new Query

put both Queries in on the same new Query page.

Hit the button (estimated execution plan)

And you'll be able to compare the 2

first of all you see the difference in percentage (one taking a higher percentage of total execution time than the other, that one is slower)

In the execution plan you can also see what indexes are used by SQL for the statements, maybe it uses different ones or is missing one.(It will tell you in green text see my blog: kaya-consulting.com/view-slow-maybe-sql-missing-index-can-help-you)

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (1)

Report

RE: CROSS JOIN VS INNER JOIN

Yes, I was already comparing the execution plans for the single statement, and it is different between the CROSS JOINs statement that AX generates and the statement where I only switched them to INNER JOINs. So, SQL is not merely doing the same thing under the hood, whether it should be or not. SQL is 2014 SP1 CU3 (latest build), so unfortunately I can't go hunting for fixes.

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

RE: CROSS JOIN VS INNER JOIN

And I had already updated the statistics for the various tables involved, and so on.

Was this reply helpful? Yes No
Prodware_SaM on at

Like (0)

Report

RE: CROSS JOIN VS INNER JOIN

We have detected the same Issue.

CustTransOpen Join CustTrans and CustTable to show all Due CustTrans

AX Performance: With Cross Joins 10 Min

SQL can perform same Result in less than 10 Seconds with Inner Joins

The Query Plan looks totaly different in both cases.

Indexes and Statistics are up to date.

Was this reply helpful? Yes No
Suggested answer

Sheikh Sohail 6,125 on at

Like (0)

Report

RE: CROSS JOIN VS INNER JOIN

If you are using AOT query then check your table relation, Cursor only use cross join when relation is missing between tables in query.

Was this reply helpful? Yes No
Rustem Galiamov 8,072 on at

Like (0)

Report

RE: CROSS JOIN VS INNER JOIN

Don't forget to wrap query into cursor when you execute it in SQL, because AX kernel any of data selection is wrapping in server cursor.

Was this reply helpful? Yes No
Tony Eden 5 on at

Like (0)

Report

RE: CROSS JOIN VS INNER JOIN

Here is the difference:

Inner Join: SQL Inner Join or Equi Join is the most simple join where all rows from the intended tables are cached together if they meet the stated condition. Two or more tables are required for this join. Inner Join can be used with various SQL conditional statements like WHERE, GROUP BY, ORDER BY, etc.

Syntax

SELECT column-name

FROM table-1 INNER JOIN table-2

WHERE table-1.column-name = table-2.column-name;

to learn more about the query and problem statement of inner join check this out: Click here

Cross Join: Cross Join is useful when you need all combinations. It maps each row of the first table with all the rows from the second table. Cross Join is also called Cartesian Product – if the first table has X rows and the second table has Y rows, it will return X*Y rows.

Syntax of SQL Cross Join

There are two ways to implement CROSS JOIN in SQL.

CROSS JOIN clause

SELECT [COLUMNS] FROM [TABLE_1] CROSS JOIN [TABLE_2]

FROM clause without WHERE clause

SELECT [COLUMNS] FROM [TABLE_1],[TABLE_2]

To learn more about Cross Join in SQL: Click here

Was this reply helpful? Yes No