Project

General

Profile

« Previous | Next » 

Revision 10361

Added by Aaron Marcuse-Kubitza over 11 years ago

inputs/REMIB/Specimen/postprocess.sql: map_nulls__*(): turned off STRICT to allow dynamic inlining, which speeds up the mk_derived_col() statements by 5x (342799.823 ms -> 71533.252 ms (6 min -> 1 min) for latitude_sec)

View differences:

postprocess.sql
37 37 
-- "ND = no disponible = not available" (https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Spot-checking#REMIB)
38 38 
SELECT util.map_nulls('{ND,NA}', $1)
39 39 
$BODY$
40 
  LANGUAGE sql IMMUTABLE STRICT
40 
  LANGUAGE sql IMMUTABLE
41 41 
  COST 100;
42 42 

  		




  43
  43
  
    
SELECT mk_derived_col((:table_str, 'country'      ), $$map_nulls__text("country_verbatim"      )$$); -- runtime: 1.5 min ("Time: 83640.424 ms") @vegbiendev


  		




  ......




  54
  54
  
    
SELECT util.map_nulls('{-999,999,1000}',


  		




  55
  55
  
    
       util.map_nulls('{RESTRINGIDO}', $1)::double precision)


  		




  56
  56
  
    
$BODY$


  		




  57
  
  
    
  LANGUAGE sql IMMUTABLE STRICT


  		




  
  57
  
    
  LANGUAGE sql IMMUTABLE


  		




  58
  58
  
    
  COST 100;


  		




  59
  59
  
    

  		




  60
  60
  
    
CREATE OR REPLACE FUNCTION map_nulls__coord__other(value text)


  		




  ......




  63
  63
  
    
-- also map nulls that are valid longitudes


  		




  64
  64
  
    
SELECT util.map_nulls('{-99,99}', map_nulls__coord__longitude($1))


  		




  65
  65
  
    
$BODY$


  		




  66
  
  
    
  LANGUAGE sql IMMUTABLE STRICT


  		




  
  66
  
    
  LANGUAGE sql IMMUTABLE


  		




  67
  67
  
    
  COST 100;


  		




  68
  68
  
    

  		




  69
  69
  
    
SELECT mk_derived_col((:table_str, 'longitude_deg'), $$map_nulls__coord__longitude("longitude_deg_verbatim")$$); -- runtime: 3   min ("Time: 188247.487 ms") @vegbiendev


  		












Also available in:  Unified diff