So... I maintain a series of legacy applications. Not quite MUMPS-level Legacy, but there are sure some interesting code choices. Much of it is ASP Classic code of dubious origin. There's a lot of SQL that often reads like it was procedurally generated, but I've come to know better.
Today, I get informed that there's a problem with one of the error audit utilities. This struck me as particularly bad news because, after years of this, I'd never seen an anything audit utility except the ones I created. As it turns out, there are a bunch of them, which run on a nightly-replicated copy of the database, and which I'd never had access to. Fun times. In any case, one of them, having something to do with inter-application data-sharing messages, has "suddenly" started hanging. I know there haven't been code or DB deployments for awhile, so I'm dubious of the timing. After a little inquisition, the guy running the reports admits this hadn't been run for 3 years, but they just failed a Best Practices check from the Auditing Department and so it is suddenly urgent again.
It doesn't take me long to realize why. Here's the problematic SQL query. This isn't a stored procedure, it's built on-the-fly in the code. A1 through A4 are ... magic numbers, so far as I can tell, the result of hundreds of lines of incomprehensible VBScript.
SELECT M.MsgID, MsgEntity, Sender, SentFor, SentAt, SUM(CASE WHEN (StatusType = 2) THEN 1 ELSE 0 END), SUM(CASE WHEN (StatusType = 11) THEN 1 ELSE 0 END), SUM(CASE WHEN (StatusType = 12) THEN 1 ELSE 0 END)
FROM dbo.Msgs AS M LEFT JOIN (SELECT MsgID, StatusType FROM dbo.MsgStatusLog WHERE StatusBy = A1) AS S ON M.MsgID = S.MsgID
WHERE ((RType = 0 AND Rec = A1) OR (RType = 2 AND Rec IN (A2, A3, A4)))
GROUP BY M.MsgID, MsgEntity, Sender, SentFor, SentAt
HAVING SUM(CASE WHEN (StatusType = 3) THEN 1 ELSE 0 END) = 0
ORDER BY SentAt DESC, M.MsgID DESC
Each attempt to run this tool executes this query ~10 times with varying values for A1, A2, A3, and A4, then does math on the results. For some reason. This apparently worked okay, 3 years ago, when there were a few hundred entries in the two tables. There are now a few hundred thousand in dbo.Msgs, and just shy of a million in dbo.MsgStatusLog. Somehow, this didn't scale well. I can't imagine why not. Purging records isn't an option, because no one seems to have any idea what the appropriate data retention policy would be. Of course.
Honestly, I'm probably going to wind up rebuilding this from first principles, because I don't have the damnedest clue what that SQL is intended to do, or how to unroll it into a less server-molesting form. If anyone wants to take a guess, um, please feel free!