将字符串转换为唯一编号/数据库性能 [英] Convert string to unique number/DB performance

问题描述

我有一个庞大的数据库，其中所有索引主要构建在一列 - CountryID。此列在数据库中以整数形式存储，值可以在1-99范围内。我的公司很快将不在国家ID中，并且首席架构师已经决定，不应该将countryid增加到三位数值，而应该可以使用字母数字值。

示例：

目前：01

未来可能：A1



数据库非常庞大且已经受到影响在一些报告中表现不佳。我担心如果我们将countryId列切换到数据库中的char，性能会更差。有没有人对我该做什么有任何建议？我想在将其输入数据库之前，我可以将国家IDA1转换为ascii值。这会增加性能（与char值相比）吗？



谢谢，

Sunker

I have a huge database where all the indexes are mainly built on one column - CountryID. This column is stored as an integer in the database, and the value can be in the range 1-99. My company will soon be out of country id:s, and the chief architects have decided that instead of increasing the countryid to a three digit value, it should be possible to use an alphanumeric value.
Example:
Currently: 01
Possible in the future: A1

The database is really enormous and already suffering from bad performance in some reports. I’m afraid if we switch the countryId column to a char in the database, the performance will be even worse. Does anyone have any suggestions to what I should do? I suppose I could convert the country Id "A1" to an ascii value before I enter it in the database. Would that increase performance(compared to a char value)?

Thanks,
Sunker

推荐答案

为什么不让CountryID数据类型为带有identity属性的整数，以便您的数据库将处理ID的创建？整数数据类型不允许仅在1到99之间的值;所以我无法真正理解你的问题是什么。



为主键设置char值是一种非常糟糕的做法。索引整数值在性能方面效率更高。

Why don''t you just let the CountryID datatype to integer with identity property, so that your database will handle the creation of IDs ? Integer datatype does not allow values only between 1 and 99 ; so I cannot really understand what your problem is.

It is a very bad practice to setup char values for primary keys ; indexing integer values is much more efficient, performance-wise.

请参阅我之前的帖子：生成id喜欢''Cust001'' [ ^ ]，您将在其中找到信息：为什么不使用id''00001到99999;）

See my previous post: Generate the id Like ''Cust001''[^], where you''ll find information: why to not use id''s like "00001" to "99999" ;)

这篇关于将字符串转换为唯一编号/数据库性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！