Continue in 2 seconds

Johns Hopkins Improves SQL Performance with TunaSQL

Published
  • September 01 1998, 1:00am EDT

PLATFORMS: Johns Hopkins Development and Alumni Relations Department currently runs Oracle 7.3.2 on a DEC Alpha server running Open VMS. They are using TunaSQL from DBE Software, Inc., for Oracle on Windows 95 clients.

BACKGROUND: Founded in 1876, the Johns Hopkins University enrolls more than 16,000 full and part-time students on three major campuses in the Baltimore-Washington area and in China and Italy. Johns Hopkins University is in the process of implementing an Oracle database system for its Development and Alumni Relations Department. The Development Information Systems (DIS) office supports development staff throughout both the university and the Johns Hopkins Health System. Although TunaSQL is only used in the DIS office, its tuning statements address application software supporting the university's development and alumni relations departments.

PROBLEM SOLVED: In order to locate poorly performing SQL statements, Johns Hopkins University's DIS office uses TunaSQL. Prior to using TunaSQL, DIS used more conventional and manual techniques to tune SQL statements during the development cycle. TunaSQL provided DIS database developers with a valuable tool to help them easily identify offensive SQL statements. Now the developers can quickly check the efficiency of their SQL statements before migrating their code to production. Since implementing TunaSQL into the development process over the past two months, the university has successfully identified and tuned a number of problem queries in the development cycle.

PRODUCT FUNCTIONALITY: TunaSQL functions well as a tool DIS developers use to perform impact analysis before code goes into production. It allows the comparison between multiple execution plans and identifies the best way to write the query based on a function of statistics which are user definable. In addition, developers can pick any combination of execution statistics to graphically compare all alternative SQL versions in more detail. TunaSQL displays detailed or common statistics of the different plans thus helping to evaluate the best alternative for the SQL execution. In the future, they will use TunaSQL to check SQL statements already in production to see if any performance improvements can be made.

STRENGTHS: TunaSQL's explorer-like window allows DIS developers to easily track and visualize the impact of similar SQL statements. This type of interface is well known to Windows 95 users and, therefore, minimizes the learning curve to use TunaSQL.

WEAKNESSES: TunaSQL's on-line help is sparse and needs to be expanded. Also, TunaSQL's documentation would be more helpful if it were more task oriented. DBE Software is currently developing an improved documentation version for TunaSQL to address this need.

SELECTION CRITERIA: TunaSQL was selected because DIS needed a tuning tool to use before SQL statements went into the production environment. A major advantage it offered was its ability to manually alter SQL statements. We also conferred with several experienced industry consultants who highly recommended TunaSQL for the job.

DELIVERABLES: Johns Hopkins DIS is a relatively new user of TunaSQL and, therefore, does not use the product to its full capabilities. Users tune the SQL statements using the basic version of the SQL produced by TunaSQL. DIS is using TunaSQL to analyze SQL statements in the development environment. TunaSQL allows application developers to automatically find alternative SQL statements to improve performance of application code. With TunaSQL's graphs, wizards and statistics, TunaSQL helps developers of all levels to tune poorly performing SQL statements. In the near future, DIS plans to use more advanced features of TunaSQL such as extensive use of hints and "Ask Tuna" expert system to benefit from an automatic rewrite of the SQL statements.

VENDOR SUPPORT: Support from DBE Software has been excellent. The best part of working with DBE Software is the immediate response we receive when it comes to any questions or problems. They have answered our inquiries right away, and we know we can call them at any time.

DOCUMENTATION: The documentation is complete but not easy to understand; however, the company was quick to respond to any questions we posed.

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