ETL from hell – getting past denial

A great article here on diagnosing problems with batch system performance: https://www.simple-talk.com/sql/performance/the-etl-from-hell—diagnosing-batch-system-performance-issues/

Basically it goes like this – when there’s problems with batch processes overlapping or timing out, companies typically try the following:

  1. Can we ‘fix’ this with more hardware? (this is expensive, and a temporary fix)
  2. Can we change the product – for example from SQL to Oracle? (this only work with a redesign. Usually the product isn’t the issue!)
  3. Turnkey solution (expensive and typically doesn’t address the root issue)
  4. Split systems into parts, each on isolated servers (hard to synchronize running jobs across your now-separate systems)

Instead of these knee-jerk reactions, there’s a better way – both strategic and tactical – to fix this:

  • Tactical
    • Gather statistics – how long is a process likely to take?
    • Get a baseline of long-running problem queries
  • Strategic
    • Determine processing time windows
    • Create a task list in Excel – and include internal/external dependencies
    • Monitor the system – and include management in monitoring reports
    • Define threads – tasks in sequence – and processing times
    • Implement batch system control that runs off your table of tasks/dependencies
Advertisements

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