SQL Dumbass

I was reviewing procedures from one of our dev teams today when I saw this (names to protect someone and there were a bunch more parameters, and yeah, I doubt this, as typed would compile, work with me):

CREATE PROC
@PK int
@Val1 varchar(50) OUTPUT
@Val2 datetime OUTPUT
@Val3 int OUTPUT
AS

IF EXISTS (SELECT 1 FROM Table1 WHERE pk = @PK)
BEGIN
                SELECT @Val1 = Val1 FROM Table1 WHERE PK = @PK
                SELECT @Val2 = Val2 FROM Table 1 WHERE PK = @PK
                SELECT @Val3 = Val3 FROM Table 1 WHERE PK = @PK
END
ELSE
BEGIN
                RAISERROR(‘We don’t have any rows that match that pk’)
END
 
If I could guarantee a jury of DBA’s, I know I’d be acquitted… Instead, I rewrote it and tried to educate them
 
SELECT @Val1 = Val1
                ,@val2 = Val2
                ,@val3 = Val3
FROM Table1
                WHERE PK = @PK
                And Val3< > 42
 
IF @@ROWCOUNT = 0
                RAISERROR(‘We don’t have any rows that match that pk’)
END
 
The real query went from more than a second to less than 50ms and the reads against the table went from several hundred to 2. All that and I still have to justify to developers why they send their code through for review…

We have a system that stages some data.  A job fires off and picks up the data from the stage db, pushes it to the real db, performs a few edits on it, and then goes onto the next record.  Sounds simple.

Customer calls to complain that the system is too slow.  10s of hours waiting for items to reach them…

I start digging to get better details of the system.  The above is a generalization.
What happens is the job calls a proc.  This proc says, get me the top 1 item from stage db.  Then calls another proc with this ID.  This proc in turn does some insertion into multiple tables.  The last of which has a trigger.  The trigger sends this ID thru a series of edits.  Once done, there is some clean up procs called for this ID and eventually the stage data is removed.  Thus returning to the original proc.  It then says, are there any records left in the stage table?  if so, get me the top 1 item from the stage table.  And repeats.
The trigger turns out to process some 139 rules.  53 of which are rules comparing one field to be equal to a value.  the rest are comparing one field to be like a value.  There is a priority to these rules.  If you look at the history, the last rule, is the one most picked, eventually, by the system.  So, 139 rules must be traversed before it can perform an action.  139 checks on values.  And keep in mind that this is in a trigger, while we are moving data from stage to real db.  If it takes 2 minutes to process all this, and we have received 50 new items in those 2 minutes, you can imagine the backlog. 

Thus the delay.  Poor job, on occasion, was running for 9 hours straight.  Trying to get everything thru the system.
So, a little research reveals that the priority of the edits could be drastically altered, and the most picked edits moved up the priority list.  This requires some work on the operations staff.  They are duely informed.  Nothing happens. 

Last night, same process occurred.  Same slowness.  I’m just waiting for the email to complain about slowness….

Working in a dot.com as a dba. This particular dot.com was extremely concerned with getting as much code out the door as humanly possible. They weren’t terribly concerned with the quality of the code or any of the data. We were collecting over a gb of data every day in SQL Server 7.0. To say the least, the system required serious babysitting, monitoring disk space, log space, growth, etc. The DBA’s spent a considerable amount of time, figuring out how best to monitor & respond to the crazed behavior we got in the system. But that didn’t sit well with the business majors running the company (“all from top schools”).

So we got the word one day, stop spending time messing with maintenance and spend more time churning out code with the developers. Less than a week later, one night a strange spike in activity (right after a new code release that we didn’t monitor) filled the log drive. We tried recovering the database, but nothing worked. We spent three straight days in the office, first attempting to get all the data back, then restoring an older backup, then fixing the code that kept breaking the system. After three days without sleep, we had the system back online. Our glorious kid managers didn’t send a thank you or an atta-boy, but they did send an email instructing us to begin monitoring the systems, especially during and after code releases.

All three dba’s quit within three months of that incident and the company went the way of the dodo less than a year later.