SQL query optimization
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SQL query optimization

  1. #1
    Join Date
    Sep 2005
    Posts
    11

    SQL query optimization

    Hi!

    I have some questions related with SQL query optimization (Oracle 10g Release 2).

    It's like this:

    Query A:
    SELECT X."COL1", Y."COL8"
    FROM "TBL1" X INNER JOIN "TBL2" Y ON (X."ID" = Y."XID") WHERE X."COL4" = 'xpto';

    Query B:
    select x.col1, y.col8
    from tbl1 x, tbl2 y
    where x.id = y.xid and x.col4 = 'xpto';


    I usually code queries like "Query B", but recently a software tester told me that I should code like "Query A" and told me this:

    1 - you should always use upper case.
    2 - you should put quotes in column and table names
    3 - you should use the JOIN syntax because it's the recommended syntax (ISSO/IEC 9075 SQL:2003)

    And he justifies this with performance improvements.

    And my questions are:

    A) Does this make any sense?
    B) Is the JOIN syntax better than the Oracle syntax (+) in terms of performance? Does it make any difference?
    C) Does the upper case and quotes make any improvements?
    D) Does any of this influences SQL statement processing phases?


    Thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You can easily test it.
    Run/trace both versions of the query and post results.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Sep 2007
    Posts
    36
    >>but recently a software tester told me that I should code like "Query A"

    A tester will no doubt find query A more 'readable'

    >>you should always use upper case.

    ALWAYS? FOR EVERYTHING? personally I like upper case only on RESERVED words.

    >>you should put quotes in column and table names

    Personal preference. It's up to you.


    >>you should use the JOIN syntax because it's the recommended syntax (ISSO/IEC 9075 SQL:2003)

    If it's part of that ISO standard, and you want to meet that standard, then you probably should use it.

    >>And he justifies this with performance improvements.

    I'd ask for proof.
    As PAVB suggests, how about some numbers to back it up?


    >>A) Does this make any sense?

    The questions make sense.

    >>B) Is the JOIN syntax better than the Oracle syntax (+) in terms of performance? Does it make any difference?

    No. To readability, yes. To the ISO, probably

    >>C) Does the upper case and quotes make any improvements?

    No. To readability, yes for the case; for me No for the quotes.

    >>D) Does any of this influences SQL statement processing phases?

    Unlikely.

Posting Permissions

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


Click Here to Expand Forum to Full Width