Master/detail performance

[piopio1]

I have three tables:

• tbl_customer_master_data
  tbl_currencies
  tbl_invoices.

I run the following select in PGQuery and link it to tbl_customer_master_data via master/detail (master=tbl_customer_master_data.customer_code and details=tbl_invoices.customer_code).

Code: Select all

SELECT
  tbl_invoices.customer_code,
  tbl_currencies.code,
  tbl_currencies.id
FROM tbl_invoices
INNER JOIN tbl_currencies
  ON tbl_invoices.currency = tbl_currencies.id
WHERE (tbl_invoices.update_date = '6/15/2014 12:00:00 AM')
AND customer_code = :customer_code
GROUP BY tbl_invoices.customer_code,
         tbl_currencies.code,
         tbl_invoices.currency,
         tbl_currencies.id
ORDER BY tbl_invoices.currency

• tbl_customer_master_data has an index on customer_code
  tbl_invoices has index on customer_code, update_date and currency
  tbl_currencies has an index on id

It takes 1.872 sec, info taken from dbMonitor, for the query to complete the job when moving records in tbl_customer_master_data but if I run the query in PGAdmin III it takes only 122 ms.

What can I do in order to improve the performance ?

Many thanks
Pio Pio

[azyk]

Unfortunately, we couldn't reproduce the problem. Please download the latest PgDAC version from our website http://www.devart.com/pgdac/download.html , update it, and try to reproduce the problem on this version.

[piopio1]

You are right, the issue was elsewhere.

Thanks
Pio Pio

[azyk]

Feel free to contact us if you have any further questions.