Hi All, I have a question on how to reduce the time taken to run a script that involves three queries wen individual run on Oracle.
1. A query to get get all the samples takes about "Elapsed: 00:00:00.30"
2. A query to get the snps takes about "Elapsed: 00:00:00.7"
3. To get the genotypes for each sample and on each snp."Elaesed 00:00:00.1(for single sample and single snp)

My general structure of the code is something like this: 
foreach my $ind( @ {$indsent} ){ #where indsent is the list of sample
 21   my $i = 1;
 22   foreach my $snp (@ {$snparray} ){ list of snp
 23 
 24     my $genotype = $conn->executeArrRef('snp::Sequenom::getGenotyp
+eCalls', $ind->[1], $snp->[1]);
 25 
 26     foreach my $geno( @ {$genotype} ){
 27       
 28       if(defined $geno->[9]){
 29 
 30 
 31         print "$i,$ind->[0],$geno->[9],$snp->[2],$snp->[0]\n";
 32 
 33       }
        }
      }

[download]
Could someone suggest, is there any alternate way of doing this? I also tried dumping the snparray, and indsent to a hash and passing the values to fetch the genotype. But both of them are equally bad :)
I feel very bad to say, but it takes nearly an hour to just fetch the data from the Db. Though the query is not bad.( If it would be of any help; I will have 4K samples and 30 -90 snps.)

Thanks for your time and suggestion in advance :)

In reply to How to increase the efficiency of the code wen working with DB? by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.