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

In previous blog posts I have mentioned that Cache has good write performance, in fact in certain circumstances it is faster than many other databases. However, it is rarely the case that optimising performance is not desirable. Every one loves a performance boost, it is the most universally loved product change. The closest I have ever experienced to a complaint about a performance boost is "its so fast I didn't believe it had worked". Any way I was working on improving query performance on a server when I noticed some intermittent slow down. I traced the performance issue to a task which performs a huge burst of write operations. The write operations were so heavy that the rest of the server was experiencing slow down. Obviously the code is under review to see if the amount of data being written is necessary, but I was curious about optimising the write operation itself. With read operations I have noticed that dynamic SQL is slower than compiled SQL which is i...

When I first started my current job I was told that the database was an Intersystems database, I had not heard of this before on researching it I found out a number of things. 1. It was ranked number 63 on DB-Engines most popular databases 2. Cache is not a good name for a piece of software if you want to find information in Google 3. There was a distinct lack of examples and people discussing real code This 3rd issue is perhaps the most important. In programming most of what you want to do has been done by thousands of people before you. Want to parse a CSV file, well if your language does not have a built in function someone will have posted a solution on the internet. Not only will you have a working solution it will probably have been commented on by a number of programmers and improved to a level which would have probably taken you several live iterations to achieve. The code examples in the documentation are basic, out of context and ignore even simple advise on field fil...

Photo: Markus Spiske / www.temporausch.com / CC-BY When going through a performance review of a grid on the website, I was struggling to see why the query was taking so long. It was a single table query with a single where clause which was on an indexed field. This indexed field could only contain 5 different values and the query was return fewer than 100 entries but was taking over 1 second. On other lower powered servers the query was taking less than a third of a second and often returning more data albeit on a smaller total data set. Checking the query plan revealed something interesting, the query was not using the index but was instead performing a whole table scan. Further testing revealed that only "=" queries use indexes. "!=" ignore indexes as do any queries with "OR" or "IN". After further investigation I found that if you only return the ID then NOT, OR , IN queries do use indexes, however, if you return any other field inc...

As I alluded to in my previous post there are many reasons to pick a database, performance is only one of them, but as it is the easiest to objectively assess that it where I am starting. So here is a quick look at query performance. So we have our 1,010,000 strong pair of databases. So how about a count of the number of editions which have a 1 in them (about 47000). MongoDB - 0.906 seconds Intersystems Cache - 2.247 seconds Well MongoDB takes a healthy lead in this respect. It should be said that by default MongoDB has its own query language, where as Intersystems Cache can be accessed through iteration or via SQL queries. Now I could have biased the query strategy, for example Intersystems Cache is significantly faster at selecting whole phrases, e.g. searching a specific property which appears multiple times can be returned faster that MongoDB, but in both cases I was looking at 0.010 seconds VS 0.007 seconds. It was consistently faster although it was in the magnitude of...

While looking through stackoverflow I noticed an interesting question about the differences between Intersystems Cache and MongoDB. Now these databases are substantially different in many respects, but both do allow for a more direct object mapping approach than you will normally experience in a standard SQL environment. Having read that MongoDB had recently secured $150M in financing I was curious as to what the platform was like. Picking a database is never just about raw performance, but it is one of the most important aspects so I thought I would start with this aspect. What I thought would be interesting would be to compare the insert performance of objects in Intersystems Cache VS the insert performance in MongoDB. I took a very simple example, inserting 10,000 objects each with 3 properties, performed both tests 3 times on my laptop and averaged the results. There were no indexes defined. This is not exactly a great test but as Cache was significantly faster at doing ...