SQL In Terror.

Buddy

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

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.

boomzilla

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?

PJH

@boomzilla said:

oid. oid. oid.

http://api.ning.com/files/rwropZPUhbgUbFmnxAiZCS-OYz93D6E41*ogLOztysICj2qlfJZ8wTKAJh74fqju09cYBGGGAzBiiSf5mAo0fCQuSVib5QIpYZH-p8iHnr4_/Ood.jpg

PleegWat

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

Jaime

@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.

boomzilla

@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.

Jaime

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.

boomzilla

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.

Jaime

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.

Buddy

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).