Intersystems Cache Indexing Shocker - How to Fix Sluggish Queries
|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 including the field you are performing your where clause on you activate a full table scan
Uses indexSELECT ID FROM TABLE WHERE STATUS != 'Complete'
SELECT count(*) FROM TABLE WHERE STATUS != 'Complete'
SELECT * FROM TABLE WHERE STATUS = 'Complete'
Full table scanSELECT STATUS FROM TABLE WHERE STATUS != 'Complete'
SELECT * FROM TABLE WHERE STATUS != 'Complete'
SELECT * FROM TABLE WHERE STATUS = 'Active' OR STATUS = 'Error' OR STATUS = 'Cancelled'
As far as I am concerned this is unbelievable behaviour, which is still in the product as of the latest 2014 version of Intersystems Cache database!
At least it had revealed a 3 different ways of substantially improving query performance
1. Union QueryIf you know all of the data that can be contained in the field then you can perform a UNION query
SELECT * FROM TABLE WHERE STATUS = 'Active'
SELECT * FROM TABLE WHERE STATUS = 'Error'
SELECT * FROM TABLE WHERE STATUS = 'Cancelled'
This of course means more maintenance when adding a new status.
2. ID Only SelectIf you do not know the data then you can just return the ID and loop through the returned IDs to grab the data.
SELECT ID FROM TABLE WHERE STATUS != 'Complete'
SELECT * FROM TABLE WHERE ID=...
3. Create New Propertye.g.
SELECT * FROM TABLE WHERE Active = '1'
This requires a new property which is probably easiest to create in the OnUpdate method, again maintenance of this would be an issue.
I have not seen any documentation talking about these issues with queries. It does strike me this should be an easy thing for Intersystems to fix at the database level, but if they haven't considered it by now then I won't be holding my breath for it.