I admit I am no Intersystems Caché guru, I am looking at 3 years working with the software. I have attended the sparse available training and I have been left to explore this weird universe known as Caché.
Now if you read their website you will read the tag line "the World's fastest, most scalable Object oriented database". More over they make even greater claims of "...it can run SQL faster than relational databases. "
Every training session seemed to pad every sentence with "so fast" and "world's fastest" although I was never sure if they were trying to convince me or themselves of this "fact".
When using SQL databases for reporting data I have never really encountered issues, my SQL has normally been suboptimal fire and forget code, I make the query get the data and if the full page response including the query is less than 1 second it is not worth optimising.
Now while 99% of my MSSQL and MySQL queries have always been so quick I have never needed to revisit them with Intersystems I have had to go back again and again to optimise everything, completely re-writing a ton of code, turning a single line of SQL in to an intricate For loop absolutely minimising memory use. Yet the makers continue to bang on about performance, but to my knowledge only write performance is reasonable, read performance is SLOW.
I thought it would only be fair to benchmark the process to see if it was possible to match the performance of two of the most common SQL databases.
The following query is based on a "real world" query and one of the first that I experienced slow Cache performance. I wrote a summary page which contained several aggregate queries, and to my surprise it was very sluggish. The page in question ran between 10-30 aggregate queries, and the tables containing the data ranged from 100-100,000 rows.
SELECT LastName, count(*) AS Total
ORDER BY DESC Total
I consider that a reasonably simple, but a reasonable example of a commonly run query.
I chose this number as it should help highlight real performance differences and helps establish if it truly was more scalable.
MSSQL 0.061 seconds
MySQL first query in 10.4 seconds and subsequent queries in 0.003 seconds.
Caché *as SQL(/w bitmap indexes) first query in 62.83 seconds and subsequent queries in 33.27 seconds.
Caché *as $Order through the index global 48.17 seconds first query and subsequent queries 48.17 seconds
MSSQL unindexed 4.4 seconds
I hope you will agree the performance figures are WAY off SQL performance.
Next test to line up is write performance, as Cache does not need to be "ACID" I suspect it has a chance at winning, but nothing is guaranteed...