Tesmond's Useful Design

Well made a new interesting discovery today. Combining TOP with ORDER BY can severely slow down a query. I was looking into a slow query which was taking around 10 seconds to return 69 results. It was getting the "TOP 100" from a specific date range and then ordering by ID descending. It was fascinating to me to discover that removing the TOP 100 returned the results in 0.005 seconds. Removing the ORDER BY instead of the TOP 100 also resulted in again producing the results in 0.005 seconds. So the moral of the story is don't use TOP and ORDER BY if you want your results back quickly. I have no idea why this kills performance right now, but it is perhaps even more interesting that even though the query TOP 100 ORDER BY ID ASC results in the same set of results as TOP 100 without an ORDER BY command, it takes 8.8 seconds rather than 0.005 seconds. There are definitely some huge optimisations available in respect to these queries. Update Well after reporting this t...

I release my initial post on building excellent software may have sounded a bit anti-UX. The UX "movement" is all about providing an excellent, unique, crafted user experience that rewards the user and delights them with your product. I perhaps implied that "features" don't matter. Yet little rarely used features are often the ones that delight us the most. I recently owned a Samsung Galaxy Note. With the stock Samsung firmware it was extremely feature rich, there was lots of nice stuff like sending the screen easily to my TV, some nice gesture support, eye tracking screen time-outs. However, I chose to install a custom firmware that was extremely stripped down by comparison. This was purely because the standard firmware was too slow for my liking, I felt I was always waiting. Of course I missed some of the features but the large increase in responsiveness in 99% of the actions I would perform on the device more than made up for the missing functionality. I ha...

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 ...

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. ...

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...