Lets join on two fields, wait, lets send those two fields to a functino to scrub them first, then join
October 25th, 2007
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!!)
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!!)