From: Eric <emayo@pozicom.net>
To: pgsql-sql@postgresql.org
Subject: Performance Ideas
Date: Tue, 25 Jun 2002 15:37:39 -0400
Message-ID: <afagq4$mcm$1@news.hub.org> (raw)
I have a SQL which uses a function for one of the returned rows. This
stored function does calculations that are expensive & slow. I am looking
for ways to speed up this query but having no luck.
Any SQL geniuses out there help me with this?
select
o.orderid,
ol.itemcode,
ol.itemname,
ol.uom,
qty_available( ol.itemcode, ol.uom ) as "Qty On Hand"
from
orders o,
orderlines ol,
where
o.status = 'OPEN' and
ol.orderid = o.orderid and
qty_onhand( ol.itemcode, ol.uom ) > 0;
The function, qty_onhand, calculates the Qty on hand and returns a value in
units of measure passed (ol.uom). This function is an expensive function to
use -- degrades performance. With out the function in the WHERE or SELECT
clause, performances is acceptable.
I get marginally better performance if I "select into temporary table"
without the function and then run a query on the temporary table which
includes the qty_onhand function.
I am trying to present the user with a list of open orders that are "READY"
to be fulfilled which requires me to do a "stock level check."
My fall back solution is to make the user enter some pre-query information
like the orderid she is trying to ship against but my customer really likes
the current view they have which shows all open orders that are READY to be
fulfilled.
Any ideas??!?!?! Tricks of the trade?!?!?!
Also, side note, I tried creating views assuming PostgreSQL would optimize
the view after a vacuum but it does not. Also, the function seems faster in
the temporary table, why? Why wouldn't the funciton only evaluate values
that match the first 2 criteria (OPEN and ol.orderid = o.orderid)? It's as
if the qty_onhand is evaluating ALL records in the orderlines (ol) table.
Thanks , Eric
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: emayo@pozicom.net
Subject: Re: Performance Ideas
In-Reply-To: <afagq4$mcm$1@news.hub.org>
* 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