Left Join Slow on Firebird 2.0

Asked

Viewed 143 times

0

I have two tables:

Sale > 100 Records Primary key fields: Invoice

Payments > 200,000 Records Primary key fields: Id, Invoice, Type, 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 the performance?

firebird join

  • The field Fatura table pagamentos is foreign table key venda?? Put the structure of your two tables in the question to facilitate understanding!

  • No, I don’t have a foreign key connecting these tables. It’s an old legacy system. As I put up there the Sale table has the primary key field: invoice only. The Payments table has four primary key fields. @Matheusribeiro

  • Did my answer help you? Feedback is always welcome!

  • 1

    I added the index, but I didn’t see any improvement. The foreign key helped, but I can’t create it without deleting the duplicate items first, so it’s not viable for me. I haven’t had time to look for other solutions, but something put here

1 answer

1

In your case to improve performance you will have two options

1st Create an index for your table, with this may improve well

CREATE INDEX VENDAS_IDX1 ON VENDAS (FATURA);

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

The 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.

Browser other questions tagged firebird join

You are not signed in. Login or sign up in order to post.