Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2012
    Posts
    2

    Unanswered: Are Joins really performance killers?

    I am trying to execute the following two queries both intended for the same result.
    select p.pname,s.course,p.prof1,p.prof2 from programmer p, studies s where p.pname = s.pname and prof1 != s.course and prof2 !=s.course;

    select p.pname,s.course,p.prof1,p.prof2 from programmer p JOIN studies s ON p.pname = s.pname and prof1 != s.course and prof2 !=s.course

    One of them is a JOIN, the other is not. Surprisingly the query with JOIN keyword returned the result in 0 sec where as the other query took 0.05 sec.

    Are JOINS better than non JOINS?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    both SQL statement perform a join of PROGRAMMER table with STUDIES table.
    some SQL statements perform better than others.
    It depends.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If you look at the query plan generated by Oracle for both of these queries, you will likely see they are very similar, or probably identical.

  4. #4
    Join Date
    Sep 2012
    Posts
    2

    Thumbs up Is there any alternative that uses no JOIN to this query.

    Many thanks for the reply. I agree with you people. But is there any alternative that use no JOIN to achieve the same result. Thank you, I am only a beginner.

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    For clarity, both of your SQL statements are performing a join.

    The first SQL statement that you have posted is Oracle's proprietary way of writing SQL statements (although many other DB's also support the same "join style"). Oracle understands that any statement in which you compare two columns from two different tables, is a join. You don't have to explicitly use a JOIN keyword.

    The second SQL statement that you have posted is SQL92 standard compliant. I.e. you can use that query against Oracle or MS Sql Server or MySql or any other SQL92 compliant database. In this version, you must specifically state how the two tables are joined by using the JOIN keyword. This form is more verbose, but also more explicit about the joins taking place.

    From a performance perspective, they will likely be identical (given your simplistic example). You can't just run the query once and get a wall clock time and say one is faster than the other. You will need to run both queries a few thousand times each to get a more accurate reading.

    Also consider the explain plans. I will bet they are identical. Run this in SQL*PLUS:
    Code:
    set autotrace traceonly explain
    select p.pname,s.course,p.prof1,p.prof2 from programmer p, studies s where p.pname = s.pname and prof1 != s.course and prof2 !=s.course;
    select p.pname,s.course,p.prof1,p.prof2 from programmer p JOIN studies s ON p.pname = s.pname and prof1 != s.course and prof2 !=s.course;
    I bet those explain plans come out the same.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the timing metrics returned are, in my opinion, not significant
    if the query was processing thousands, possibly millions of rows you may get meaningful metrics. bear in mind you do not have a great deal of control of a server based DB. and to run those queries to get meaningfull metrics you'd probably have to run both in the same way. ie make certain that there are no indexes or tables already loaded into memory.

    FWIW the
    where p.pname = s.pname
    is the old style ANSI standard way of defining joins which is now deprecated in favour of the join syntax. Who first came up with it I dunno, I don't care
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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