Suggested Methodology for large lookup table functionality?

[Jason Alexander]

As part of a major upgrade we find ourselves in a position where we may need to build a large look-up table as part of a HIM upgrade.  Current estimates suggest we’re looking at roughly 30,000 rows in the translation table.  It’ll be used on roughly 100,000 transactions per day (fairly common, 10-hour bimodal distribution throughout the day).

By the time we implement this we should be running on 6.1 on an AIX 6.1 box.  We’re looking for any experience folks have had as we consider how to build this.  We can create a standard Cloverleaf table.  We could store the data in a text file and load it into a keyed list in resident memory during thread start-up (which I believe is technically equivalent to the Cloverleaf Table).  We could build GDBM or sqlite DB tables instead.

Are there other methods we are not considering at this time?  Does anyone have experience running such large tables under any of the methods we’re considering?  Any lessons learned that we should be aware of which could help with the decision or optimization of the eventual solution employed?

Topic

[James Cobane]

Hi Jason,

My recommendation would be to put this into an SQLite DB.

[Jim Kosloskey]

Just an additional piecce of information – loading such a table into a keyed list is not equivvalent to the Table I believe.

Efforts were made to optimize the table load in the engine so that it actually uses less space and is traversed very fast.

Having said that, a Table that large will have an impact.

It will run – I did a test quite a few releases back on an old machine of a similarily sized Table and the impact was not horrendous and actually executed quite fast.

If I were you I would do a test with a subset of the Table using a good size but not the entire table.

Try both the Table and SqlLite solutions and use the one that seems the most reasonable.

Another possibility (if you are licensed for the ODBC Drivers) is to use ODBC to directly access a source Table maintained by the system of truth. That would relieve the maintenance headache whiich I think will be attendant to any of the other potential solutions.

Alternatively if the release of Cloverleaf you are on supports it, you could use JDBC in the Xlates or whatever. I know one of the 6.x.x releases is supposed to provide the ability to query a DB from the Xlate just not sure if that is out yet or if you are on that release.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Rob Abbott]

Cloverleaf 6.1 supports querying a database during translation.  It’s actually an enhancement to the table configurator.

What Jim K. says about table optimization is true.  Large tables should work fine and should be the fastest option for lookup.  I’d take his advice and test SQLite against the local Cloverleaf tables.

What Jim C. says about GDBM is very true – this is not recommended as it’s been deprecated and is no longer shipped with Cloverleaf.  SQLite is the replacement for GDBM.

Rob Abbott
Cloverleaf Emeritus

[David Barr]

We’ve used ODBC to solve a similar problem. We’ve used it to get data from SQL Server and Informix. You probably have too much data to put in a Cloverleaf table, but SQLite should be able to handle it if you’d rather not use ODBC. I agree with Jim that it would be good if you can access the data from a stable source that you don’t maintain rather than having to do regular imports or maintenance of a local table.

[Yves Guerin]

Hello,

Rob Abbott wrote:

What Jim C. says about GDBM is very true – this is not recommended as it’s been deprecated and is no longer shipped with Cloverleaf.

[Author]

Replies