Results 1 to 2 of 2

Thread: Multiple Joins

  1. #1
    Join Date
    Oct 2002
    Location
    Cochin, INDIA
    Posts
    9

    Unanswered: Multiple Joins

    SQL Gurus,
    I have a query as given below, can any of you help me to optimise the same with multiple joins etc.., or else can any of you point me to some source which can guide me to achieve the same.

    I have 4 tables which can be related using Orchid ID.

    select snp.orchid_id as orch, sequ.sequence as seqe
    from all_snp snp left outer join all_snp_sequences sequ on anp.orchid_id = sequ.orchid_id
    where (snp.chrom = '22' and snp.chrom_start between 22000000 and 24000000) and ((snp.orchid_id in (select saf.orchid_id from sampleset_allele_freq saf where saf.sampleset_id = 3 and saf.freq between 0 and 1)) or (snp.orchid_id in (select pop.orchid_id from pop_frequencies pop where pop.pop_index = 3 and pop.frequency between 0 and 1 )))

    Thanking you in advance,
    Warm Regards,
    Saju Peruvachira

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Multiple Joins

    Originally posted by peruvachira
    SQL Gurus,
    I have a query as given below, can any of you help me to optimise the same with multiple joins etc.., or else can any of you point me to some source which can guide me to achieve the same.

    I have 4 tables which can be related using Orchid ID.

    select snp.orchid_id as orch, sequ.sequence as seqe
    from all_snp snp left outer join all_snp_sequences sequ on anp.orchid_id = sequ.orchid_id
    where (snp.chrom = '22' and snp.chrom_start between 22000000 and 24000000) and ((snp.orchid_id in (select saf.orchid_id from sampleset_allele_freq saf where saf.sampleset_id = 3 and saf.freq between 0 and 1)) or (snp.orchid_id in (select pop.orchid_id from pop_frequencies pop where pop.pop_index = 3 and pop.frequency between 0 and 1 )))

    Thanking you in advance,
    Warm Regards,
    Saju Peruvachira
    To answer your question would require more knowledge of the tables. Depending on the data, EXISTS may or may not be more efficient than IN - IN is better if there is an index on all_snp.orchid_id and the proportion of orchid_id's matching the subquery is relatively small; EXISTS will scan all the all_snp records where (snp.chrom = '22' and snp.chrom_start between 22000000 and 24000000) and then run the subquery(s) for each record, so it may like indexes on saf.orchid_id and pop.orchid_id.

    BTW, it shouldn't affect performance, but your subquery could be re-written as:
    snp.orchid_id in (select saf.orchid_id
    from sampleset_allele_freq saf
    where saf.sampleset_id = 3
    and saf.freq between 0 and 1
    UNION
    select pop.orchid_id
    from pop_frequencies pop
    where pop.pop_index = 3
    and pop.frequency between 0 and 1
    )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •