We have a query as below:
=========================================================
select relationship_id as "Id",company_name as "Company",product_category_desc
as "Product",uom_desc as "UOM",shipment_term_desc as "Shipment
Term",payment_term_desc as "Payment Term",city_name as "Port",currency_name
as "Currency",activity_desc as "Activity",credit_days as "Credit Days" from
_100001relationships rs,master_member mm,member_product_details
mpd,master_product_category mpc,master_uom mu,master_shipment_term
mst,master_payment_term mpt,member_financial_details mfd,master_currency
mc,member_commercial_details mcd,master_activity ma,master_city mcy
where mpd.product_details_id = rs.product_details_id and
mpd.product_category_id=mpc.product_category_id and mpd.uom_id = mu.uom_id
and rs.shipment_term_id = mst.shipment_term_id and rs.payment_term_id =
mpt.payment_term_id and rs.commercial_details_id = mcd.commercial_details_id
and mcd.port = mcy.city_id and rs.financial_details_id =
mfd.financial_details_id and mfd.currency_id = mc.currency_id and
mpd.activity_id = ma.activity_id and mm.member_id = mpd.member_id
ORDER BY rs.relationship_id DESC;
=====================================================
The decision to keep the fields in different tables was taken in view of the
overall need of the system (there might be scope for improvement here too).
This query normally select about 10-20 rows. The problem is, the page load
takes about 4-5 seconds in the local network. The query run in psql terminal
takes about 2 second to execute (outputing 3 rows).
When hosted on the internet with most of our users using dialup connections,
and the query returning 10+ rows, this will not be acceptable.
Please point us towards the right direction to handle this type of problems.
Best regards,
Siva Kumar
