SQL Dumbass

Fighting dumbasses, one query at a time…

Lets join on two fields, wait, lets send those two fields to a functino to scrub them first, then join

Clients db is runing slow.  I hear about it thru the normal grapevine.  I look into Spotlight to see what is going on.  Sure enough, seems like its getting hit hard.  But before i can investigate more, it clears itself up. 
Few minutes later, slow again.  Clears up again.  Something up…
So start digging.  Notice a job is always running when slowness is reported.  Start asking questions.  Turns out some new code was rolled just the other day, that is called from this job. 
Lets look…
First proc i look into has an odd query.  Seems we are joining an invoice_no field between tables, but we dont know if this data has been scrubbed, so its being sent to a function to scrub it.
  WHERE  DBO.UDF_REMOVE_BADCHARS(H.INVOICE_NO) = DBO.UDF_REMOVE_BADCHARS(P.INVOICE_NO) 
Wow, nice code there… lets do a table scan… and this is done in multiple places, in multiple procs, which are all called from this job, that runs every minute… wooohooo

rollback.  rewrite.

(I know what you are saying now… how did this get by me?  well, we are not part of the code review process.  We just roll it.  ARGH!!)