Benchmarks
A top Oracle consultant built an Oracle database from our demonstration of 987 million Trades and Quotes (TAQ) records from the New York Stock Exchange. The Oracle database was heavily optimized, and ran on a high-end server; Xpace was run on a notebook.
The bottom line: Xpace is several times to several thousand times as fast as Oracle on these tests, and takes a tiny fraction of the disk space. Oracle performance is extremely dependent on choices made in initial setup; Xpace demands no such choices.
This means that Xpace provides results much more quickly than Oracle—or provides the same results using a small fraction of the resources Oracle requires.
Results
Details
The dataset was already denormalized, removing a significant performance advantage for Xpace. (Xpace does not require denormalized data for maximum performance.) The Oracle database is 20 gigabytes, compared to 4.3 gigabytes for Aeolus™. To optimize Oracle searches, both Bitmap (for exact match) and B-tree indexes (for range search) were built, with hash key partitioning. The indexes were locally partitioned, and tables and indexes were compressed. To minimize overhead, there was no logging or flashback. The computer was a 3.2GHz dual-core Pentium, 4 GB RAM, four disk drives as RAID-0 (striped), running Oracle 10g Release 2.
Xpace was run on a 1.8 GHz Centrino notebook with one gigabyte of memory and one hard drive. The only indexer setup was whether a column is text, numeric, a date, or a time.
Oracle built bitmap indexes in just over two hours, and 11 gigabytes; the B-trees consumed over 20 hours and 220 gigabytes. Xpace's 4.7 gigabytes of indexes build in about half an hour.
On each platform, we did eight queries, chosen by the Oracle consultant. The first two (Q1 and Q2) are exact matches, suitable for Oracle's bitmaps, so they mostly test data retrieval time. The next four (Q3 - Q6) are range searches—these test both search performance and retrieval of a large amount of data. Finally, we searched without retrieving any data—this gives a measure of the crispness and interactivity of the search engine, one of Xpace's strong suits.
Below are descriptions and timings (in seconds) of the queries used; following, is the actual SQL for Oracle. Xpace was restarted between queries to remove any caching effects; since Oracle takes so long to start, each query was run during a single Oracle session.
Besides Xpace's speed, these tests show the sensitivity of Oracle to up—front choices in how indexes are built; Oracle's performance varied by a factor of over a hundred on one test (Q7). Xpace's indexes automatically structure themselves according to the distribution of index terms, so they don't need to be defined up front– there are no Bitmap vs. B-tree vs. both decisions to make.
Fine Print
Like any benchmarks, these have their limitations. This database has relatively small rows (only ten columns), and is of moderate size (about a billion rows). Some of Xpace's advantage has been removed, since there is only one table. However, we believe this test provides accurate measurements of Xpace's raw search (especially) and retrieval performance, and show that we are faster—in some cases, much faster– than Oracle.
Q1
select *
from (
select symbol, sum(offersize), sum(bidsize),
round(avg(offersize)), round(avg(bidsize)),
rank () over (order by sum(offersize)+sum(bidsize) desc) as rank
from quotes
where tradedate='01-JUN-00' and offersize>0 and bidsize>0
group by symbol)
where rank <=5
Q2
select *
from (
select symbol, sum(offersize), sum(bidsize),
round(avg(offersize)), round(avg(bidsize)),
rank () over (order by sum(offersize)+sum(bidsize) desc) as rank
from quotes
where tradedate between'01-JUN-00' and '30-JUN-00' and offersize>0 and bidsize>0
group by symbol)where rank <=5
Q3
select *
from (
select exchange, symbol, sum(offersize), sum(bidsize),
round(avg(offersize)), round(avg(bidsize)),
rank () over (partition by exchange order by sum(offersize)+sum(bidsize) desc) as rank
from quotes
where tradedate='01-JUN-00' and offersize>0 and bidsize>0
group by exchange, symbol
)where rank = 1
Q4
select *
from
select exchange, symbol, sum(offersize), sum(bidsize),
round(avg(offersize)), round(avg(bidsize)),
rank () over (partition by exchange order by sum(offersize)+sum(bidsize) desc) as rank
from quotes
where tradedate between '01-JUN-00' and '30-JUN-00' and offersize>0 and bidsize>0
group by exchange, symbol)where rank = 1
Q5
select symbol, avg(bidsize), avg(offersize)
from quotes
where symbol = 'C' and tradedate between '01-MAR-00' and '30-JUN-00'
group by symbol
Q6
select symbol, avg(bidsize), avg(offersize)
from quotes
where symbol in ('C','BOA') and tradedate between '01-MAR-00' and '30-JUN-00'
group by symbol
Q7
select count(*)
from quotes
where symbol between 'A' and 'CCC' and tradedate between '01-JUN-00' and '30-JUN-00'
Q8
select count(*)
from quotes
where symbol in ('C','T','AA','KO') and tradedate between '01-JAN-00' and '28-FEB-00'
