|Pathologically Eclectic Rubbish Lister|
SQL vs Perl table comparingby LiTinOveWeedle (Scribe)
|on Nov 07, 2002 at 18:21 UTC||Need Help??|
LiTinOveWeedle has asked for the
wisdom of the Perl Monks concerning the following question:
Hi my dear Monks.
I wrote part of provisioning interface which manage data from inventory management into performance managent. Data are stored into SQL, Oracle database. In predefined time my script compare SQL table with its SQL local backup copy to find out changes... So I am comparing two SQL tables with identical structure to find:
1. records existing only in first table
To do that I am using DBI::Oracle module and ->selectall_hashref() method to fetch whole content of both tables to two hash data structures. Than I have simple procedure to compare both hashes and as result I get three hashes (see points 1-3, I don't take care about identical record - point 4) This works good for now. I am using something like this:
where $hash1 and $hash2 are references to input tables and output are hash1(see point 1.), hash2(see point 2.) and hash3(see point 3.)
Problem is, that in this time tables contains about hundreds of records. Now we are going to store about 10.000 records in tables!!! Because I am reading whole table into perl variable (into the memory):
1. I want to know, if perl can handle so big data structure or is this just HW sizing problem?
->selectall_hashref() method return hash of arrays, where each array is one row in SQL table and hash key is in my example sql primary key. Table has about 20 collumns.
Hope that the code I wrote bellow is good. I tried to memorized it, but my brain work worse than sql database :-). Thank you for help brothers.
Li Tin O've Weedle