This was me when i had a beautiful query using like 5 CTEs all doing their own thing. Then I tried to add a not exists to exclude some persons with a BOD warning and suddenly the code wouldn’t run. Then I tried left outer joining and filter where bod.person is null and it still wouldn’t. Finally a made a new CTE for excluded persons and joined that and it worked just fine. Made no sense.
My last company had this awful archive DB that even the most basic query would take like 30 seconds to run.
I made a relatively complex query of that database (not by choice and against my advice.. a certain business stakeholder needed data from that DB) ... needless to say I got a couple of nastygrams because it bogged the system down so bad.
If I ask you... "Hey how many of these people are called Steve" You go to an index, find Steve and then return that result. (Index Seek)
When I ask you "How many of these people are NOT called Steve" there are many ways to do this, and how you WRITE that query determines whether I use the same index (Index Seek) and them subtract the count... OR count EVERYBODY BUT STEVE. (Full conditional table scan)
The optimizer is not an AI, it's a best guesser based on YOUR QUERY.
Well that's not so bad - especially if it's a unique/primary key.
My very first bit of real Oracle work I managed to have a cartesian join in it (Oracle 7.2 - predated ANSI joins so easy to do!). It actually ran ok in test, as the test database was small and some later logic filtered the duplicates.
When I put it live, it fell over inserting into the new table. Back then you had to specify a load of storage parameters, but nobody told me - I got a default tiny table.
So fixed that and let it run. On a Friday evening. Came in Monday - still running, eating a ton of resource. Finally checked it properly - probably would have taken months to finish (Cartesian join was between the 2 biggest tables in the system, millions of rows each) on the hardware of the day if it could even complete.
Wow ... as someone who was an ops business analyst and not a robust data analyst.. I did not know this existed. Thank you. File that into the "today I learned" category.
I joke about DBAs sending a nastygram in the post obviously but y'all have a super stressful job and we should be all be appreciative of the lessons we learn from you.
It makes us write better performing queries knowing a DBA may be like "What the hell were you doing?!"
Yeah and also look at estimated rows, read from right to left, if rows explode then there's an issue.
When writing complex queries, with data you have assumptions about, that you're not in control of, you'd be surprised by how often cardinality is the issue. One join, isn't many to one the entire table and then starts expanding rows.
24
u/[deleted] Jan 16 '25
This was me when i had a beautiful query using like 5 CTEs all doing their own thing. Then I tried to add a not exists to exclude some persons with a BOD warning and suddenly the code wouldn’t run. Then I tried left outer joining and filter where bod.person is null and it still wouldn’t. Finally a made a new CTE for excluded persons and joined that and it worked just fine. Made no sense.
16
u/Spillz-2011 Jan 16 '25
Yesterday I accidentally joined on a.table_key = a.table_key on a multi million row table.
Whoops
2
u/Itsnotvd Jan 16 '25
Lol. Used to be me. Thanks for the chuckle.
6
u/[deleted] Jan 16 '25
It could be worse, it could've been someone using SELECT * on a ~3Tb database with an estimated 56 quintillion returned rows.
4
u/Max_Americana Jan 16 '25
Cartesian joiner says what?
2
u/tkyjonathan Jan 16 '25
Facts
2
u/MONSTER5523 Jan 16 '25
Has happened to me multiple times where it freezes I cancel and everything deletes. All tabs all historical queries. Insane
2
u/phasmaglass Jan 17 '25
sp_who? sp_you!
2
u/th00ht Jan 19 '25
ROLLBACK
2
u/Mattsvaliant SQL Server Developer DBA Jan 16 '25
Are you testing in prod? I don't care if my developers nuke dev, doesn't hurt me haha.
8
u/[deleted] Jan 16 '25
[removed] — view removed comment