LEFT JOIN is killing my query - how to speed up?

Question

I have two tables:

1. a list of documents created in our system (offers and invoices) when something is logged as "done".

   BELEG_ART	BELEG_NR	DATUM	BELEG_TYPE
   160	337691	11.01.2021	Invoice
   10	195475	04.01.2021	Offer
   20	195444	04.01.2021	Confirmation

2. A list of transactions (sales etc) with article information AND the document info

   ANUMMER	KDNR	NAME1	REC_LIST
   181557	59301205	Fred	332240
   195973	59306391	John	338225
   189661	59304599	Steve	335495
   189718	49302475	Ed
   196483	59303491	Mark	338204
   190021	49302595	Jones

You can see that the Offers and Confirmation... they start with a "1". Invoices with "3".

I need everything to be linked and identified by it's ANUMMER with "1". Later on, I'll pull other tables based on this number, thus it's the critical point for me.

The problem is - in the documents table, when you see the invoice, you don't see the ANUMMER. You only see the "3".

So, I have created a join as below to pull everything together.

SELECT
DAB700.BELEG_ART,DAB700.BELEG_NR,DAB700.DATUM,DAB700.BUCH_DATUM,

case // rename the documents to something more meaningful
when DAB700.BELEG_ART = 10 then 'Angebote'
when DAB700.BELEG_ART = 20 then 'Auftrag'
when DAB700.BELEG_ART = 60 then 'Lieferschein'
when DAB700.BELEG_ART = 160 then 'Rechnung'
else 'not defined'
end as "BELEG_TYPE",
DAB050.ANUMMER,

case // if the document is an offer, then copy it to order_number. If it's a invoice, copy that number to order_number.
when DAB700.BELEG_ART = 10 then DAB700.BELEG_NR
when DAB700.BELEG_ART = 160 then DAB050.ANUMMER
else 'NA'
end as "order_number"

FROM "DAB700.ADT" DAB700

// if the document is an invoice, then join to the table DAB050 and reference the same key field REC_LIST.
left join "DAB050.ADT" DAB050 on
Case
When DAB700.BELEG_ART = 160 then DAB700.BELEG_NR = DAB050.REC_LIST
 End

WHERE (DAB700.DATUM={d '2021-01-12'})

So - to my problem and question: when running this join query, it's much slower than I'd like (even with small datasets). Is there a way to restructure this, so it's faster?

Long story short - to simplify:

• I want to add some columns to table 1
• when table 1-BELEG_NR starts with a "1", good - just move the number into a new column
• when table 1-BELEG_NR starts with a "3", then I have to link to table 3, and pull in the ANUMMER

thanks for your help

Answer 1

The problem is that the join condition is not optimizable because 1. it is a complex expression, and 2. it involves a memo/clob columen (REC_LIST is probably a memo based on your other question). What that mean is that the joint condition has to be evaluated for each combination of rows in DAB700 and DAB050.

It seems to me that the join condition can be simplified to:

DAB700.BELEG_ART = 160 And DAB700.BELEG_NR = DAB050.REC_LIST

There is no need for the Case because the default case is null or false. That will cut down the number of rows in DAB700 participating in the join. However, the second part of the join is still not optimal if REC_LIST is MEMO or CLOB data type.

In any case, I would also suggest checking to see if REC_LIST should be a fixed length string column (CHAR, NCHAR, VARCHAR or NVARCHAR) instead of MEMO which cannot be indexed and thus not useful for optimization.