You don't show which driver you're using, nor the queries you're sending, nor how you're fetching the data. So I don't see how I can offer any suggestions for improving your database access.

However, you do have three nested loops. Perhaps you're fetching the same data repeatedly? If so, you might get better performance by caching the data and fetching it only once.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Well, I thought its not to concern wthe query or the database in this instance, I think(may be I am wrong) the issue is the way I go through the loop. (foreach sample and for each snp)
but here it is:

 
$conn = XXXXX::Database::Conn->new('live'); #its the wrapper build aro
+und the actual DBI module to facilitate the ease of fetching the data
+.
$conn->addConnection(DBI->connect('dbi:Oracle:SNP.world','snp','snp', 
+{RaiseError =>1 , AutoCommit=>0}),'snp');
[download]


My queries

select distinct i.id_ind  from dna_plate p,dnaplate_status ps, dna_wel
+l w, individual_name i where p.id_dnaplate = w.id_dnaplate and w.id_c
+ontent = i.id_ind and p.plate_type = 13 and ps.id_dnaplate = p.id_dna
+plate and ps.status = 7 and p.id_study in(6637,7867) and i.name_type 
+= 2 order by i.id_ind 

select ss.id_snp
from well_assay wa, snpassay_snp sas, snp_summary ss
where wa.id_assay = sas.id_assay
and sas.id_snp = ss.id_snp
and wa.id_well in ('W30467', 'W30569', 'W30572')
order by 1

select distinct 'C' as proc_status,nvl(o.abbreviation, substr(translat
+e(o.orgtitle, ' ', '_'), 1, 15)) || ':' || nvl(inm.name, 'NOEXTNAME')
+ as suppname,i.id_ind,i.clonename,g.id_snp,wa.id_well as plex,nvl(dp.
+id_dnaplate, 0) as id_dnaplate,nvl(upper(rm.maprow), '-') || nvl( tri
+m(to_char(rm.mapcol, '09')), '-') as wellrc,nvl(to_char(g.id_dnawell)
+, 'UNKNOWN') as id_dnawell,g.genotype,g.confidence,nvl(dp.plate_name,
+ 'NOPLATENAME') as plate_name from (individual i left outer join indi
+vidual_name inm on i.id_ind = inm.id_ind and inm.name_type = 2),
(genotype g left outer join ((dna_well dw join dna_plate dp on dw.id_d
+naplate = dp.id_dnaplate) join rowcol_map rm on dp.format = rm.format
+ and dw.id_map = rm.id_map) on g.id_dnawell = dw.id_dnawell),
organisation o,
well_assay wa
where nvl(i.supplier, 0) = o.id_org
and i.id_ind = g.id_ind
and g.id_assay = wa.id_assay
and i.id_ind = ?
and g.id_snp = ?
and g.disregard = 0
order by 2, 3, 4, 5, 6
[download]

Is there a way that you can let the database handle the loops? Perhaps rewriting two or three of your queries as joined queries, and returning the data all at once would allow the DBMS to optimize the query a bit.

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)

That doesn't look too bad but of course you have to do the last one almost 400000 times, so that will take a while. I haven't looked at your queries too closely but is there a reason why you need to issue a query for genotypes with a single sample and a single snp instead of querying for all samples IN (...) and all snps IN (...) in one go? That should be a lot faster. You can associated sample, genotype and snp in your perl script by parsing the results. Other than that I would still look at the indices, which might not be optimal for your queries, and also at the sorting you let the DB do - that too will take time and might not be necessary, depending on how you use the data afterwards.