Continue in 2 seconds

Erie Insurance Dramatically Improves Query Performance Using SyncSort

Published
  • January 01 2005, 1:00am EST

REVIEWER: Nathan Hunsaker, associate life actuary for Erie Insurance.

BACKGROUND: Founded in 1925 as a Pennsylvania auto insurer, The Erie Insurance Group has grown to become a multiline insurance company, offering auto, home, commercial and life insurance through a network of independent insurance agents. The organization adheres to disciplined underwriting, fair pricing and a prudent investment philosophy. These factors have contributed to Erie's ability to earn consistently superior ratings in the insurance industry.

PLATFORMS: Dell GX150s with Windows 2000.

PROBLEM SOLVED: I am charged with the task of creating customized applications to process the data needed for the analysis of claims and policy data. Complicating matters is the fact that we recently migrated the system I used from CMS to Windows. I needed a tool that could provide me with the same functionality and performance that I was getting on the mainframe. SyncSort for Windows provided me with that functionality and far exceeded my performance expectations.

PRODUCT FUNCTIONALITY: When information is requested within the life insurance division, the data is FTPd to the local area network where I am able to access it. I tend to create extracts out of reporting files. The applications that I build are always changing, depending on user requirements. For example, I recently had to compare thousands of records and filter the duplicates, keeping only the last duplicate record. I built three SyncSort applications to perform the necessary processing. The first one used the copy function to assign a key to each record. The second SyncSort application sorted the records by the original key in ascending order and by the sequence number in descending order. This step also removed the sequence number, bringing the output back to the original format. The final application was a merge that eliminated the duplicates while keeping the original order of the records.

STRENGTHS: Because SyncSort for Windows was very easy to understand, there was absolutely no problem getting the same applications to exist on the new platform. I am currently building a mortality study system. For the project, I'm collecting an entire history of death benefits and lapses for all contracts at some point in time for 50 years into the future. This consists of several hundred thousand records of various types. Just getting that correct was a challenge because I needed to identify invalid records. Separating and dealing with these records was essentially impossible until SyncSort was implemented. With SyncSort, I was able to add processing and reformatting capabilities. I developed the study from the ground up so that it's all fixed-length records with 50 repeated fields at the end of the file. It is a bit difficult to index, but SyncSort handles it with no difficulties. SyncSort completed the initial processing of 10GB in 15.75 seconds. The project will be run on an annual basis, and the data is expected to grow continually. Prior to integrating SyncSort, the application was taking 45 minutes to sort and process the data. Once SyncSort was implemented, we were able to reduce the elapsed time by 99.8%, cutting it down to just 5 seconds.

WEAKNESSES: I need to hit the sample button every time I change the record number when sampling records. I understand that enhancement requests to the product have been noted and may be available in a future release.

SELECTION CRITERIA: Because I was already familiar with SyncSort CMS, I suggested to the IT department that the company utilize SyncSort for Windows. They came to the same conclusion, and now we're starting to replace all of my mainframe applications.

DELIVERABLES: By using SyncSort continually to create new applications in response to queries, I am able to supply my division with key information as quickly as possible. It is one of the resources that helps Erie Insurance provide the type of service and security that customers have come to expect. Because of this superior service, customers have peace of mind knowing that they are protected.

VENDOR SUPPORT: I've had a favorite vendor for many years. They have always answered my questions, whether it is their problem or not. I just promoted Syncsort to that level. They are exceptionally responsive and always provide a very thorough and useful solution. They either have the answer right away or they research the question and quickly send me examples.

DOCUMENTATION: The documentation was very good and met all our needs.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access