An interesting case
Tuning data warehouse queries is not always easy; when you take into account complications such as parallel query, bitmap indexes, star transforms, and query rewrite then there is a lot going on to confuse the picture. This is further complicated by the sort of SQL generated by query tools, especially those of the drag and drop kind.
My current pet hate is people who drop concatenated columns into the where clause of a query and eradicate the chances of an index being used. Recently I had a query that completed in around 30 seconds if they just used the index key but took over an hour when they concatenated the index column to the description column.
Today, I was looking at a similar problem for a grocery supermarket. They had a concatenated-column where clause on store and region. But the interesting thing was a CASE statement in the where clause.
Supermarkets often divide products into departments, for example meat, fruit & vegetables, diary, tobacco, and alcohol. In this case the report was looking at non-tobacco sales across two regions by store, item, and basket where the spend was above a certain threshold. On the face of it this would look like a simple (and assuming tobacco was department 6)
AND DEPARTMENT_NO <> 6somewhere in the where clause. But in this case they had a more complicated construction:
AND CASE WHEN DEPARTMENT_NO = 6 THEN ‘A’ ELSE ‘B’ END = ‘B’Explaining the plan for the “not equals” variant I had total cost of around 29000 but the “case” variant only had a cost 1500, Quite some difference! The main difference in the plan was that the case version managed to use a single transform operation to navigate from product to department whereas the other query had to hash join several tables to effect the same navigation.
Of course, this use of a case statement to replace a not-equals operation may not work in every case but it may be worth a look when you test various query plans.