23 September 08 - 21:17Hoops to jump through if using Oracle 10g (instead of Postgres 6+)
I've been using PostgreSQL for years, and I've often made use of cacheing of functions.
Last year I discovered - working on an Oracle 10g environment, that any sort of caching of functinon results does not exist. (It does for 11, though) This totally blew me away, and lead to the following soluction. PostgreSQL people may enjoy reading what they can happily avoid...
The solution utilises oracle Packages. These are assembles of functions and procedures that stay in memory for a time after first being called. The packages can contain member private variables - and these are what we use to cache the results.
The result of doing this calculation in a cached function was a halving of run-time.
CREATE OR REPLACE PACKAGE SCHEMA.etl_utils IS
FUNCTION cache_clear RETURN INTEGER;
FUNCTION keyword_text_clean(i_rawtext IN VARCHAR2 ) RETURN VARCHAR2;
END etl_utils;
create or replace PACKAGE BODY SCHEMA.etl_utils IS
TYPE assoc_array_str_type IS TABLE OF VARCHAR2(200) INDEX BY VARCHAR2(200);
TYPE array_num_type IS TABLE OF number INDEX BY pls_integer;
TYPE assoc_array_nums_type IS TABLE OF array_num_type INDEX BY VARCHAR2(200);
assoc_array_str assoc_array_str_type;
assoc_array_nums assoc_array_nums_type;
FUNCTION cache_clear RETURN INTEGER
AS
BEGIN
assoc_array_str.DELETE;
assoc_array_nums.DELETE;
RETURN 1;
END;
FUNCTION keyword_text_clean(i_rawtext IN VARCHAR2 ) RETURN VARCHAR2
AS
l_out VARCHAR2(200);
l_out_upper VARCHAR2(200);
l_key VARCHAR2(200);
l_found BOOLEAN := TRUE; -- set to TRUE to enable caching
BEGIN
l_key := trim(i_rawtext);
BEGIN
l_out := assoc_array_str( l_key );
EXCEPTION
WHEN OTHERS THEN
l_found := FALSE;
END;
--sometimes short text comes back NULL erroneously
--this means we can't rely on looking up the keys that should resovle to null
--pain but makes only a small difference
IF l_out IS NULL THEN
l_found := FALSE;
END IF;
--do calculation for value and store in array-cache.
IF NOT l_found THEN
l_out := .....
assoc_array_str( l_key ) := l_out;
END IF;
RETURN l_out;
END;
http://frakkle.com
- postgresql - - § ¶
07 November 07 - 18:18in response to: Need Help Reducing View Calculations
I'm posting this via trackback because of a strange posting problem: (layout is strange and missing the email field? Yes I am using a crud browser from here... that could be it)
I have to agree with Hernan.
The simplest saving I can see is an extra column to save on the "run time" calculation. This does not have to be dangerous (ie potentially different to the date column you have already)Just setup a trigger for whenever the existing date column gets updated(/inserted) to update the extra column.
Another alternative is to use a materialised view. On Postgres this is a manual process involving setting up a view and rules, and ig you want it always up to date, triggers again on the source table to update the materialised view whenever the data changes.
This latter idea would be the fastest - faster than your original view by quite a margin, in fact. You may then be able to get rid of some (all?) indexes you have already on the existing table - because they would no longer be needed.
One thing I think would be handy that I have on my list of "when I get time" is to write some reasonably automatic code to do materialised views without the manual coding (ala Oracle). People may use these things more if they were a bit more simple to do.
Link to Need Help Reducing View Calculations page: http://www.justatheory.com/computers/databases/postgresql/reducing_view_calculations.html
- postgresql - - § ¶
15 June 07 - 01:48Data Warehouse / Business Intelligence - PostgreSQL or Oracle
I've just started a new contract for a Federal department where an old grants management system is being replaced with a PostgreSQL/Java based version. The sister project (sub project really) is a data warehouse. The choices were Oracle or PostgreSQL (PostgreSQL was what attracted me to the contract actually)
The argument that I have been unable to win in putting together a warehouse on PostgreSQL comes down to tool maturity - ie the risk involved in something "not proven" so it is almost certainly going to be built using Oracle Warehouse Builder and a BI tool tbd.
I have a long history with databases, and experience with PostgreSQL that dates to the beginning of version 7, however I've never been involved in formal datawarehousing or ETL - so I can't speak with the authority I would prefer ;-)
From my research (mostly involving asking Liam at Fujitsu) Bizgres which seems to be still at the "not finished" stage, and Jasper. I have experience in Jasper products from some time back - they looked very good then - so I can't wait to try out JasperETL.
In any case I am likely to do a parallel run of the work with OWB using Jasper/Postgres to see how they go in my own time. I would welcome any comments on all this - particularly if they can be backed up with real-world datawarehousing projects.
For those wondering about this blog, family illness has kept me from this for some time, unfortunately... something that is now coming to an end in a good way.
Cheers,
Mathew Frank (http://frakkle.com)
- default, postgresql - - § ¶
15 November 06 - 06:17PHP mime_content_type() Alternative avoiding PECL and PHPCompat
Okay - its been a long time between posts (that's code for very busy). The following is something I came accross while installing a membership system on somebody elses server. Aside from old PHP4, there were missing libraries... this is the result.
If mime_content_type is unavailable, and so is the PECL FileInfo library and so is the shell (in which case you are pretty annoyed by now) here is an alternative that falls back on the file extension - instead of 'magic'.
I took the mime list basically and used that for the extension check. note that unlike 'magic' file detection this will get fooled by a bad extension. So a PDF file called file.zip will be reported as an 'application/zip' file.
Like the PHP Compat library, this falls back to 'application/octet-stream'
<?php
if (!function_exists('mime_content_type')) {
function mime_content_type($filename) {
$idx = strtolower(end( explode( '.', $filename )) );
$mimet = array( 'ai' =>'application/postscript',
'aif' =>'audio/x-aiff',
'aifc' =>'audio/x-aiff',
'aiff' =>'audio/x-aiff',
'asc' =>'text/plain',
'atom' =>'application/atom+xml',
'avi' =>'video/x-msvideo',
'bcpio' =>'application/x-bcpio',
'bmp' =>'image/bmp',
'cdf' =>'application/x-netcdf',
'cgm' =>'image/cgm',
'cpio' =>'application/x-cpio',
'cpt' =>'application/mac-compactpro',
'crl' =>'application/x-pkcs7-crl',
'crt' =>'application/x-x509-ca-cert',
'csh' =>'application/x-csh',
'css' =>'text/css',
'dcr' =>'application/x-director',
'dir' =>'application/x-director',
'djv' =>'image/vnd.djvu',
'djvu' =>'image/vnd.djvu',
'doc' =>'application/msword',
'dtd' =>'application/xml-dtd',
'dvi' =>'application/x-dvi',
'dxr' =>'application/x-director',
'eps' =>'application/postscript',
'etx' =>'text/x-setext',
'ez' =>'application/andrew-inset',
'gif' =>'image/gif',
'gram' =>'application/srgs',
'grxml' =>'application/srgs+xml',
'gtar' =>'application/x-gtar',
'hdf' =>'application/x-hdf',
'hqx' =>'application/mac-binhex40',
'html' =>'text/html',
'html' =>'text/html',
'ice' =>'x-conference/x-cooltalk',
'ico' =>'image/x-icon',
'ics' =>'text/calendar',
'ief' =>'image/ief',
'ifb' =>'text/calendar',
'iges' =>'model/iges',
'igs' =>'model/iges',
'jpe' =>'image/jpeg',
'jpeg' =>'image/jpeg',
'jpg' =>'image/jpeg',
'js' =>'application/x-javascript',
'kar' =>'audio/midi',
'latex' =>'application/x-latex',
'm3u' =>'audio/x-mpegurl',
'man' =>'application/x-troff-man',
'mathml' =>'application/mathml+xml',
'me' =>'application/x-troff-me',
'mesh' =>'model/mesh',
'mid' =>'audio/midi',
'midi' =>'audio/midi',
'mif' =>'application/vnd.mif',
'mov' =>'video/quicktime',
'movie' =>'video/x-sgi-movie',
'mp2' =>'audio/mpeg',
'mp3' =>'audio/mpeg',
'mpe' =>'video/mpeg',
'mpeg' =>'video/mpeg',
'mpg' =>'video/mpeg',
'mpga' =>'audio/mpeg',
'ms' =>'application/x-troff-ms',
'msh' =>'model/mesh',
'mxu m4u' =>'video/vnd.mpegurl',
'nc' =>'application/x-netcdf',
'oda' =>'application/oda',
'ogg' =>'application/ogg',
'pbm' =>'image/x-portable-bitmap',
'pdb' =>'chemical/x-pdb',
'pdf' =>'application/pdf',
'pgm' =>'image/x-portable-graymap',
'pgn' =>'application/x-chess-pgn',
'php' =>'application/x-httpd-php',
'php4' =>'application/x-httpd-php',
'php3' =>'application/x-httpd-php',
'phtml' =>'application/x-httpd-php',
'phps' =>'application/x-httpd-php-source',
'png' =>'image/png',
'pnm' =>'image/x-portable-anymap',
'ppm' =>'image/x-portable-pixmap',
'ppt' =>'application/vnd.ms-powerpoint',
'ps' =>'application/postscript',
'qt' =>'video/quicktime',
'ra' =>'audio/x-pn-realaudio',
'ram' =>'audio/x-pn-realaudio',
'ras' =>'image/x-cmu-raster',
'rdf' =>'application/rdf+xml',
'rgb' =>'image/x-rgb',
'rm' =>'application/vnd.rn-realmedia',
'roff' =>'application/x-troff',
'rtf' =>'text/rtf',
'rtx' =>'text/richtext',
'sgm' =>'text/sgml',
'sgml' =>'text/sgml',
'sh' =>'application/x-sh',
'shar' =>'application/x-shar',
'shtml' =>'text/html',
'silo' =>'model/mesh',
'sit' =>'application/x-stuffit',
'skd' =>'application/x-koan',
'skm' =>'application/x-koan',
'skp' =>'application/x-koan',
'skt' =>'application/x-koan',
'smi' =>'application/smil',
'smil' =>'application/smil',
'snd' =>'audio/basic',
'spl' =>'application/x-futuresplash',
'src' =>'application/x-wais-source',
'sv4cpio' =>'application/x-sv4cpio',
'sv4crc' =>'application/x-sv4crc',
'svg' =>'image/svg+xml',
'swf' =>'application/x-shockwave-flash',
't' =>'application/x-troff',
'tar' =>'application/x-tar',
'tcl' =>'application/x-tcl',
'tex' =>'application/x-tex',
'texi' =>'application/x-texinfo',
'texinfo' =>'application/x-texinfo',
'tgz' =>'application/x-tar',
'tif' =>'image/tiff',
'tiff' =>'image/tiff',
'tr' =>'application/x-troff',
'tsv' =>'text/tab-separated-values',
'txt' =>'text/plain',
'ustar' =>'application/x-ustar',
'vcd' =>'application/x-cdlink',
'vrml' =>'model/vrml',
'vxml' =>'application/voicexml+xml',
'wav' =>'audio/x-wav',
'wbmp' =>'image/vnd.wap.wbmp',
'wbxml' =>'application/vnd.wap.wbxml',
'wml' =>'text/vnd.wap.wml',
'wmlc' =>'application/vnd.wap.wmlc',
'wmlc' =>'application/vnd.wap.wmlc',
'wmls' =>'text/vnd.wap.wmlscript',
'wmlsc' =>'application/vnd.wap.wmlscriptc',
'wmlsc' =>'application/vnd.wap.wmlscriptc',
'wrl' =>'model/vrml',
'xbm' =>'image/x-xbitmap',
'xht' =>'application/xhtml+xml',
'xhtml' =>'application/xhtml+xml',
'xls' =>'application/vnd.ms-excel',
'xml xsl' =>'application/xml',
'xpm' =>'image/x-xpixmap',
'xslt' =>'application/xslt+xml',
'xul' =>'application/vnd.mozilla.xul+xml',
'xwd' =>'image/x-xwindowdump',
'xyz' =>'chemical/x-xyz',
'zip' =>'application/zip'
);
if (isset( $mimet[$idx] )) {
return $mimet[$idx];
} else {
return 'application/octet-stream';
}
}
}
?>
- default - - § ¶
