Continue in 2 seconds

Fremont Comp Tunes Oracle Apps, Databases Using LECCO SQL Expert

  • July 01 2001, 1:00am EDT

REVIEWER: James H. Lui, senior financial systems analyst for Fremont Compensation Insurance Group.

BACKGROUND: Fremont Comp is one of the industry's leading providers of workers compensation insurance, tailoring coverage to individual companies and trade associations. The company is a subsidiary of Fremont General Corporation, a financial holding company with assets of more than $8.4 billion.

PLATFORMS: We use Oracle11 databases and Oracle Applications financial programs running on IBM hardware and the UNIX operating system. The LECCO SQL Expert product runs on PCs using the Windows NT operating system.

PROBLEM SOLVED: User service requests ­ from independent insurance agents, brokers and internal financial staff ­ as well as rapid growth in database size and content are our primary challenges. Our financial applications must address continual changes and requests for enhancements and customization of SQL-based user reports and database queries. Database volume typically grows by 150 percent per year. All of these factors cause slowdowns and bottlenecks in processing SQL transactions from users. We've resolved these performance issues by tuning our SQL statements with LECCO SQL Expert.

PRODUCT FUNCTIONALITY: LECCO SQL Expert identifies, evaluates and rewrites problematic SQL statements in order to optimize database and application performance. Its unique feedback- searching engine uses artificial intelligence-based technology to rewrite SQL statements in a recursive manner so that every SQL statement is rewritten continually until all possible semantically equivalent alternatives have been generated. This provides more alternative SQL statements than a human expert could generate, and in far less time. In addition, it batch tests the alternatives to find the highest performing SQL statements possible that are guaranteed to return correct results. Our staff now routinely tunes SQL queries during application and database development as well as in daily production operations. We use it to tune a number of different application elements ­ from customer reports to quote forms, Oracle alerts-based code and any of our interface scripts that have been written solely in external SQL and PL/SQL.

STRENGTHS: LECCO SQL Expert gives us the ability to scan our SQL code to find problematic statements. It then uses artificial intelligence techniques to fully automate the rewriting of semantically equivalent alternative SQL that meets the functionality of the statement. It then batch tests the alternatives to find the best performing solution, and we simply paste it back into the application and run it. It saves us the high cost of using outside consultants to resolve these SQL problems while providing very fast turnaround with application and database solutions that resolve performance issues.

WEAKNESSES: There are some features that we might like to see for database administrator (DBA) use. These include gathering of real-time statistics on SQL performance and the ability to do more graphical analysis of SQL impact and overall system performance. The display of data in a more graphical overview would give us a more meaningful assessment of the entire database system's performance. We are told that this functionality has been developed in a new LECCOTECH product, called LECCO DB Expert.

SELECTION CRITERIA: We purchased this product because it truly automated the SQL optimization process. Also, because this is a client-side toolkit, it's completely nonintrusive to the database and application servers. There is no additional load presented to the systems being optimized.

DELIVERABLES: LECCO SQL Expert delivers the optimized SQL statements we seek. It is applicable to every report and query we run on our massive systems. It's easy to use and provides the performance increases that we sought. In terms of growing with our databases and applications, it has allowed us to use the same code that we originally developed three years ago. We have half of the obsolescence that we typically encountered with mainframe code. At the same time, our developers are becoming better SQL programmers because they've learned so much just from using the product.

VENDOR SUPPORT: LECCOTECH support has been excellent. We haven't needed a lot of support because the product is intuitive; but when we have had questions or needed assistance with a particularly difficult tuning issue, they have been very responsive.

DOCUMENTATION: The documentation is complete and easy to understand. In fact, the product is so easy to use that we rarely have used the documentation. You can load the software and begin using it right out of the box.

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