Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

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

by tospo (Hermit)
on May 26, 2011 at 15:02 UTC ( #906848=note: print w/ replies, xml ) Need Help??


in reply to How to increase the efficiency of the code wen working with DB?

How long does it take to run those queries in a shell, i.e. not through your Perl module? I think the issue is likely to be one of the database itself and not one of your client script. First and foremost I would check indices of the relevant tables in that schema. If the query can not be run efficiently because no suitable index can be found then it's going to take forever.


Comment on Re: How to increase the efficiency of the code wen working with DB?
Re^2: How to increase the efficiency of the code wen working with DB?
by Anonymous Monk on May 26, 2011 at 15:42 UTC
    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.
        Hi Topso, Thanks , I have checked with the indices and thats solved now. Thanks For pointing them .

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://906848]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (11)
As of 2014-12-22 16:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (121 votes), past polls