Over the past few days life has been full of little distractions.
Recently we implemented some changes on a customer’s DW to improve query performance. As usual, all changes were fully tested before implementation on the production system, but because of the nature of the changes and the fact that it is difficult to adequately emulate user workload we also adopted a period of intensive review or running queries and their performance. Well, our changes have been justified, most user queries run faster, CPU utilisation is up (yes, up – they were only using 5% of the available machine and you can’t save CPU for later!) But we did notice a few odd queries being run by the users; well the queries weren’t odd, it was just the bad way they asked them. So, on the back of our implementation we created a small course to help users write better queries.
Three things not to do:
- Miss out a join predicate
- Forget to join to the partition key
- Create a temporary table as a selection of all customers that bought product x, y, or z ever and then filter that selection on time and customer (thereby omitting to use partition pruning and several bitmap indexes) – this was a good one, the original query took 50 minutes, the rewritten version less than 4
I am looking forward to Jeff Moss presenting at the UK OUG BIRT SIG next week, I think the presentation will be fine.
Finally, if you buy your 15-year old a bass guitar, get her a small practice amplifier!