将字符串转换为唯一编号/数据库性能 [英] 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屋!