Enterprise Software

Consultant straddles technical and political issues to resolve CRM problems

When you're hired to improve the response time in a recently installed CRM system, where's the best place to start? In this case, a DBA, a list of possibilities, and benchmarking led to a solution.

Leon Tribe is a CRM consultant for Deloitte Touche Tohmatsu (DTT) in Australia. He recently completed a contract involving a CRM system and kept notes on the problems he faced and how he resolved them. This is the first in an occasional series in which he will explain his work for the client, the problems that he encountered, and how he worked to resolve them.

The contract: Slow CRM
After project-managing a customer relationship management (CRM) application installation for a financial services client, DTT was asked to help resolve a problem with the CRM response time. Simple tasks, such as adding a contact, were taking more than a minute to process, instead of a few seconds.

As a result, the client’s call center operations staff was unable to enter real-time data as calls were taken. Information was being recorded on paper and entered later.

That approach caused errors and killed both time and cost benefits the CRM system should have afforded. Moreover, the problems were causing resistance to the adoption of the new system from the “engine room” and complaints were beginning to filter up the chain of command.

Since the CRM vendor was slow to respond remotely and wasn’t available to go on-site in the time required, the client needed someone on-site for a week to bed down the system and to reduce political friction within the company. Because DTT had initially put the system in place and had extensive knowledge of the client’s business needs, the client turned to us. The CRM vendor gave us its blessing because our involvement meant the major problems would be resolved, relieving the pressure on the vendor.

My role
I was to be on-site full-time to assist the internal administrators. If I could resolve the major complaints, this would quell the critics and reinforce our relationship with the advocates of the new CRM system. By being on-site, I would demonstrate our ongoing commitment to the CRM project, establish our capability to solve technical issues with the CRM product, and demonstrate our desire to work with the client to allow it to reach its full potential.

Also, we would demonstrate to the vendor that it could turn to us to give its clients technical assistance. Finally, the contract would allow me to identify future work for DTT, establishing us as the strongest consultant.

I am a consultant, after all.

Solving the problem
Here’s what I did to solve the problem with the CRM system’s slow response time.

Tuesday, 10:00 to 11:00 A.M.: Benchmark the database performance
I obtained a copy of the database being used on the network and loaded this on a local PC. By performing the same operation on the local machine and across the network to the server, I could determine whether the network was the source of the problem. While testing showed that operations were slower across the network than on the local PC, both were slower than desired (see Figure A).

Figure A

The results suggested that the problem was either within the CRM software or the local machine. I planned to do benchmarking every day to account for fluctuations caused by factors like network outage and mp3 downloading.

Tuesday, 2:30 to 4:30 P.M.: Meet with the Oracle DBA
The client had been investigating the performance issue before involving me and had enlisted an Oracle DBA for help. The DBA had done exhaustive tests on the communication between the CRM program and the Oracle database it was using and had come up with a number of possible solutions to try to improve the performance.

I met with him for about an hour to review the solutions he proposed. They ranged from tweaking the settings of the middleware between the CRM product and the database to tweaking the Oracle settings (increasing the buffer cache size or temp segment). My plan was to go through his suggestions and see which one, if any, affected response time.

Wednesday, 10:00 to 11:00 A.M.: Benchmark the database
Today’s benchmarking produced numbers that were similar to those from the day before. This told me that my readings were a true reflection of the system performance and that the response-time problem had to be solved if the business processes were to be run efficiently.

Wednesday, 12:30 to 1:30 P.M.: Benchmark the database
To account for fluctuations during the day, I did the readings a second time, again with similar results. The purpose of doing the readings at a different time was to see if the delay was caused by some kind of performance spike on the machine or some sort of network traffic. The results told me that the CRM product was behaving consistently slowly and the problem was inherent to the system.

Wednesday, 1:30 to 2:30 P.M.: Begin testing the Oracle DBA’s suggestions
I began trying the suggestions given by the Oracle DBA, benchmarking performance as I went. No smoking guns yet.

Thursday, 11:45 A.M. to 1:45 P.M.: Compile a list of suggestions
I compiled a definitive list of suggestions from both the Oracle DBA and the CRM vendor in an Excel spreadsheet. Creating the list made it easier to manage what had been tested and allowed for note taking. It also meant that all my thoughts were in one location.

One recommendation was to increase the RAM on the local machines to 256 MB. Because the CRM product didn’t have a thin client, local machine performance was a factor. Also, the machines on-site were old, mainly running Windows 95 and most with 64 MB of RAM or less. Although some machines had been upgraded to 128 MB of RAM, the increased memory didn’t do enough to make the CRM system respond more quickly.

Another suggestion was to improve the database server by installing another CPU. A third suggestion, from the Oracle DBA, was to restrict the number of records sent between the CRM program and the database for a given SQL query. This seemed the most promising.

Thursday, 3:00 to 3:30 P.M.: Breakthrough
The Oracle DBA's suggestion worked. The response time was drastically shortened when I changed the local settings of the middleware and restricted the SQL traffic between the CRM program and the Oracle database. By restricting the size of responses from the Oracle database, the system was improved in two ways:
  1. The amount of traffic across the network was significantly reduced.
  2. When the information arrived at the call center operator's computer, the computer didn’t need to process as intensely.

Although a simple operation such as adding a contact in the CRM program shouldn’t lead to a large amount of traffic, the program was refreshing the client by having the entire dataset sent to the desktop for processing. The combination of old desktops and a large database had bogged down the response time and been my client's undoing.

Thursday, 4:00 to 4:30 P.M.: Follow up with Oracle DBA
I talked to the Oracle DBA about what worked and what didn’t. He suggested that table-partitioning the Oracle database could also improve performance. I added this to the list although, politically, to radically change the database in any way so close after implementation would be risky.

Thursday, 4:30 to 5:30 P.M.: Benchmark the database
The performance was measurably improved due to the “SQL throttle” in place. Benchmarking now gives different results (see Figure B).

Figure B

Friday, 9:00 to 9:30 A.M.: Inform client contact
I informed the client point of contact of the solution to the problem. We organized the change to be made to the middleware on all of the call center desktops.

Friday, 3:00 to 4:00 P.M.: Benchmark the database
No surprises. This produced results that were similar to yesterday’s vastly improved response times.

Bottom line
In this case, benchmarking the database wasn’t the smoking gun I was after, but the results of the tests showed the benefit of the solution when it presented itself.

It’s not always obvious where the answer to a problem will come from. By making time for a one-hour meeting to discuss the Oracle DBA's point of view, I received a fresh perspective and ultimately the answer to the problem. The result: Response time decreased from more than a minute to less than five seconds and all parties were satisfied.

As Sir Arthur Conan Doyle said, “When you have eliminated the impossible, whatever remains, however improbable, must be the truth.” It is best to assume nothing when it comes to solving a problem. Approach all problems with an open mind and take all facts and feedback into account.

Editor's Picks