Re: Using all uppercase for SQL

[Jerry Coleman <colemanj@WASHPOST.COM>]

As for Oracle, SQL embedded in SQR uses bind variables, so no re-parsing
should be taking place after the first time.  In Oracle 8i (maybe 8.1.6
specfically), there is an init.ora parameter called CURSOR_SHARING, which
can accept literals (SELECT EMPLID FROM PS_EMPLOYEES WHERE NAME LIKE
'Smith%") and it will substitue a bind variable for the 'Smith%' literal.
This can be helpful because some SQLEXECS in PeopleCode don't appear to use
bind variables, depending on how they're written.

Case has no bearing on performance.  Who knows, maybe the parser has to
convert a statement to upper case, and years ago this would have been an
issue, but now, CPU's being what they are, it is not.





                    the dragon
                    <ceprn@HOTMAIL        To:     SQR-USERS@list.iex.net
                    .COM>                 cc:
                    Sent by:              Subject:     Re: Using all uppercase 
for SQL
                    "Discussion of
                    SQR, Brio
                    Technology's
                    database
                    reporting
                    language"
                    <SQR-USERS@lis
                    t.iex.net>


                    01/31/01 07:58
                    PM
                    Please respond
                    to sqr-users





>From an Oracle standpoint, this is partially a correct statement, but only
to the point where it relates to uniformity.  The compiler will execute a
sql statement faster if it looks *exactly* like a statement it already has
in memory, regardless of the case - for example:

select sysdate from dual; (first execution)
select sysdate from dual; (second execution)

the second execution will run faster, because it has already been compiled.
However, if you change even 1 character, even a space added, then the sql
has changed, and it will have to recompile again.

So then, from a consistency viewpoint, if you have the above all in upper
case, it will execute faster on subsequent executions as long as it is
exactly the same.

This is from oracle optimization class for v6.0...  I assume it has
remained
this way.

clark 'the dragon' willis
dragon enterprises, consulting services


----Original Message Follows----

Hi Tom,

To the best of my knowledge there is nothing about the case that
increases or decreases efficiency but I do know that MS SQL Server is
case sensitive so if you are writing code that will be used on different
platforms you should write it all in uppercase.

Cameron

Tom Dommermuth wrote:

 > Someone told me that we should type all of our SQL code in SQRs in
 > uppercase so that the SQL commands are processed more efficiently.
 > Anyone heard of this?

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com