-
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
-
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.
-
>>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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|