On 09/27/2012 02:55 PM, Scott Marlowe wrote:
> On Thu, Sep 27, 2012 at 2:46 PM, M. D. <lists@turnkey.bz> wrote:
>> select item.item_id,item_plu.number,item.description,
>> (select number from account where asset_acct = account_id),
>> (select number from account where expense_acct = account_id),
>> (select number from account where income_acct = account_id),
>> (select dept.name from dept where dept.dept_id = item.dept_id) as dept,
>> (select subdept.name from subdept where subdept.subdept_id =
>> item.subdept_id) as subdept,
>> (select sum(on_hand) from item_change where item_change.item_id =
>> item.item_id) as on_hand,
>> (select sum(on_order) from item_change where item_change.item_id =
>> item.item_id) as on_order,
>> (select sum(total_cost) from item_change where item_change.item_id =
>> item.item_id) as total_cost
>> from item join item_plu on item.item_id = item_plu.item_id and
>> item_plu.seq_num = 0
>> where item.inactive_on is null and exists (select item_num.number from
>> item_num
>> where item_num.item_id = item.item_id)
>> and exists (select stocked from item_store where stocked = 'Y'
>> and inactive_on is null
>> and item_store.item_id = item.item_id)
> Have you tried re-writing this query first? Is there a reason to have
> a bunch of subselects instead of joining the tables? What pg version
> are you running btw? A newer version of pg might help too.
>
>
This query is inside an application (Quasar Accounting) written in Qt
and I don't have access to the source code. The query is cross
database, so it's likely that's why it's written the way it is. The form
this query is on also allows the user to add/remove columns, so it makes
it a LOT easier from the application point of view to do columns as they
are here. I had at one point tried to make this same query a table
join, but did not notice any performance difference in pg 8.x - been a
while so don't remember exactly what version.
I'm currently on 9.0. I will upgrade to 9.2 once I get a new server.
As noted above, I need to buy a new server anyway, so I'm going for this
one and using the current as a VM server for several VMs and also a
backup database server.