Tesmond's Useful Design

While my investigations have still left me sad to find that there is no real way to match the read performance of an SQL database I have found out some interesting little pieces of information on Caché. 1. Bitmap indexes always appear to be faster than standard indexes no matter how many unique values there are. 2. Traversing standard indexes manually is the fastest way to return a query first time (second time bitmap index SQL is the fastest). 3. Single Insert performance is good 4. Reversing through an on disk array (e.g. $ORDER(^global,-1) ) is slower than going through it forwards putting the data into an in memory array and then reserving through it! To my surprise all my tests showed that it was at least a 50% performance improvement and as the data grows it can double. The only problem is if the memory array grows too big then you will get a store error as the memory runs out. I am considering re-writing my queries to only reverse $ORDER in memory and checking the $STO

Time For Write Performance to Shine? Having struggled to achieve good read query performance I thought I would give the write performance a try. Caché has suggested that its write performance is its real strength and so I thought I would have a quick comparison. I used the same simple table with 10,000,000 records. It only has a first and lastname value, I inserted 10,000 rows 3 times in each and took the average performance figure. A Win for  Caché? I tried both SQL INSERT and creating objects using %Save() to see which was faster. I found SQL INSERT to be about 15% faster than using objects, so the figures below are from SQL INSERTS. Caché 10,000 inserts 0.42 seconds, this went down to 0.38 on subsequent tests MySQL 10,000 inserts 0.152 seconds.  MSSQL 10,000 inserts 0.502 seconds. Finally  Caché  performing well. What is an additional bonus is that  Caché  has good performance with sequential inserts which are significantly slower in MySQL. Overall the write performanc

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 alwa