| Index Search Add FAQ Ask Question |
|---|
Index > Other 3rd Party Tools > Software Tools > DBAConnect
LECCO SQL Expert by LECCOTECH, Inc.Topics24 July 2001Author: Andrew Deighton |
Rating
|
Summary
LECCO SQL Expert primarily provides an environment for optimising your SQL code.
Although the new release 3.0.7 provides a fuller development environment than
previous versions, the major strength of the product is in its optimisation
engine, which quickly and accurately generates a large number of alternate
ways of writing the same query, and provides the estimated performance of
each. The performance estimates of each statement help the user select which
statements to test using the batch-testing feature that allows benchmark testing
of each.
Supported Operating Systems and VersionsLECCO SQL Expert has fairly modest client requirements, supporting Microsoft Windows9x, Windows NT4 and Windows 2000.At the backend, LECCO SQL Expert supports all the current Oracle releases, from Oracle7 upwards. (I have done some preliminary testing against Oracle9i, and have not encountered any problems with this.) LECCO SQL Expert is also available for MS SQL Server, Sybase, and a DB2-UDBversion is promised in Q3 of 2001. Although the interface and functionality for these versions is similar to the Oracle version, they are separate products, with obviously different internals (to cater for the different RDBMS) and are separately licensed.
InstallationThere are two editions available, the Developer edition (includes SQL Coder/Debugger, SQL Optimiser, DB Explorer) and the Professional edition (as Developer edition but adds SQL Scanner and SQL Inspector modules too). Installation of this product went very easily for both the downloaded version and the version supplied on the CD. There was no problem with the product integrating into the previously installed Oracle client software or networking products. The uninstall of the evaluation copy downloaded from the internet also went without any problem.The only issue I had was when my trial license expired and needed to be replaced. I could not find an option to re-register the product, so simply re-installed it. This process is not difficult, but being able to change the license code would be useful at the end of a trial period; especially if there was a large rollout of the product.
What it doesLECCO SQL Expert optimises SQL Code, and does this very well. It goes further than simply inserting hints into the code already written, and rewrites the code, producing semantically equivalent SQL. For example, the query:Select some_data From details_table D Where exists (Select 1 From lookup_table L Where L.lookup_column = D.lookup_column And L.matching_column = 'Yes')Can be written as Select /*+ FIRST_ROWS */ some_dataFrom details_table D Where lookup_column In (Select L.lookup_column From lookup_table L Where L.matching_column = 'Yes')Here, LECCO SQL Expert has been able to replace an "exists" clause with an "in" clause, to produce a better SQL statement. It has also used hints to examine their effect on the statement execution. LECCO SQL Expert also provides a development environment in which to develop and test SQL and PL/SQL code, along with a set of integrated tools to accomplish such tasks as extracting DDL statements from the data dictionary and scanning source code files for embedded SQL statements.
What problems does it solve?LECCO SQL Expert solves the problem of a missing development environment from the Oracle toolset. Oracle has a solid database with many new features added over the years, but has not yet produced a development environment for users wanting to develop in anything other than their own Forms product family. SQL*Plus is the standard tool with which to communicate with the database, but it falls short of providing an integrated development environment such as modern developers have become accustomed to.The other major problem that LECCO SQL Expert addresses is the very real problem of poor performing SQL code. Many developers of SQL code have a background in other languages (VB, C/C++, etc.). The thought processes involved in SQL coding are very different from that used in procedural or object oriented coding. For this reason, good application developers are seldom good SQL coders. This tool provides a means for the SQL code produced to be optimised as if an expert wrote it.
Who should use it?LECCO SQL Expert is not a tool for the beginner. The user of this tool should have at least some idea of what tuning is and how it impacts on the final product. Although the 3.0.5 release of LECCO SQL Expert includes more functionality than previous versions, some of these require at knowledge of SQL to be useful.For an experienced SQL programmer, the ability to develop and tune SQL and PL/SQL code within an integrated environment is extremely valuable, and the resultant savings in development and execution time will pay for the product time and again.
Competitive products
Detailed reviewWhen I started evaluating LECCO SQL Expert for this review, I was very sceptical about any product that claimed to optimise SQL code. LECCOTECH claim that on average 65% of client code they examine can be optimised with average performance gains of 400%+. I have come across far too many products that generate or have been written using very bad and un-optimised SQL, and was beginning to believe that optimised SQL just could not be generated. So, when I installed LECCO SQL Expert, I got out my reference book on Oracle performance tuning, and started paging through to find test cases. Although LECCO SQL Expert did produce some weird and wonderful looking SQL at times, I have not found a single case where I could improve on the execution plan of the suggested code or improve the response/elapsed time that LECCO SQL Expert delivered! (even with the help of the reference manual.) LECCO SQL Expert has definitely proved my sceptical views of SQL optimisation wrong.One very useful feature of LECCO SQL Expert is the amount of information that can be retrieved from the execution plan window. Most development tools will give a graphical representation of the execution plan, few though, give the user access to a description of what each step is doing, linked directly to a database browser that will allow the user to easily access information such as table definitions, index definitions on the tables, and even the data in the table itself. Most tools would require a separate database browser window for this functionality. Earlier releases of LECCO SQL Expert that I had encountered have concentrated purely on the optimising component with very little other functionality. This new release has taken a step towards providing a fuller development environment. The product now offers editors for SQL and PL/SQL as well as an intuitive interface into the Oracle debugger, allowing for easy debugging of PL/SQL code. The PL/SQL editor also allows for the execution of DDL statements, providing a means of creating and modifying objects. It is essential to be able to carry out these tasks in order to provide a complete development environment. This is still too syntax based for a modern development tool, and the product would benefit from having fill-in forms for the DDL components. The PL/SQL coder window has another very useful feature - that of batch explain plan generation. From this window it is possible to generate an execution plan for each individual SQL statement within a PL/SQL block, thereby allowing quick detection and optimisation of the bottlenecks within the code. Another strength of LECCO SQL Expert is its ability to extract individual SQL statements from program source code files as well as PL/SQL stored code within the database. These SQL statements can then be analysed and tuned in a batch mode, removing the usual requirement to process individual statements manually. This is particularly useful for old programs that have been written before implementing LECCO SQL Expert. Poorly performing SQL code can be detected very easily and optimised without the manual task of extracting the SQL from the programs. Some of the smaller features that make life easier when developing database code are the automatic highlighting of reserved words and the SQL Formatter which can make your code much easier to read. Not only does using a standard format for your SQL code make reading and maintaining code easier, but it also helps to reduce parse time by not allowing the same SQL statement to have multiple textural versions. LECCO SQL Expert is also very customisable, allowing the user to change the various thresholds for reporting purposes. This allows the product to be equally as useful in small developments as in the largest of data warehousing applications. This scalability will make the product a great choice for any organisation that is developing software with an Oracle backend. Although the product has not been certified against Oracle9i yet, I have run it successfully against a database created with Oracle9i, and have experienced no problems. The performance tuning within LECCO SQL Expert seems to function equally as well in both the releases I have tested it against.
SupportThe support provided for LECCO SQL Expert (in the UK) is via e-mail requests. This has proved very useful and efficient, with requests for information being resolved on the day they were made. Worldwide support is provided through LECCOTECH's corporate offices located in the United States, United Kingdom, Canada and Hong Kong.
An irritating feature of the product is that it does not remember that the user
has re-sized any windows. The default size that it opens windows at is fairly
small, and so they need so they need to be resized each time they are opened.
Cost and where to buy
Download a 10-day free trial of LECCO SQL Expert from
here.
LECCO SQL Expert is available in different license pack sizes. The minimum pack
size for the Developer edition is a 3-pack, at a cost of around £1,250 per seat.
The Professional edition is available as a single license basis at £3,285 with
discounts for other pack sizes. Maintenance and support are an additional 25%
per annum. For more information, contact LECCOTECH at:
+1 (415) 901 8228 in the USA, +44 (024)7669 5900 in the UK, +1 (905)415 3665 in Canada and (852) 2831 1000 in the Asia Pacific region.
Phaldor IT Solutions is an independent database consultancy that provides
database expertiese to UK based clients. For more information on the services
available from Phaldor IT Solutions, please visit their web site at
http://www.phaldor-it.co.uk.
Can you write reviews and articles like this one? Why not become an editor or reviewer for the Oracle FAQ? Find out how you can make money, get noticed and advance your career click here 
|
 |
 |
