Are your Lookups Real-Time Ready?

This is just a reminder for all of you using Lookups of any kind in a real-time ETL process. When the focus is on performance in a “batch” ETL flow, we often choose options for lookups that ask the ETL engine to pre-load reference tables into memory. Static lookups of this type might be fine for a real-time scenario, but may also be disasterous if you are expecting to get positive results on a lookup for a reference table that constantly changes.

Check your lookups and choose the right option for your needs.   I was recently reminded of this fact on a current project.  Something to always remember to consider.

In DataStage Server edition, lookups are dynamic by nature, and you need a hash table to force lookups into memory. In Enterprise Edition, however, you specifically need what is referred to as a “sparse” lookup. This is a property that you select inside the Stage.

-e

Advertisements

2 Responses to “Are your Lookups Real-Time Ready?”

  1. Milind R Says:

    hi Vincent,

    Are you suggesting that if we are doing a lookup on target table in an always running job then we should use a sparse lookup?

    • dsrealtime Says:

      Hi Milind…..like anything, it depends. Does the table you are accessing ever change? Are you using an EE (parallel) Job? If so, a standard lookup prepares the data by bringing it into memory — one time. If your table changes, and the job is always running, and you use a standard lookup, you will never “see” new rows. Sparse lookups always go back to the database, and will find those new rows. The potential sacrifice is speed. In-memory lookups can be very fast. You need to decide which is important to you.

      Ernie


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

%d bloggers like this: