From: Preeti Ambardar <pambardar@networkprograms.com>
To: pgsql-sql@postgresql.org
Subject: SQL Optimization
Date: Wed, 17 Dec 2003 11:56:52 +0530
Message-ID: <000c01c3c466$c247f5b0$5acb09c0@pambardar> (raw)
I am working on a query qhich has lot of RIGHT and LEFT Outer joins. This takes almost 2 hours to execute.
SELECT mstcurrency.pkcurid, mstcurrency.curswift_code,
mstproducts.pkprdid, mstproducts.prdname,
mstproducts.fkcnvid_prdbaseuom,
cpuser.uspname || ' ' || cpuser.uspsurname as uspname,
cpuser.pkuspid, cpuser.uspnotify_number AS
cpusercontactdetails, cpuser.fktrdid_usptradhouseid as cpthid,
mstcompanies.pkcmpid, mstcompanies.cmpname,
mstcompanies.cmpaccount_type,
mstfacilityviews.facname, mstfacilityviews.pkfacid
FROM mstuserprofiles cpuser RIGHT OUTER JOIN mstcompanies
ON cpuser.fktrdid_usptradhouseid = mstcompanies.fktrdid_cmptradhouseid
AND cpuser.pkuspid = mstcompanies.cmpcontact_userid
LEFT OUTER JOIN mstcurrency
ON mstcompanies.fktrdid_cmptradhouseid = mstcurrency.fktrdid_curtradhouseid
AND mstcompanies.fkcurid_cmpcurid = mstcurrency.pkcurid
LEFT OUTER JOIN mstproducts
ON mstcompanies.fktrdid_cmptradhouseid = mstproducts.fktrdid_prdtradhouseid
AND mstcompanies.cmpmain_product = mstproducts.pkprdid
RIGHT OUTER JOIN mstuserprofiles
LEFT OUTER JOIN mstfacilityviews
ON mstuserprofiles.uspdefaultfacility = mstfacilityviews.pkfacid
ON mstcompanies.pkcmpid = mstuserprofiles.uspdefaultcounterparty
AND mstcompanies.fktrdid_cmptradhouseid = mstuserprofiles.fktrdid_usptradhouseid
WHERE mstuserprofiles.pkuspid = 101 AND mstuserprofiles.fktrdid_usptradhouseid = 1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pambardar@networkprograms.com
Subject: Re: SQL Optimization
In-Reply-To: <000c01c3c466$c247f5b0$5acb09c0@pambardar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox