一个查询表的sql性能 [英] sql performance of a lookup table
问题描述
所以其他表都有currency_id，country_id。
我有一个复杂的sql，返回一堆这样的ids。要获得实际值，有两个选项：
1.使用查询表的连接
2.在项目中，以前的开发人员实现了一个功能，国家的枚举类，货币等级的枚举类。
所以他只是从sql结果集中获取id，并使用枚举类查找该值。他的意见是这样更快。
我可以通过将开始和结束时间更快，sql加入或枚举查找来确定。
但是没有这样做可能预测哪一个会更快？
两个原因：
- 您有一个查找表来删除数据修改异常。也就是说，只有在查找数据更改时，才能在一个地方更改数据。现在您必须编译并发布
- RDBMS旨在加入。一个枚举在客户端代码中仍然是一个JOIN
注意：
您不应该在一个真正的查找表（OTLT）反模式中具有一个查找表。您只能在表格中存储一个实体。
（2011年12月添加）：
- 如何确保您在右表？
- 您将在某个时候有多个DB客户端，不要使用枚举模糊数据
在DBA.SE上，不支持枚举或OTLT：
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 has 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.
(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:
这篇关于一个查询表的sql性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
