Clarify performance boost when viewing activity log

My customer was experiencing very slow response times when viewing the activity log for any object.  After doing some investigation, i found the query that was the source of the problem.  The Clarify thick client was doing a select * from table_site where is_default=1.  Now i can't remember if we had a bitmap index on this column or not, i think we did, but with a database with 20+ million sites and 20+ million contacts, that's a whole lot of records to index for only wanting to find the one site where is_default=1.  All the others should be set to is_default=0.  Well since they are on Oracle and we know that Oracle doesn't index null columns, i basically wrote a script to update all the sites where is_default=0 and set it to is_default=null.  This left just one record in the index, the site where is_default=1.  This dramatically improved the performance of activity log viewing.  Many of you will say this results in an on-going maintenance issue since the default value for is_default is 0, any new records will be initialized with 0.  This is true, and i didn't spend a whole lot of time modifying the schema to fix this (The customer paid hourly and wasn't all that concerned with long term fixes, they just wanted the problem fixed now and as cheaply as possible).  i left them with the instructions of running the update script periodically if they deemed the performance degrading too much.

The rule about not indexing null columns is true for Oracle, however i'm not its true for sybase/sql server/db2.  So i'm not sure this trick will work where Clarify is deployed on those databases.

The customer environment is:
Clarify 8
Oracle 9i
Red Hat EL3

One side note, this is an example of one of my personal beliefs, which is that many times biggest performance gains result from software tuning rather than hardware tuning or throwing more hardware at the problem.  There are some many occassions when i hear a customer say they would rather spend a couple $1k on new/additional hardware than spend the same amount of money in software tuning.  Quite frustrating, but i think generally people like to buy/posses shiny new boxes.

Posted at 12:00AM Feb 28, 2007 by Robert Eister in Software  |  Comments[0]