When too much of everything is never enough

For some people some things can’t happen soon enough and last week I saw two cases of that. 

In the first I saw Neil Hambly’s presentation at a SQL Social event where he showed how indexed views can speed up known queries on large amounts of relatively static data by magnitudes which 10 years ago could never have been imagined.  By caching, or materialising, a virtual table Neil showed how complex BI style queries can be reduced from hours or minutes to seconds or even milliseconds.  This is great; it gives the ability to report ad-hoc on large complex data sets without having to wait for overnight ETL or cube rebuilding tasks, providing you know roughly the query in advance. 

For cases where the underlying queries either can’t be written as indexed views or the data doesn’t even come from within SQL Server PowerPivot has also made querying on huge data sets in near-real time feasible.  Within the familiar environment of Excel power data users can slice and dice hundreds of millions of rows, again in a way which only a few years would never have been dreamt of. 

In summary, data querying technology has progressed so much in recent years that complex analysis on large data sets in near-real time is now feasible, and hopefully possible most of the time.

In the second case I saw the opposite – someone trying to reduce the time housekeeping tasks were taking and complaining when they couldn’t get them any lower.  There were two issues here: the performance of the hardware being used meant the tasks were running as quickly as you’d hope for, but more importantly, the user didn’t actually have a valid reason for needing them to run any faster, he just wanted them to as he felt things could always be optimised.

When you design a system you should understand that some things will always take a certain time.  Reporting on a sales history database might improve by using the technologies discussed above but backing up a 20GB database is never going to happen in seconds, no matter how much you optimised your code or server workload.  The same acceptance should even be made for functional operations within an application.  If placing an order has to complete 12 external tasks to succeed the functional owners should accept it’s never going to happen in milliseconds.  Sometimes it is worth investing dev or DBA time in order to optimise code, reducing a query runtime from 120 seconds to 2 demonstrates a clear return on that dev time.  However, is it really worth trying to optimise that code even further to make it run in 1 second instead of 2?  If it means your system can generate twice the revenue then maybe, if no one will actually notice then it’s probably time to move on to the next problem.

My message here is that it’d be great if everything could be optimised to run in milliseconds, either through new functionality, code optimisation or throwing large amounts of hardware at a slow running task but we have to realise that some things take time.  You will never backup a 20GB database in milliseconds, which is handy because only a few people on this planet would ever need to.


One thought on “When too much of everything is never enough

  1. John Sansom

    Nice post Gavin!

    I see this type of thinking out in the data world a lot. In our roles as Data Professional’s we are naturally quite detail oriented types, often having to focus on the small print. Such a mindset however can be detrimental if it forms a rigid habit. It’s important for us to ensure that every so often we take a step back to look at the big picture and how it applies to what we are currently doing, especially where the dark arts of performance tuning are concerned.

    For me it’s about an appreciation of the 80-20 Rule, along with knowing when a deliverable is “good enough”, the later I think comes with time and experience.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s