BASE-OFF!! (SQL nerds welcome :D)

  • Posted by a hidden member.
    Log in to view his profile

    Jul 28, 2010 4:30 PM GMT
    Inner join, left join, right join, outer join, or implicit inner join?

    Discuss; because a client of mine insists inner joins are faster and I need to know I'm not crazy for knowing otherwise icon_biggrin.gif
  • Posted by a hidden member.
    Log in to view his profile

    Jul 28, 2010 7:50 PM GMT
    flex89 saidInner join, left join, right join, outer join, or implicit inner join?

    Discuss; because a client of mine insists inner joins are faster and I need to know I'm not crazy for knowing otherwise icon_biggrin.gif


    In a classic inner join all that is returned is the data matched in both tables by your where clause. I suspect what your client is seeing is perhaps an outer join (eitehr left or right) where there are huge tables. Keep in mind that for a left outer join where Table A contains 20 million rows, all 20 million are going to be returned.

    The execution time of the query may not actually be longer, but the return and release of database resources will be huge.

    In all liklihood a well constructed data model and clean data will return much quicker in the inner join. But in poorly designed data models and with odd queries one never knows.
  • Posted by a hidden member.
    Log in to view his profile

    Jul 28, 2010 7:56 PM GMT
    I am not an expert on this, but it would seem that context is needed. I don't think that anyone can say that in all cases a particular join will always perform better.

    Indexes, index statistics, table sizes, in memory objects, page sizes, the number of rows returned and of course the optimizer will all have impact on the performance of a join.
  • Posted by a hidden member.
    Log in to view his profile

    Jul 28, 2010 9:06 PM GMT
    My general experience is left joins are faster, and if you force the index to use it gets REAL fast. The inner joins require creating a cartesian product of all the records then going through each "pair" to determine which ones need to be returned.
  • phunkie

    Posts: 325

    Jul 28, 2010 9:08 PM GMT
    work your way through right or left joins. Best performance because only one side is matched.
  • phunkie

    Posts: 325

    Jul 28, 2010 9:10 PM GMT
    flex89 saidMy general experience is left joins are faster, and if you force the index to use it gets REAL fast. The inner joins require creating a cartesian product of all the records then going through each "pair" to determine which ones need to be returned.


    Yes left Joins work fastest. I did a lot of query optimizations and have seen that removing inner joins for left joins works better.

    If the records won't grow beyond a few thousand, inner joins achieve the same result.
  • Posted by a hidden member.
    Log in to view his profile

    Jul 28, 2010 9:50 PM GMT
    I would be interested in knowing specifically which RDBMS(s) you guys have found the performance gains in.

    Also, if you have any specific scenarios that would be interesting as well.
  • phunkie

    Posts: 325

    Jul 28, 2010 9:57 PM GMT
    MS SQL, Oracle and MySQL. These had the equivalent queries so I do for all three of these.

    EDIT: Forgot to mention, SQLite3 aswell.
  • Posted by a hidden member.
    Log in to view his profile

    Jul 28, 2010 10:13 PM GMT
    flex89 saidMy general experience is left joins are faster, and if you force the index to use it gets REAL fast. The inner joins require creating a cartesian product of all the records then going through each "pair" to determine which ones need to be returned.


    Essentially the use of the left join has a similar effect as hints in that you are limiting access paths for the query, so I can see why this works in some cases.

    However, the down side of hints and by extension the left join is that as time goes on other access paths may become better options for the query. Still, they can be a great tool in performance tuning if you know how to use them properly.

    Thanks for the information.

  • Posted by a hidden member.
    Log in to view his profile

    Jul 29, 2010 5:54 PM GMT
    as mentioned earlier re: inner joins

    The execution time of the query may not actually be longer, but the return and release of database resources will be huge.

    a left join is still going to be checking for all of the existing rows on both sides of the clause as it would in an inner join, the only real difference being that the left join will likely gather/return more information as it doesn't require rows in both tables or subselects to exist in order to return it in the results.

    also as mentioned earlier, it's entirely about context, especially getting into more complicated clauses -- it's always good to run whichever execution plan explainer your particular db has - "explain" in mysql, "explain plan" in oracle, etc.
  • Posted by a hidden member.
    Log in to view his profile

    Jul 31, 2010 3:51 PM GMT
    One side, with no temp tables, is ALWAYS fastest. Fewer values to scan through....unless you're joining on the wrong table to begin with.

    In this case, I wrote our client a long winded email explaining about cartesian products, table scans, and temp tables.

    He's another dude who is clueless.

    In flex89's / Logan's case he knows well about index hinting (using) and how to force indexes when needed and how to "explain" queries.

    We're moving (especially with our telephone records) away from OLTP and more to name, value (Berkley DB) and OLAP processing. Classic relationship database design requires too much bandwidth, plain pure and simple, when dealing with very large record sets.
  • Posted by a hidden member.
    Log in to view his profile

    Jul 31, 2010 3:56 PM GMT
    twomack said
    flex89 saidInner join, left join, right join, outer join, or implicit inner join?

    Discuss; because a client of mine insists inner joins are faster and I need to know I'm not crazy for knowing otherwise icon_biggrin.gif


    In a classic inner join all that is returned is the data matched in both tables by your where clause. I suspect what your client is seeing is perhaps an outer join (eitehr left or right) where there are huge tables. Keep in mind that for a left outer join where Table A contains 20 million rows, all 20 million are going to be returned.

    The execution time of the query may not actually be longer, but the return and release of database resources will be huge.

    In all liklihood a well constructed data model and clean data will return much quicker in the inner join. But in poorly designed data models and with odd queries one never knows.


    Yep. The trick is to join the correct table to begin with.
  • Posted by a hidden member.
    Log in to view his profile

    Aug 10, 2010 5:50 AM GMT
    Move to a distributed database and your data is all materialized views. No need for joins anymore. icon_smile.gif