Re^2: How to increase the efficiency of the code wen working with DB?

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)

Comment on Re^2: How to increase the efficiency of the code wen working with DB?

Replies are listed 'Best First'.
Re^3: How to increase the efficiency of the code wen working with DB? by tospo (Hermit) on May 27, 2011 at 08:37 UTC
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.	[reply]
Re^4: How to increase the efficiency of the code wen working with DB? by Anonymous Monk on May 31, 2011 at 14:02 UTC
Hi Topso, Thanks , I have checked with the indices and thats solved now. Thanks For pointing them .	[reply]