Increasing Availability by Increasing SQL Query Performance
One important facet of delivering database availability is ensuring high performance of database applications. Creating and maintaining high performance databases and applications require that developers and DBA's create and maintain a high performance foundation. Approximately 60% of database performance problems are caused by problematic SQL statements, which consume as much as 70% of the resources in a database server.
Due to SQL's complex nature, there may be many SQL statements that return the same result set, but only a few of those SQL statement variations may be efficient. To ensure optimal SQL performance, a SQL statement needs to be transformed in every possible way to produce every equivalent SQL statement and unique query plan for ASE.
Sybase offers a tool, called Sybase SQL Expert whose SQL Scanning technology allows users to proactively identify problematic SQL statements from database objects, files, and application code without executing the applications. Problematic SQL statements can be rewritten with the Syntactical SQL Optimizer that uses AI-based algorithms to transform a SQL statement in every possible SQL alternative. These alternatives are guaranteed to be semantically equivalent to the original SQL statement and have different query plans in ASE. After the SQL transformation process, the original and alternative SQL statements are benchmarked to identify the most efficient SQL for a particular database environment. The benchmarking process reports elapsed/response time, and run time statistics from ASE.
Sybase SQL Expert incorporates a SQL Comparer that allows users to increase their knowledge in SQL coding by comparing the changes between the SQL alternatives and query plans. Sybase SQL Expert also exploits the benefits of ASE 12 Abstract Plans that allow users to force ASE to use a specific query plan. Sybase SQL Expert optimizes a SQL statement using Abstract Plans, offering a source-less performance optimization solution that allows users to improve the performance on any SQL statement, from any application, without source code modifications.