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

    Unanswered: Re: How To Misuse SQL's From Clause

    I have just been reading How To Misuse SQL's From Clause (thanks to Rudy who pointed it it out on his website r937.com).

    It makes me go "hmm". It says "...this is a very bad query; it's logically flawed" - well no, actually I don't agree with that. Logically, the query is just perfect. However, it is true that a DBMS with a less than perfect optimizer might not recognize that the query can be transformed into one with a correlated subquery, which may be faster to process.

    But, dammit, the whole point of SQL is that we define what we want, not how the DBMS should go about getting it. That's what the optimizer is for! Why should we have to waste our time re-phrasing our queries when the optimizer should be quite capable of doing that itself (by following the algorithm given in the article).

    I don't think I'm beaing unreasonable here!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hands up all of you who have never tried to influence the way the optimizer handles a query by rephrasing it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Posts
    330
    "From Clause"?? Influence the Optimizer?? I would just like the user to be able to tell me what he what results he really wants from his query.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by urquel
    I would just like the user to be able to tell me
    heh, good one

    yes, that's often the biggest hurdle, isn't it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by r937
    hands up all of you who have never tried to influence the way the optimizer handles a query by rephrasing it
    Not me: in my early Oracle-using days before the cost-based optimizer was introduced, we used to have hours of fun modifying queries by re-arranging the order of the tables in the FROM clause, and the order of predicates in the WHERE clause, because the old rule-based optimizer processed the tables and predicates in the order it read them (right to left).

    Thankfully, those days are long gone, and so in most cases has the need to restructure queries in other ways (like the one the article recommends). If two queries are semantically equivalent, then it shouldn't matter which one you write, the optimizer should try both and use the fastest. And it should be much better at it than a human!

Posting Permissions

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