SQL In Terror.



  • Continuing the discussion from The WSO₂ Has Come To Pass.:

    @Buddy said:

    when an ‘unrelated’ fuckup in some other area dumped a bunch of extra rows in the db, causing the query to take longer than that, everything went to the dogs.

    Anonymized and misremembered:

    WITH itemsofinterest AS (
      SELECT item.oid itemoid, item.description_3635 itemdesc, etc FROM
        items, actions
      WHERE
        item.oid = action.item_2345 AND
        action.code_2364 in ( 'CREATED',
                              'CREATED_OTHER',
                              'ANOTHER_CREATION_ACTION' ) AND
        -- some other conditions i don't remember right now. AND
        action.creationdate_4352 > sysdate - 1 AND
        action.creationdate_4352 < sysdate )
    WITH results AS (
        SELECT itemoid, itemdesc, 
          (SELECT weight_2346 FROM infoitem WHERE
            item_3478 = itemoid) weight, 
          (SELECT height_8374 FROM infoitem WHERE
            item_3478 = itemoid) height,
            etc × many FROM
        itemsofinterest)
    SELECT * FROM results
    

    Each of those infoitems is a different row. 99% of all infoitem fields are empty. Item is not the only table that infoitem is related to.



  • oid. oid. oid.

    Ugh...non-ANSI joins, SELECTs in your SELECTs (yo dawg). Are the underscore + number suffixes victims of anonymization, or did they really do that?


  • Discourse touched me in a no-no place

    @boomzilla said:

    oid. oid. oid.



  • We use SELECTs in the SELECT list as well. The advantage over joins is that you'll never get duplicated rows.



  • @boomzilla said:

    non-ANSI joins

    When is this practice going to die? I know of a development team that writes only non-ANSI joins "for consistency". Unfortunately, they can't upgrade to SQL 2008R2 or higher because MS dropped support for non-ANSI joins in SQL 2005 and dropped support for SQL 2000 compatibility at the database level in 2008R2.

    I'm sure they'll bitch at Microsoft in 2024 when support is dropped for the last version of Windows that still runs SQL 2008.



  • @PleegWat said:

    The advantage over joins is that you'll never get duplicated rows.

    Just do your joins properly? It always seems like added complexity and poorer performance.



  • That's not an hard and fast rule; I made a query run a hundred times as faster last moth by replacing a join with a subquery.



  • I do subqueries all the time, but as joins or common table expressions, not as part of the, ummm...bit where you're specifying the columns to be returned from the query.



  • SELECT
      EmployeeID,
      EmployeeName = (SELECT Name FROM Employees WHERE EmployeeID = TimeClock.EmployeeID),
      TotalTime = SUM(ClockOut - ClockIn)
    FROM
      TimeClock
    GROUP BY
      EmployeeID
    

    Looks a lot neater than...

    SELECT
      EmployeeID,
      Employee.Name,
      TotalTime = SUM(ClockOut - ClockIn)
    FROM
      TimeClock
        JOIN
      Employees ON Employees.EmployeeID = TimeClock.EmployeeID
    GROUP BY
      EmployeeID,
      Employee.Name
    

    Sometimes it performs faster too.



  • We have a <our original company name>oid.nextval() procedure that gets called for every single insert. Sometimes it fails; that's just a thing that happens that we've learned to live with (well, they have. I'm sure I'll get there some day).

    The suffixes on everything are due to our schema having been designed in some kind of visual editor and auto generated. I anonymized out the two letter prefix that's on every table name (the first two letters of our original company name).


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.