Left Join Slow in Firebird 2.0

Navigation
0

I have two tables:

Sale > 100 Records Primary key fields: Invoice

Payments > 200,000 Records Primary key fields: Id, Invoice, SalesType, Document.

I have the following SQL: 

Select V.FATURA, P.ID
  From Venda V
Left Join Pagamentos P on p.FATURA = V.FATURA
Where v.DTEMI between '25.09.2018' and '25.09.2018'

But it is very slow. What can I do to improve performance?

    
asked by anonymous 03.10.2018 / 20:32

1 answer

1

In your case to improve performance you will have two options

1ª Create an index for your table, so it can improve. 

CREATE INDEX VENDAS_IDX1 ON VENDAS (FATURA);

2nd Change the structure of your pagamentos table so that the pagamentos.fatura field is a foreign key of the vendas table.

Correct would be the 2nd option, but as you commented that it is a legacy system you will have to see if it is feasible to change.

    
03.10.2018 / 21:06
Creating components from another base component How to make a menu in Console in Python