Feeds

Improving on optimisers

'Clever' enhancers up performance

High performance access to file storage

Comment After all these years you would assume, wouldn't you, that database optimisers were pretty good? Companies like IBM and Oracle have been costing query plans and re-writing SQL not just for years but for decades, so you would expect they would know what they were doing.

Well, if that’s the case, how come ActiveBase and its UK distributor Application Performance, can making a living out of selling the former’s ActiveKnowledge product, which has been expressly designed to improve optimiser performance though, to be fair to IBM, only for Oracle?

ActiveKnowledge acts like what you might describe as a SQL Firewall. That is, it intercepts all incoming SQL, does various clever things with it, and then passes it on to the database to process. What, of course, is important is the "clever things". So, what are these?

The main "clever thing" that ActiveKnowledge does is to assume the optimiser doesn’t know how to calculate costs accurately. The company’s view is that costs can only ever be estimated and that those estimates will always include inaccuracies which, in turn, will result in queries running more slowly than would otherwise be the case.

ActiveBase contends that the only way you can accurately calculate the costs of a query are to run the query multiple times using multiple options and actually find out in practice which is the best way to run a particular query. This is exactly what the product does: determines multiple execution strategies and then benchmarks them. For obvious reasons, therefore, the product is most suitable for environments where you have many queries that are repeated on a regular basis.

Following on from the determination of the most efficient way to run any particular query you can then derive a rule to correct the execution path, for this query (and any other queries that are similar to it), which is then activated and the improved query is passed to the database whenever this query arises.

Other facilities provided include SQL rewrite, support for hints, blocking rules (for example, blocking Cartesian joins), query delays, and so forth. In addition, Application Performance is developing rules packs for specific application environments such as Chordiant, Siebel and so on. There is also an auditing module that allows you to inspect usage information in a graphical fashion, with details such as the number of reports submitted daily and average query times.

Of course, the real proof of this particular pudding will be to see it improving the performance of your queries, which Application Performance will be happy to demonstrate in a proof of concept. Existing customers have reported a variety of results, ranging from an average performance improvement of 30 per cent to some queries with as much as an 80 per cent improvement. At one Siebel installation, the company was able to bring down elapsed time for one query from over 10 minutes to 30 seconds.

These results highlight another consideration: this is not just for queries you run regularly, it is also for queries that run in a relatively short period. This should be obvious: running benchmarks for queries that take days would typically be too onerous (except on a test system). Nevertheless, improving the performance of all those short queries releases resources for those longer running enquiries so this should produce a spin-off benefit.

In a nutshell: it is certainly worth considering if you are an Oracle user.

Copyright © 2006, IT-Analysis.com

High performance access to file storage

More from The Register

next story
Android engineer: We DIDN'T copy Apple OR follow Samsung's orders
Veep testifies for Samsung during Apple patent trial
Windows 8.1, which you probably haven't upgraded to yet, ALREADY OBSOLETE
Pre-Update versions of new Windows version will no longer support patches
Microsoft lobs pre-release Windows Phone 8.1 at devs who dare
App makers can load it before anyone else, but if they do they're stuck with it
This time it's 'Personal': new Office 365 sub covers just two devices
Redmond also brings Office into Google's back yard
Half of Twitter's 'active users' are SILENT STALKERS
Nearly 50% have NEVER tweeted a word
Windows XP still has 27 per cent market share on its deathbed
Windows 7 making some gains on XP Death Day
Internet-of-stuff startup dumps NoSQL for ... SQL?
NoSQL taste great at first but lacks proper nutrients, says startup cloud whiz
US taxman blows Win XP deadline, must now spend millions on custom support
Gov't IT likened to 'a Model T with a lot of things on top of it'
Batten down the hatches, Ubuntu 14.04 LTS due in TWO DAYS
Admins dab straining server brows in advance of Trusty Tahr's long-term support landing
prev story

Whitepapers

Securing web applications made simple and scalable
In this whitepaper learn how automated security testing can provide a simple and scalable way to protect your web applications.
Five 3D headsets to be won!
We were so impressed by the Durovis Dive headset we’ve asked the company to give some away to Reg readers.
HP ArcSight ESM solution helps Finansbank
Based on their experience using HP ArcSight Enterprise Security Manager for IT security operations, Finansbank moved to HP ArcSight ESM for fraud management.
The benefits of software based PBX
Why you should break free from your proprietary PBX and how to leverage your existing server hardware.
Mobile application security study
Download this report to see the alarming realities regarding the sheer number of applications vulnerable to attack, as well as the most common and easily addressable vulnerability errors.