My example would be looking up invoices in Oracle Applications (Oracle DB) and for each invoice lookup the part number off the AS/400 (DB2 DB) then create an export file with the combined data, or push this data into another database. Gotta love Perl and the DBI.
For each row returned from Oracle I do 4 field lookups in Oracle and 3 off the AS/400.
After using the prepare_cached I was disappointed in the overall performance. So I wrote my own caching method, which appears to be what the DBI does (at least by description). I created a %CACHE with the select statement as the Key and a Hash representing the columns returned as the %CACHE value. Before doing the logic select I check my %CACHE first then use it's value if it exists. Thus a cache.
I ran the same code, it was all identical except for changing prepare to prepare_cached, and commenting out my cache routines.
just prepare 1000 recs 3m25s 15m used with prepare_cached 1000 recs 3m51s 87m used with prepare & self cache 1000 recs 1m20s 15m usedThe just prepare is the baseline with no caching. And after turning on prepare_cached it actually got slower and it used a lot of memory, 87m.
The only thing I can even imagine that might screw it up is the fact that I'm opening numerous connections to different databases. My DBI handle is a Hash with the Key being the name of the logic, these logic statements are the field lookups into other database sources.
$Lsth = $Ldbh{"$dologic"}->prepare_cached("$a");
I can live with it the way it is, now that I've build my own caching (since when is $C{$A}=\%{"H".$z} considered building something?)
On the output below, there's 1,113 misses on the cache and 5,886 hits. With all the testing I've done, it's actually faster to use my logics and do seperate cached lookups for non-unique columns then having Oracle ( or the AS/400) do the actual joins to get the column data.
Here's my output using prepare and my cache:
OPS-FSP - Booked Invoice for Material File (FSP) Making Text file Select Complete Records Processed: 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000, Total Cache Hits: 5,886 @ 84.1 % OPS Buyer Code - hits: 999 OPS ESN/ARE - hits: 950 OPS GL Code - hits: 993 OPS Part Number - hits: 603 OPS UOM - hits: 999 OPS Vendor Delivery Date - hits: 636 Part Desc - hits: 706 Total Cache Miss: 1,113 @ 15.9 % OPS Buyer Code - miss: 1 OPS ESN/ARE - miss: 50 OPS GL Code - miss: 7 OPS Part Number - miss: 397 OPS UOM - miss: 1 OPS Vendor Delivery Date - miss: 363 Part Desc - miss: 294 Cache Size (in bytes) Size of keys: 143,887 Size of values: 57,803 Click here for your Text File OPS-FSP-10032.txt, it is 501,000 bytes (Right-Click Save Target As) Click here for your ZIP File OPS-FSP-10032.zip, it is 36,474 bytes (Right-Click Save Target As) Total Records: 1,000, Total Skipped: 0, Warnings: 1, Errors: 0 + Elapsed Time: 1 minutes and 20 seconds
20030928 Edit by Corion: Changed PRE tags to CODE
In reply to DBI prepare_cached VS self cached by ronzomckelvey
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |