Slow sub-selects, max and count(*)

-- extend yr to 4 digits, alter quotes to dollars

 

  update temp_shareprices set dte = \'20\'||dte,v1=v1/100,v2=v2/100,v3=v3/100,v4=v4/100;

 

-- add in any new company tickers
insert into fsecurity (sec_cde) select ticker from temp_shareprices where length(trim(ticker))=3 and ticker not in ( select sec_cde from fsecurity);

 

-- ====================

-- remove rows from temp_shareprices where we do not have a corresponding security

-- using sub-select is slow
-- delete from temp_shareprices where ticker not in (select sec_cde from fsecurity);

-- can just delete ones where length<>3 as insert above ensures all length 3 are inserted. All ASX codes are 3char long
delete from temp_shareprices where length(trim(ticker))<>3;

 

-- Attempt to remove without limiting to length and not using sub select does not work
--update temp_shareprices set ticker = s.sec_cde from temp_shareprices t left outer join fsecurity s on t.ticker = s.sec_cde;
--delete from temp_shareprices where ticker is null;

 

-- Scan the input table and delete as appropriate from fsechist

 

-- Using sub select to remove any pre-existing records in fsechist very slow
-- delete from fsechist where hist_q in (select hist_q from fsechist, temp_shareprices where hist_tick = ticker and dte = hist_date);

 

-- Tried to replace subselect
update fsechist set hist_tick = \'@@\' from fsechist h, temp_shareprices t where t.ticker = h.hist_tick and h.hist_date = t.dte;
delete from fsechist where hist_tick = \'@@\';

 

-- insert newvalues
insert into fsechist(hist_tick,hist_date,hist_v1,hist_v2,hist_v3,hist_v4,hist_vol) select   ticker,dte,v1,v2,v3,v4,v5 from temp_shareprices;

 

-- count the number inserted
-- select   count(*) into cnt from temp_shareprices;

 

-- perform  drop_table(\'temp_shareprices\');

 

return alldone;

 

end;'
  LANGUAGE 'plpgsql' VOLATILE;