SQL In Terror.
-
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.
-
oid. oid. oid.
Ugh...non-ANSI joins,
SELECT
s in yourSELECT
s (yo dawg). Are the underscore + number suffixes victims of anonymization, or did they really do that?
-
oid. oid. oid.
-
We use SELECTs in the SELECT list as well. The advantage over joins is that you'll never get duplicated rows.
-
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.
-
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).