Posts from 2019

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.

We all love pub quizzes and like TV quiz shows. I think its partly because we are curious to see whether we can answer the questions and sometimes, to occasionally, laugh at some of the bizarre answers offered by contestants under time pressure.

One of my favourites recently was a lady who was asked:-

'Hydrocarbons are made up of carbon and which other element ?'

'Carbon dioxide'

I loved this answer because she replied, in part, with 'Carbon' which would have meant that hydrocarbons comprised of carbon, and well, more carbon. Then she added two atoms of oxygen for good measure.

Tipping Point is a very simple game; it's essentially the Penny Falls we all loved as children when we visited the amusement arcades at the seaside.

However, to tart it up a bit for TV, Tipping Point has introduced a load of silly phrases and words to make the game seem more exciting:-

• 'broad push' - when multiple counters fall and get pushed on the next level. These, in turn, get moved forward en masse to push more counters forward than a single counter could

• 'ghost drop' - when a counter takes a long time to fall, sometimes by lodging on a peg, so it takes longer to fall to the bottom.

• 'rider' - when a counter doesn't lay flat but lies over another counter, reducing its effectiveness.

This is probably the most common and irritating. We get contestants and the host, Ben Shephard, constantly urging 'Don't ride, don't ride, DON'T YOU DARE RIDE !' or 'OH NO ! DAVID ! I CAN'T BELIEVE IT - IT'S A RIDER'

• 'flat' - when a counter falls and just lies, err, flat so it's optimally positioned to be pushed onto other counters in an effort to move them too. 'JUST SETTLE FLAT. GO FLAT !'

• 'light up drop zone 3 please' - the contestant has a choice of four separate columns in which to place the counter. These are known as 'drop-zones' but it's incredibly irritating to constantly hear 'drop zone 3 please Ben' followed by 'drop zone 3 - light her up please'.

• 'ambient drop' - a rogue counter that falls, and may potentially dislodge more counters, but sadly when no contestant is active. Any fallen counters are declared null and void. Ben loves these rare events and almost screams orgasmically as he reiterates the rules to the disappointed contestant.

• 'rapid drop' - a counter that falls through the drop zone without hitting any pegs that disrupt or change its direction resulting in a fast, vertical drop.

• 'boomerang drop' - a counter that is dropped from one drop zone but miraculously is diverted (obeying the laws of physics) and ends its journey in an adjacent drop zone.

• 'lateral movement' - another common and incredibly irritating phrase. Used when the counter is simply going to do nothing of note other then fill an empty void. Ben immediately tries to dispel everyone's disappointment and boredom by fuelling a forlorn hope for some sideways motion and shrieking 'Janet, don't worry. We might get some lateral here', 'If we could just get some lateral, maybe the silver one will drop' or 'We could use some lateral here'.

Another element I find slightly irritating is the fake, forced bonhomie and camaraderie on the show. You are a contestant, competing for money against three other contestants, you don't know, on a TV game show. Please don't pretend you're all best friends and like each other by congratulating or otherwise commenting or providing lengthy, detailed Tipping Point analysis during other players' turns.

'Oh - marvellous drop, Peter. That's set you up very nicely indeed.'

'Oh - she's not being very kind to us today' (attempting to humanise the machine)

'Oh yes - well done Yvonne. You've capitalised on all my hard work there and won £450 but I don't care. Honest.'

'Oh - hard luck Brian. I can't believe that black one on the edge didn't fall.'

The best counter-example was a wonderful young man with a wispy, straggly beard (probably a student) who simply answered all his questions, frowned and silently cursed when he got a question wrong and barely reacted or smiled after a successful answer. He also grimaced, sighed inwardly and silently cursed when other contestants did well. He certainly didn't comment, praise, empathise with or interact with the other players. On the contrary, you could almost see him wince as other players luckily dislodged multiple counters to increase their total.

So that's why I hate 'Tipping Point'. Plus I have applied 79 times to go on the show and not been accepted.