All geek questions in one place

Sql performance lookup tables

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

So other tables have currency_id, country_id.

I have a complex sql that returns a bunch of such identifiers. To get the actual values, there are 2 options:
1. Use a connection to the lookup table
2. In the project, the previous developer implemented a function in which he has a transfer class for a country, a transfer class for a currency, etc.
Therefore, it simply extracts the identifier from the sql result set and looks up the value using the enum class. In his opinion, this is happening faster.

I can, of course, determine by setting the start and end time, which is faster, sql join or enum lookup.
But without doing this, is it possible to predict which one will be faster?

+4
source share
1 answer

Two reasons:

  • You have a lookup table to remove data change anomalies. That is, you can change data in one place only when changing the search data. Now you need to compile and release
  • RDBMSs are designed to be attached. Enum is still a JOIN in client code only

Note:

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

(Added: December 2011):

  • How to ensure the correct search value in the right table?
  • At some point you will have more than one database client, do not obfuscate the data with transfers

There is no support in DBA.SE for Enums or OTLT:

+5
source

Source: https://habr.com/ru/post/1344547/

More articles:


All Articles