Finance | Project Operations, Human Resources, ...

query inner join

(2) Share

Report

Posted on by DELDYN

536

Hi,

As you can see in the query attached image, i have two tables inner joined with each other, where i only selected one field from first table and no fields from the other table

Why when calling Query::delete_from(query);

in trace parser, it appears as cross join, and it selects all fields from the 2nd table even though i didn't add any field. And even if i select one field from table 2, trace parser still selects all fields and still make it as cross join but it selects one field from the first table correctly.... Any idea?

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (19)

Answers (0)

Martin Dráb 238,734 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Can't you simply use an exists join instead of an inner join?

Is Dynamic Fields property of the XXXParameters set to No?

Was this reply helpful? Yes No
DELDYN 536 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

yes dynamics fields property is No. If i don't add any field to xxxParameters or if i add one field, it will still show all fields with cross join

Yes i can use exists join but i wanted to know if there is a reason for this

Was this reply helpful? Yes No
Martin Dráb 238,734 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Can you reproduce it with standard tables, so others can test it? Or is it specific to your table and we should check what's so special with your table?

Was this reply helpful? Yes No
DELDYN 536 on at

Like (1)

Report
Copy link

Link copied!
Hi Martin,

I will try to reproduce it with standard tables and let you know. But not sure what would be special about those tables that would make it different. (please note that those two tables are custom tables but not in my model)

but just to add

Exists join at first resulted in worse performance.

With inner join:

In trace parser, the call tree showed inclusive(ms) =588.65 for the query below

Now after i changed it to exists join:

In trace parser ,the call tree showed inclusive(ms) =1291,37 for the query below

I'm not sure why it said SELECT 'X' FROM XXXPARAMETERS T2 (maybe because i didn't put any fields in the query for the parameters table)

SELECT T1.XXXID, T1.RECVERSION, T1.RECID FROM AMRENTTABLE T1 WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'XXX')) AND ((T1.CREATEDDATETIME>='2025-12-7') AND (T1.CREATEDDATETIME<='2025-12-8'))) AND EXISTS ( SELECT 'X' FROM XXXPARAMETERS T2 WHERE (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'XXX')) AND ( NOT ((T2.XXXLOCATIONID='''')) AND (T1.XXXLOCATIONID=T2.XXXLOCATIONID)))) ORDER BY T1.XXXID

then i repeated it again with exists join, but this time i didn't leave fields empty in the query for XXXParameters table, i added one field which is XXXLocationId to the query.

Now in trace parser, the call tree showed inclusive(ms) =371.94

but the query still showed select 'X' from XXXParameters, not sure why

So does that mean i need to use exists join, and select a field in the query from XXXParameters table and not leave it with empty fields?

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 238,734 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

With an exist join, it doesn't matter which fields you select. It'll never fetch any. The part SELECT 'X' actually means "return the hard-coded value X if a record exists", because we need to return something (for the EXISTS check) but we don't need to bother getting any field value from the table.

Was this reply helpful? Yes No
DELDYN 536 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

I agree.

But why when i didn't add any field to the exist join, the performance was worse than adding one field to the exist join?

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 238,734 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I wouldn't draw any conclusion from a single execution. Don't you have the same SQL query in both cases?

If not, show us how they differ. Execute both of them a few times and check whether the performance difference is consistent. Then analyze their execution plans to see how their execution differs and what's the cause of the slowness.

Was this reply helpful? Yes No
DELDYN 536 on at

Like (0)

Report
Copy link

Link copied!

Hi @Martin Dráb,

i did a fair test, where i created the same exact order 200 times and tested the delete batch job in each try

Inner join without any field (original) (200 records) (dynamic fields No)
First try: 9 minutes 31 seconds
Second try: 3 minutes 41 seconds
Third try: 3 minutes 56 seconds
Forth try: 3 minutes 48 seconds
-----------------------------------------------------

Exist join without any field (200 records) (dynamic fields No)
First try: 4 min and 3 seconds
Second try: 4 minutes 9 seconds
Third try: 4 minutes 6 seconds
Forth try: 3 minutes 43 seconds
Forth try: 4 minutes
-----------------------------------------------

Exist join with selecting one field (XXXLocationId) in the query (200 records)
First try: 4 minutes and 9 seconds
Second try: 4 minutes 21 seconds
Third try: 4 minutes 29 seconds
-----------------------------------------------------------

Indeed exist join with selecting a field or not selecting any field in the query was almost the same.
However surprisingly, inner join was better? is it because trace parser converted inner join to cross join and selected all fields regardless?

Was this reply helpful? Yes No
Martin Dráb 238,734 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You need to check the execution plan to see how the queries were executed. Even if I had your tables (which I don't) and ran the same SQL code against my database, I could get different execution plans (e.g. because different statistics of data), therefore it would tell me nothing about your results.

But I don't believe that the query optimizer would select all fields from XXXPARAMETERS when the query doesn't want any.

Note that Trace parser is a just a tool for showing collected trace logs. It has nothing to do with how the query were executed. That's the work of SQL Server (and it's various components, such as the query optimizer).

Was this reply helpful? Yes No
DELDYN 536 on at

Like (0)

Report
Copy link

Link copied!

Hi @Martin Dráb,

Do you mean to take the query that appeared in traceparser and copy it to SQL and see execution plan?
if yes

this is with exist join

And this is with inner join where it got converted to cross join and selected all fields even though my query had dynamic fields no and no fields were added
Also not sure about the index it suggested here

Does this tell you anything?

I'm really not sure how inner join was faster based on my tests when looking at start and end date of the batch

Was this reply helpful? Yes No