Connor McDonald posts an excellent series of
articles about tuning a database application.
This analysis from a end user perspective reminded me of my own
experiences when I was a technical consultant helping customers
running a large CRM application, typically in call centres scattered
across Europe.
I was often summoned onsite and told to solve the problem that 'The
application is slow'. Usually, different people were eager to give me
their view on the issue:-
-
Oracle DBA's often would be pouring over AWR reports or a
monitoring tool and examining wait events in minute detail.'We can
see multiple ITL waits over 700 ms. This means we need to increase
the FREELISTS for the ORDERS table but the business won't let us
have an outage'.
-
The application developers would also proffer their own
diagnosis - 'Oh yes, we already know what causes that. It a custom
workflow written by the previous integrator. It needs refactoring
but it will take 3 months'.
-
The CEO brusquely told me - 'This CRM application isn't fit for
purpose. If this isn't resolved by Thursday, we're going to
evaluate SAP and Safra will be hearing about this'.
Now, this is all detailed, technical analysis and background providing
useful information to be considered but I would often ask to see the
problem at first hand by talking to an individual who was using this
application all day, every day to see the perspective from his point
of view.
This simple request was often met with puzzlement and resistance by
the technical team - 'Why do you want to watch a user ? We've already
told you what the problem is. This will just waste time'.
Sometimes, this resistance was born out of a concern that the user
feedback would unearth different, unrelated functional issues and
distract me from the performance problems under investigation.
Alternatively, a floor supervisor would air the valid concern that my
conversation with an agent would distract him from dealing with the
customer call. This was easily overcome by letting the agent handle
the call with me simply watching and taking notes. Then, after the
call was finished, we'd have our chat.
On one occasion, this approach of listening to the users proved
particularly beneficial. The client was a utility company but could
have easily been a bank or a telco. The business scenario in the busy
call centre was typical. he, Essentially, the customer calls in with a
query or complaint which is resolved by the agent.
Some call centres use CTI technology where the application looks up
the customer from the inbound telephone number and then presents the
customer details to the agent on the screen so he can start the
dialogue, typically security checks.
However, this call center didn't use CTI so the agent had to manually
search for the customer before the call could commence.
I watched the agent process an entire customer enquiry from start to
finish and took notes.
The call started and after the initial exchange, the agent asked for
customer's surname and started a search. In this example, the customer
was Mr. Johnson. I watched with interest as the agent typed in 'J'
into the customer tab and his 'Search'. This operation took a long
time. There are 66 million people in the UK and 38 million of them
appear in this client's database. Searching a table for all customers
with a surname starting with 'J' is expensive performance wise.
The agent didn't seems phased or perturbed or even irritated as the
hour glass popped up. He merely continued to clear security with the
customer. By the time, this exchange was complete, the search had
finally returned.
My eyes widened as the agent then proceeded to sort all these
thousands of customers by surname and scrolled down page by page
searching for 'Johnson'. Again, sorting a large data set like this is
sub-optimal performance wise. This is an online application where
users are expecting each button click to return within 3 seconds - not
3 minutes. The solution isn't for the DBA to increase the PGA to allow
larger temporary segments to accommodate the massive sort operation.
The solution is not to issue the request to sort thousands of records
in the first place.
It would have been marvellous if the agent had uttered the immortal
words 'Sorry, Mr. Johnson but the system is really slow today'.
Unfortunately, he didn't but you can certainly envisage similar
scenarios where this excuse is proffered.
When the agent finally identified 'Mr. David Johnson' of '23 New
Street, Canterbury, CT2 6AD', the rest of the customer call went
pretty quickly. It was either taking a payment, changing a tariff,
lodging a complaint, a billing enquiry or a change of personal details
and common to most agents working on that floor.
After the call ended, I asked the agent why he used that sequence of
searches and scrolling to identify that specific customer. The answer,
inevitably, was 'We always do it that way and when I joined, that's
what Barry showed me...'
Then we revisited the call using a different technique. This time, I
recommended he searched for the complete surname (he has that
available as soon as the customer starts talking). When he searched
for 'Johnson', the query ran much quicker but there are still probably
thousands of people called 'Johnson' in the UK.
Instead of sorting and endlessly scrolling to locate the customer in
question, I suggested he simply entered the postcode into the
'Address' section. The postcode is now known after the customer
completed the security questions. He could have used customer number
but that's a long 12 digit number with scope for error when entering
it.
[ Ironically, one of his reasons for typing in 'J' instead of
'Johnson' was that 'Hey - I'm pretty lazy and that's a lot of typing'
which resonated with me as that's normally my attitude. ]
The agent just needed to type the first element of the postcode
('CT2') to refine the search further and now we have the customer
details on the screen in a fraction of the time it used to take him.
I thanked him for his time and told him it had been a very interesting
exercise for me to see the application actually in use. He
reciprocated and thanked me. As he went to put his headset back on, he
smiled and said:-
'You're not going to tell my Supervisor about what we've just done,
are you ?'
'Well, yes I am. Trying to solve these performance issues is why I've
been asked to come in. Why do you say that ?'
'If Barry gets to hear about this, our call targets will probably be
doubled !'.
He smiled and nodded at the electronic rolling ticker display
detailing how many calls have been handled, how many are waiting,
average call duration etc.