sql performance of a lookup table

I have a lookup table which is master table of id-value pairs. This table is used to store static data like :all countries, currencies etc.

So other tables have a currency_id, country_id.

I have a complex sql that returns a bunch of such ids. To get the actual values, there are 2 options:

1. Use a join with the lookup table

2. In the project, the previous developer h开发者_运维百科as implemented a feature whereby he has an enum class for country, an enum class for currency etc.

So he just fetches the id from the sql resultset, and looks up the value using the enum class. His opinion is this is faster.

I can of course determine by putting start and end times which is faster, sql join or enum lookup.

But without doing that, is it possible to predict which one will be faster?

Two reasons:

• You have a lookup table to remove data modification anomalies. That is, you can change data in one place only when lookup data changes. Now you have to compile and release
• RDBMS are designed to JOIN. An Enum is still a JOIN just in client code

Note:

You should not have one lookup table in the "One True Lookup table" (OTLT) anti-pattern. You store only one entity in a table.

• Common Lookup Tables
• OTLT and EAV: the two big design mistakes all beginners make
• Google search

(Added Dec 2011):

• How to ensure you have the right lookup value in the right table?
• You will have more than one DB client at some point, don't obfuscate the data with enums

On DBA.SE, there is no support for Enums or OTLTs:

• https://dba.stackexchange.com/q/6962/630
• https://dba.stackexchange.com/q/6987/630