If you don’t ask, you will never find out why your system is performing poorly

We had a large operation that took up to a minute to run.  It was copying data to a staging area, running over a thousand data validation rules (~50,000 lines of code), and then formatting all of the validation errors for viewing.

Seven years ago, when we first put the procedure into production, it took about 20 seconds to execute and was never thought about again.

Recently, we wanted to reuse the same routine in a different system.  In the new system, there was motivation to run the routine over and over again, so a 20-second execution time was uncomfortable.

Therefore, we asked the question, what is this routine actually doing?  In about a minute we white-boarded the following steps:

1)    It is reading a few hundred records (one logical customer file) using indexed searches.

2)    It writes all of those records into a staging area.

3)    It runs 1500 little data validation routines against the staging area.

My intuition told me that none of those steps should take more than a second each, so if the routine was taking over 5 seconds, something was wrong. In our initial tests, the routine was taking up to a minute. Hmmmm….

After about an hour of analysis on the routine, we found that there was a single view with a function in the SELECT statement that had a cursor in it.  That function was also calling a function that had a cursor in it.  The view was returning about 80,000 records, so we were doing 160,000 context switches and queries. The really stupid thing is that all of the columns we needed were already in the view, so the function calls were completely unnecessary.

In a couple of hours, we fixed the problem and now the routine takes about 3 seconds.

I am sooooo embarrassed.