It doesn’t matter what tool you use, it’s how you use it
A market risk historical database runs on Oracle 9i.
Data is stored in daily partitions, 50 million rows/day.
Users will query data across 100 days, therefore reading from 100 partitions.
Performance is so bad, that it is unusable.
This triggered a new IT initiative to start looking at other solutions, Sybase IQ.
A proof of concept showed that Sybase IQ managed to improve performance by 5 folds.
So, should we migrate the whole system from Oracle to Sybase IQ? This would involve changes in so many areas:
- ETL
- Reporting
- Software licensing
- Hardware
- Developer reeducation - how to code on Sybase IQ, rather than Oracle.
I was asked to review this. I requested a few days to look at the existing system, prior to making the decision to move to Sybase IQ.
Tested a few things:
- recreate table as compressed IOT
- ordered data prior to loading
- add index (yes, the existing table does not have a single index)
Voila, suddenly existing system can perform about 4x faster than Sybase IQ. We can pull 100 days worth of information in under 2 minutes.
Now I have no doubt that a Sybase IQ expert can come in, and make the Sybase IQ implementation even faster, but the question is for whose best interest?
If it is in the interest of the user, you would agree with me that a simple tuning on the existing system does the job, with least aggro for the user community.