Select invoice count per customer. slow join , bad performance ( 0,4 sec vs takes forever) ( left vs right join ) - [FB 3.0.3]

[blackholeearth]

İ have invoice table with 200 000 record And customer table 200 customer.

Problem: İ wanna get invoice count per customer

Select
  cu.cust_id
  ,cu.name
  ,inv1.invoice_count
From customer as cu
Right join 
( 
  Select 
       invoice_no_letter
      , invoice_no_digit
      , cust_id
      ,Count( * ) as invoice_count
     From invoice
     Group by  invoice_no_letter  , invoice_no_digit , cust_id
) 
As inv1 on inv1.cust_id = cu.cust_id

İf i execute left join . İt takes 0,5 second. only return 50 customer !?!?
Query Plan: col1 index Rdbsomething , col2 index rdbSth

İ dont want this. İ want full customer list 200 of em. With invoice count per customer. So i switch to right join.

Now, i execute right join . İt takes forever, never finishes Query Plan: col1 natural , col2 natural

İ also added plan myself below the query. With Changing natural to index İt says cant use index null something etc.

---

İ can use dataadapter.fill query inner , outer query seperately, Merge table in c# myself.

But this doesn't solve the firebird issue . People must ve encountered this before.

Or am i doing something wrong

[hvlad]

From description above it looks like you need

select cu.cust_id ,cu.name, (select count(*) from invoice inv where inv.cust_id = cu.cust_id) as invoice_count from customer as cu

Index on invoice.cust_id might be useful.

It is better to ask such questions at support list: https://groups.google.com/g/firebird-support

[blackholeearth]

İnvoice.cust_id index is present. İ made it foreign key.

Subquery in "select column" not After "from".

İnteresting query. İ couldn't even imagine to do it like that. Eyeopener for me.

edit:

1- i tried your query . it returns customer list and invoice_count or null (if no row exists for cust_id )

im getting expected behavior. 0,7 second. 👍 this solves my current problem. 👍 thank you.

2- but outer join bad performance issue still exist? isnt it?.

[blackholeearth]

this guy here suggests to do samething like you written here .

https://groups.google.com/g/firebird-support/c/w0qmpMIB2QQ/m/q-zYvmSOAQAJ