firebird
firebird copied to clipboard
Select invoice count per customer. slow join , bad performance ( 0,4 sec vs takes forever) ( left vs right join )
İ 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
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
İ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?.
this guy here suggests to do samething like you written here .
https://groups.google.com/g/firebird-support/c/w0qmpMIB2QQ/m/q-zYvmSOAQAJ