• Jon (unregistered) in reply to Maurits
    Maurits:
    I've never used ACOS, but I have used ASIN in T-SQL: (snip)
    That's quite the ASINine code you have there.
  • Tuning (unregistered) in reply to Arachnid

    Saying "the DB simply needs to do a union on the results from each index" is, IMHO, tantamount to saying "you do not need any indexes, since a full table scan will do".

     

    Assume the table is huge, performance is important and the result set from any part of the query is also huge but the sought after combination is small or even uique.

  • (cs) in reply to Maurits

    Maurits:
    I've never used ACOS, but I have used ASIN in T-SQL:
    CREATE FUNCTION DistanceBetween
    -- given the latitude and longitude of two points on the surface of the earth
    -- this returns the distance in miles between them as the crow flies
    -- The first point is (@lata, @longa)
    -- The second point is (@latb, @longb)
    (
        @lata float,
        @longa float,
        @latb float,
        @longb float
    )
    RETURNS float
    AS
    BEGIN

    RETURN
    <FONT style="BACKGROUND-COLOR: #ffffff" color=#008000>-- *SNIP* -- COUNT'EM -- 60+ LINE EXPRESSION
    </FONT>    END

    You're not storing intermediate results in local variables with descriptive names that describe what you're doing! WTF are you, a parser? Are your children called child[0], child[1] and child[n]? I think I'm gonna copy/paste this in the submit box for Alex....

  • Oli (unregistered) in reply to Maurits

    Maurits:
    I've never used ACOS, but I have used ASIN in T-SQL:
    CREATE FUNCTION DistanceBetween
    -- given the latitude and longitude of two points on the surface of the earth
    -- this returns the distance in miles between them as the crow flies
    -- The first point is (@lata, @longa)
    -- The second point is (@latb, @longb)
    (
        @lata float,
        @longa float,
        @latb float,
        @longb float
    )
    RETURNS float
    AS
    BEGIN

    RETURN
    (    (
            6367 *
            0.6214
        ) *
        

     * snip *

     

        )
    )
    END

     

    The REAL wtf is this code.

  • Martin Vilcans (unregistered)

    This ACOS(-1) stuff brings back memory of how numbers were stored in the ZX Spectrum's BASIC format. For example, if you entered the following code to make the screen border black (color 0):

    10 BORDER 0

    That would be stored in memory and on file like this (hex dump):

    00 0A (2 bytes for the line number)
    AF    (code for border)
    30    (ascii for '0')
    0E    (special ascii code meaning "number")
    WW XX YY ZZ (floating point data for 0)

    This is obiously to avoid parsing the number each time the line is run, but sacrifices a few bytes of memory. So a common memory optimization was:

    10 BORDER PI-PI

    Where PI is a reservered word and thus stored as one byte. If you want the number 1 instead of 0, you could use for example PI/PI.

    Perhaps this guy was an old-school programmer? It's obvious that we has no clue about present day software development priorities.

  • Anonymous Coward (unregistered) in reply to ammoQ
    ammoQ:
    travisowens:

    I could point out the concept that many people feel it's bad to do complex math within your database server as your coding language of choice will always perform it faster.  This is even more important when you look at the past where the database server is far more overloaded than your app server, but now that I/O has improved somewhat, this isn't as much of an issue as it use to be.



    I see no reason why Java or C# or C++ on an app server would calculate ACOS faster (or slower) than the database (unless the Oracle guys wrote the ACOS alorithm in PL/SQL themself)


    You really don't see a reason why native code will execute faster than its -interpreted- counterpart?
  • (cs) in reply to CornedBee
    CornedBee:
    I completely failed to understand this code. I didn't even really try.

    Where in Vienna, ammoQ?


    9th District (Alsergrund)
  • Robert (unregistered) in reply to Otto

    As 6 billion people walk along the surface of the earth, we're bound to be wearing it down! Certainly it gets smaller and smaller each year.

  • (cs) in reply to Otto
    Otto:

    MOD(FLOOR(VLOOPER /4), 2), therefore, will produce 0,0,0,0,1,1,1,1, in that order.

    MOD(FLOOR(VLOOPER /2), 2) will produce 0,0,1,1,0,0,1,1.

    And MOD(FLOOR(VLOOPER), 2) will produce 0,1,0,1,0,1,0,1.

    I can't comment further, because I don't know VB and thus don't have any idea what DECODE() will do.


    i.e. "VLOOPER >= 4", "MOD(VLOOPER, 4) >= 2" and "MOD(VLOOPER, 2)" rewritten in a thoroughly unreadable way.

  • (cs) in reply to Alexis de Torquemada
    Alexis de Torquemada:
    Otto:

    MOD(FLOOR(VLOOPER /4), 2), therefore, will produce 0,0,0,0,1,1,1,1, in that order.

    MOD(FLOOR(VLOOPER /2), 2) will produce 0,0,1,1,0,0,1,1.

    And MOD(FLOOR(VLOOPER), 2) will produce 0,1,0,1,0,1,0,1.

    I can't comment further, because I don't know VB and thus don't have any idea what DECODE() will do.


    i.e. "VLOOPER >= 4", "MOD(VLOOPER, 4) >= 2" and "MOD(VLOOPER, 2)" rewritten in a thoroughly unreadable way.



    While expressions such as VLOOPER >= 4 will translate to 0 or 1 in C or C++, I highly doubt you can make such an assumption in PL/SQL.  I know for a fact that there's no direct way in T-SQL to convert a boolean expression such as those above to an integer.
  • (cs) in reply to John Smallberries
    John Smallberries:
    <font size="2">
    Anonymous:
    Don't ban all the code... just ban features that make our jobs easier :)

    Doesn't code make your job easier?
    Try writing software without it.</font>


    No, code makes my job harder.    EVERY single problem I deal with is caused by code.   Either complex code that I need to write (ideally in a simple way), or more likely bugs in code that has been written (often by me...).   

    Every once in a while I have a problem with management, but those are tiny compared to all the hard problems I have caused by code.

    Of course without code I wouldn't have a job at all, so I don't dare say too much.   Still, essentially all my problems are caused by code these days.
  • (cs) in reply to Oliver Klozoff
    Oliver Klozoff:

    While expressions such as VLOOPER >= 4 will translate to 0 or 1 in C or C++, I highly doubt you can make such an assumption in PL/SQL.  I know for a fact that there's no direct way in T-SQL to convert a boolean expression such as those above to an integer.


    You don't need to convert them to an integer, you only have to replace the following zero in the DECODE calls with FALSE in the first two cases.

  • A. Nonny Mouse (unregistered) in reply to Otto

    Otto:
    See, just one more reason not to have constants in your code.
    i quite agree

    why write 3.1416 when you can use PI instead

    this also future-proofs your code, you know, just in case the value of PI changes some day...

     

  • (cs)

    On a side note... If there's one thing I can't stand about T-SQL, it's the general "standard" that just about everything is upper-cased.  It drives me batty to look at it.  Imagine my recent horror when our DBA decided to work that into our coding standards.  Yuck.

    Does anyone know how that standard came to be?

  • (cs) in reply to Alexis de Torquemada

    I just realized that PL/SQL has a BITAND function, so the "mod magic" is even more of a WTF.

  • christoofar (unregistered) in reply to Schroeder
    Schroeder:

    On a side note... If there's one thing I can't stand about T-SQL, it's the general "standard" that just about everything is upper-cased.  It drives me batty to look at it.  Imagine my recent horror when our DBA decided to work that into our coding standards.  Yuck.

    Does anyone know how that standard came to be?

    ]


    IBM, the people that brought you present-day SQL to begin with (http://www.answers.com/DB2).

    If you have been exposed to any IBM culture, you will quickly learn that PGMRS love to use UPPRCASE identifiers that are FOUR or EGHT characters in length, hence why so many DB2 database tables have column names like ACTNMBR, DEPTCODE, etc.

    The practice continues to live on despite IBM's shift to Java.
  • christoofar (unregistered) in reply to christoofar

    Actually, go even go back farther than DB2, which the query language embraced the uppercase statement nomenclature... the practice of using CAPS only was mostly due to the fact that prior to the IBM 5110, most IBM machines didn't have lower case on their code maps and most people didn't care.

    IBM later added that capability to the System/370 and was later on the System/23 Datamaster (the immediate predecessor to the IBM PC), IBM Displaywriter, and System/36 when the noticed that people had this fun idea of actually typing documents up and storing memos on a computer rather than just using a computer to print out statements and bills.

  • (cs) in reply to Schroeder
    Schroeder:

    On a side note... If there's one thing I can't stand about T-SQL, it's the general "standard" that just about everything is upper-cased.  It drives me batty to look at it.  Imagine my recent horror when our DBA decided to work that into our coding standards.  Yuck.

    Does anyone know how that standard came to be?


    I hate that too.
    We've decided to move away from that convention, in favor of the camel/Pascal case scheme.

    As my former DBA pointed out, an impedement is that the BOL (books on-line documentation that ships with SQL Server) and much of the literature still use the uppercase keyword convention.

    I worked at an Informix shop that had a mix of conventions. That in itself is bad, but what really made it a nightmare is that the SQL engine was case sensitive. We had to remember that ACCOUNTS was upper case, but Departments was simply capitilized. And yes, they used plurals for entity names, too.
  • (cs) in reply to John Smallberries

    John Smallberries:
    I worked at an Informix shop that had a mix of conventions. That in itself is bad, but what really made it a nightmare is that the SQL engine was case sensitive. We had to remember that ACCOUNTS was upper case, but Departments was simply capitilized. And yes, they used plurals for entity names, too.

    Nice.  We have case sensitive databases as well.  Two tables, same name, different capitilization (i.e. "blah" vs. "Blah") same table structure and roughly the same data.  Which one do I use???  Welcome to my hell.

  • (cs) in reply to John Smallberries
    John Smallberries:

    And yes, they used plurals for entity names, too.


    That's a bad thing?  I always kinda thought that's just how it's done.  It doesn't seem unreasonable to me to name a table "Accounts" if it contains information about a bunch of different accounts.  Of course, I may feel this way because the database I'm currently maintaining has table names consisting of less than 5 upper case letters plus underscores...-anything- seems better to me than that.
  • Just Another WTF (unregistered) in reply to Schroeder
    Schroeder:

    John Smallberries:
    I worked at an Informix shop that had a mix of conventions. That in itself is bad, but what really made it a nightmare is that the SQL engine was case sensitive. We had to remember that ACCOUNTS was upper case, but Departments was simply capitilized. And yes, they used plurals for entity names, too.

    Nice.  We have case sensitive databases as well.  Two tables, same name, different capitilization (i.e. "blah" vs. "Blah") same table structure and roughly the same data.  Which one do I use???  Welcome to my hell.

    Sounds like a primitive 'load balancing' scheme

  • (cs) in reply to UncleMidriff

    UncleMidriff:
    That's a bad thing?  I always kinda thought that's just how it's done.  It doesn't seem unreasonable to me to name a table "Accounts" if it contains information about a bunch of different accounts.  Of course, I may feel this way because the database I'm currently maintaining has table names consisting of less than 5 upper case letters plus underscores...-anything- seems better to me than that.

    A database purist will tell you that table names should always be singular.  Sure, the 'Customer' table is a collection of customers so on the surface it makes sense to refer to it in the plural.  The problem is that each row represents a single entity.  Thus, when you say 'Customers.LastName' it actually sounds kind of stupid because you're really looking for the last name of a single customer.

    It's not such a huge deal.  It's one of those things that people "in the know" will scoff at and snicker at you because they feel that it seperates the men from the boys in terms of database design.  Frankly, I can go either way on it, but I distinctly remember the guy I learned SQL from insisting that tables never be named in the plural (for the reasons I described) and it kind of stuck.

    I think there are bigger naming issues in the world of database design.  Like prefacing every table with the name of the system (i.e. "MyStupidSystem_Customer", "MyStupidSystem_Accounts") or prefacing every stored procedure name with "sp_" or, for that matter, prefacing anything with any consistent and worthless naming convention that just forces you to type those extra few characters on front of everything you do.

  • (cs) in reply to UncleMidriff
    UncleMidriff:
    John Smallberries:

    And yes, they used plurals for entity names, too.


    That's a bad thing?  I always kinda thought that's just how it's done.  It doesn't seem unreasonable to me to name a table "Accounts" if it contains information about a bunch of different accounts.  Of course, I may feel this way because the database I'm currently maintaining has table names consisting of less than 5 upper case letters plus underscores...-anything- seems better to me than that.

    The logic for using singular entity names is that you typically refer to an instance (row) of the data. This lends itself to declarative statements like "an Account <is associated with> a Person". If things are appropriately normalized, an instance should never be a collection of things, but a single thing.
  • (cs) in reply to UncleMidriff
    UncleMidriff:

    ....the database I'm currently maintaining has table names consisting of less than 5 upper case letters plus underscores...-anything- seems better to me than that.

    Anything?
    The Informix database I mentioned had mostly 2 character entity names: AA, AB, AC...ZY, ZZ.
    *puke*
  • (cs) in reply to John Smallberries
    John Smallberries:
    UncleMidriff:

    ....the database I'm currently maintaining has table names consisting of less than 5 upper case letters plus underscores...-anything- seems better to me than that.

    Anything?
    The Informix database I mentioned had mostly 2 character entity names: AA, AB, AC...ZY, ZZ.
    *puke*


    I -might- be able to live with poorly named tables if the column names made any damn sense.  But in the database I mentioned, it's all S_C_S_L.F_A, S_C_S_L.I_1, S_C_S_L.I_2, S_C_S_L.I_3 and so on.  Yearrgh!

    The only thing I can think of that'd be worse would Table1.Field1, Table1.Field1, Table2.Field3, etc...

    The convention of not using plurals for table names makes sense to me now.  I'll be keeping that in mind from now on.
  • Anonymous Cowboy (unregistered) in reply to GalacticCmdr
    GalacticCmdr:
    When you wrote this code were you?

    A) Drunk
    B) On Crack
    C) Both A and B
    D) Buggering the nearest LISP book

    Maurits:
    I've never used ACOS, but I have used ASIN in T-SQL:
    CREATE FUNCTION DistanceBetween
    -- given the latitude and longitude of two points on the surface of the earth
    -- this returns the distance in miles between them as the crow flies
    -- The first point is (@lata, @longa)
    -- The second point is (@latb, @longb)
    (
        @lata float,
        @longa float,
        @latb float,
        @longb float
    )
    RETURNS float
    AS
    BEGIN

    RETURN
    (    (
            6367 *
            0.6214
        ) *
        (
            2 *

    ... snip Pabst drinking/Crack-smoking code ...

       )
        )
    )
    END


    You missed it.  Squint your eyes and look at the monitor from about 4 feet away, and you'll see that he's cleverly encoded the sine wave in the whitespace in front of the parenthesis.

    Actually, we have a guy here who writes code with a similarly impaired sense of readability, who must think that we all read his code on some mythical four-foot tall monitors.  We used to yell at him, now we just run prettyprint on his code before reading it so we can maintain it..
  • Code Complete (unregistered) in reply to Schroeder
    Schroeder:

    I think there are bigger naming issues in the world of database design.  Like prefacing every table with the name of the system (i.e. "MyStupidSystem_Customer", "MyStupidSystem_Accounts") or prefacing every stored procedure name with "sp_" or, for that matter, prefacing anything with any consistent and worthless naming convention that just forces you to type those extra few characters on front of everything you do.

    Actually, naming stored procedures "sp_<XXX>" is a true WTF. Those procedures actually run more slowly because the name lookup starts by looking in the wrong place. Also, there may be future built-in stored procedures with the axect name chosen, causing no end of trouble.

  • (cs) in reply to Code Complete
    Anonymous:
    Schroeder:

    I think there are bigger naming issues in the world of database design.  Like prefacing every table with the name of the system (i.e. "MyStupidSystem_Customer", "MyStupidSystem_Accounts") or prefacing every stored procedure name with "sp_" or, for that matter, prefacing anything with any consistent and worthless naming convention that just forces you to type those extra few characters on front of everything you do.

    Actually, naming stored procedures "sp_<xxx>" is a true WTF. Those procedures actually run more slowly because the name lookup starts by looking in the wrong place. Also, there may be future built-in stored procedures with the axect name chosen, causing no end of trouble.</xxx>


    The bigger WTF is why SQL Server examines the leading 3 chars of a proc name to see if it's a system proc.

    Doesn't almost every system probe starting with the local environment (database, folder, class, context, etc.) then move outward?
  • (cs) in reply to Schroeder
    Schroeder:

    A database purist will tell you that table names should always be singular.  Sure, the 'Customer' table is a collection of customers so on the surface it makes sense to refer to it in the plural.  The problem is that each row represents a single entity.  Thus, when you say 'Customers.LastName' it actually sounds kind of stupid because you're really looking for the last name of a single customer.



    Tell that to Joe Celko. (He helped write the SQL-89 and SQL-92 standards.) Quoting an article: "Use plural or collective nouns for table names since they are sets and not scalar values."
    I've also seen him rant on the SQL Server newsgroup about this particular issue on several occasions.
  • (cs) in reply to Alex Papadimoulis
    Alex Papadimoulis:

    Mike R:
    Hey, wasn't this discussed in a a previous post?

    That discussion prompted me to post this one :-). It kinda ... sorta makes sense why they used ACOS(-1) ... I'll break down a predicate:

    NVL(BCTRY_ID, TO_CHAR(ACOS(-1))) = DECODE(MOD(FLOOR(VLOOPER /4), 2), 0, VCTRY_ID, TO_CHAR(ACOS(-1)))

    If IsNull(BCTRY_ID) Then
      LeftOperand = PI
    Else
      LeftOperand = BCTRY_ID

    If Floor(VLOOPER/2) Mod 2 = 0 Then
      RightOperand = 0
    Else
      RightOperand = PI

    What still gets me is WTF is with the Floor/Mod nonsense. Any ideas?

    I'll give you a hint.

    000
001
010
011
100
101
110
111

    It's converting vlooper into a binary representation to get all possible combinations of true/false.

  • (cs) in reply to Schroeder

    Schroeder:
    I think there are bigger naming issues in the world of database design.  Like prefacing every table with the name of the system (i.e. "MyStupidSystem_Customer", "MyStupidSystem_Accounts") or prefacing every stored procedure name with "sp_" or, for that matter, prefacing anything with any consistent and worthless naming convention that just forces you to type those extra few characters on front of everything you do.

    Back in my Web development consulting days I used to develop on a single box. Of course, the Internet provider would only let me have one database. So what I did is assign each project a TLA and start all the tables pertaining to that project with that TLA. It wasn't that much harder to type 3 more letters per table per query, but allowed me to have literally hundreds of tables in the same database without ever losing my mind about what table belongs to what project.

  • (cs) in reply to joost

    joost:
    You're not storing intermediate results in local variables with descriptive names that describe what you're doing! WTF are you, a parser? Are your children called child[0], child[1] and child[n]? I think I'm gonna copy/paste this in the submit box for Alex....

    <FONT face="Courier New" size=2>hey, awesome icon.  it's one of my favorite movies.</FONT>

  • (cs) in reply to zinglons_ale
    zinglons_ale:
    Schroeder:

    A database purist will tell you that table names should always be singular...



    Tell that to Joe Celko...

    Joe Celko can do what he wants.

    Maybe he works completely in SQL, and his conventions work for him. I have to program in about a dozen different languages throughout the day, and a consistent style works much better for me.

    Humans (and software developers in particular it seems) are very good at pattern recognition. Keywords like "select" leap off the page to me without "visual cues" like all caps.
  • (cs) in reply to John Smallberries

    sorry, that last post didn't really match what I quoted...

    On the issue of singular/plural entity names:
    I maintain that singular is the way to go from a data modeling perspective. Relationships exist between instances, not entire entities. Attributes also apply to instances; Person.LastName looks fine but Persons.LastName doesn't really make sense. Thinking in terms of instances when modeling leads to clearer designs.

  • (cs) in reply to John Smallberries
    John Smallberries:
    sorry, that last post didn't really match what I quoted...

    On the issue of singular/plural entity names:
    I maintain that singular is the way to go from a data modeling perspective. Relationships exist between instances, not entire entities. Attributes also apply to instances; Person.LastName looks fine but Persons.LastName doesn't really make sense. Thinking in terms of instances when modeling leads to clearer designs.


    I agree.  That's why class names are singular.  Well, except for System.Drawing.Graphics.
  • (cs) in reply to Maurits
    Maurits:
    John Smallberries:
    sorry, that last post didn't really match what I quoted...

    On the issue of singular/plural entity names:
    I maintain that singular is the way to go from a data modeling perspective. Relationships exist between instances, not entire entities. Attributes also apply to instances; Person.LastName looks fine but Persons.LastName doesn't really make sense. Thinking in terms of instances when modeling leads to clearer designs.


    I agree.  That's why class names are singular.  Well, except for System.Drawing.Graphics.

    I would argue that System.Drawing.Graphics is really a collective singular, not plural.
    We say "System.Drawing.Graphics is slow" not "System.Drawing.Graphics are slow".
  • (cs) in reply to Oliver Klozoff

    Oliver Klozoff:

    I know for a fact that there's no direct way in T-SQL to convert a boolean expression such as those above to an integer.

    SELECT CASE WHEN Field1 > Field2 THEN 1 ELSE 0 END FROM Table

Leave a comment on “Relational Trigonometry”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article