Tesmond's Useful Design

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