Table lookup with database lookup, questions

[Jason]

Hi there, so I’m thinking about trying out database lookups for my table lookups, couple of questions:

• will there be a performance hit since it has to go out to a database to do a lookup?
• if there’s an issue with the database/table, what happens with the Cloverleaf message/engine?
• what’s the difference between basic and advanced database table lookup?

Thanks and any help or advice is greatly appreciated!

 

Topic

[Jim Kosloskey]

It is good you are thinking this through before using.

While I have never done this, by inspection this is what I would expect:

I think there would be some additional overhead as the Tables are stored in memory with first reference then the memory version is used thereafter (until process or potentially thread stop) whereas the DB based Tables will go through the DBMS access each time. Depending on the DBMS used that would vary I would think.

If there is an access issue I suspect the TABLE Action may error at which point the setting you select in the TABLE Action Error entry (Skip (default), Pad, Error (put the message in the Error DB) will take place.

The difference between Basic and Advanced that I see is you can specify a Stored Procedure to use for the lookup. I imagine that might allow you to return a value which is the cause of the error (such as “can’t connect”, or “not found”, or whatever). Then in your Xlate you can test for error conditions and take appropriate action (perhaps retry the connection or trigger an alert of some kind and dispose of the message in a more intelligent fashion).

Hopefully someone who has also thought this through AND actually utilized the DB lookup (both basic and advanced) can reveal the real facts.

 

Otherwise you will need to do that experiment an let us all know how it goes.

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

[Dirk Engels]

Hi Jason,

the points Jim wrote are right. he performance is highly depending on the database. Also you must be aware, that it can error out.

The difference between basic and advance dblookup is, that in advance you specify one table and one or more input and output fields from that table as where in the advanced lookup you can do with a sql statement whatever you like. Of course in a dblookup only select is allowed, but you can query over multiple tables, you can use inner selects and join and group results.

We have used both in xlates at our customer base. The overall performance is ok. Modern servers should be fast enough to handle this. Of course the performance is also based on the complexity of your sql statement.

[Author]

Replies