Greg Stark wrote:
Ron St-Pierre <[EMAIL PROTECTED]> writes:
BTW these updates do take longer than we'd like so I would appreciate more
input on how this setup could be redesigned.
Where is the input coming from?
One option is to batch changes.
<snip>
We check for new stocks and add them, and initially were using a procedure to do something similar to your code:
Something like
update current_stock_price set price = log.price, timestamp = log.timestamp
from stock_price log where current_stock_price.stock = stock_price_log.stock
and stock_price_log.timestamp between ? and ?
CREATE OR REPLACE FUNCTION updateData() RETURNS SETOF datatype AS '
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT symbol, tradeDate, tickDate, high, low , open, close, volume FROM exchangedata LOOP
RETURN NEXT rec;
UPDATE stockdata SET high=rec.high, low=rec.low, open=rec.low, close=rec.close, volume=rec.volume, tradeDate=rec.tradeDate
WHERE symbol=rec.symbol;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
... but it took too long. Off hand, do you know if your approach above would be quicker?
Ron
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
