Convert string to unique number/DB performance

Question

0.00/5 (No votes)

See more:

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

Posted 19-Mar-13 23:52pm

erik.sundell87@hotmail.com

Updated 19-Mar-13 23:53pm

v2

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2013-03-20T00:19:00

Solution 1

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.

Posted 20-Mar-13 0:19am

phil.o

Comments

erik.sundell87@hotmail.com 20-Mar-13 6:58am

Thanks for the reply!

I know, but my company has limited CountryId to two digits. It will never be > 99. We will soon be out of CountryId:s(we have like 10 left), and the solution will not be to increase the integer (e.g CountryId: 100), but to add the possibility to use alphanumeric values. E.g A1. And obviously, I cannot add ”A1” to the CountryId column since it’s stored as an integer...

I have built almost every index in the database based on the fact that CountryId is an integer, and I want to keep that design. So, should I convert CountryId to an integer is some way?
A1  4209 (to decimals)
A1  1000001110001 (to binary)
..and then I can keep my indexes.

Or do you know a better solution?

phil.o 20-Mar-13 7:06am

Like I said, this is silly to setup a primary key as anything else than an integer (byte, short, int, long) value.
If you want to do it, go on. But you will have been warned about the performance hit.
What the hell is causing problems with 3-digits numbers ?

erik.sundell87@hotmail.com 20-Mar-13 7:21am

I know, it's stupid. But there are many files with fixed positions curculating in this company.

I have no intentions making the field a char. I'm sorry i'm beeing unclear. I guess this is more of a programming question than a database question. Is there some kind of convention for this problem? How to convert an alphanumeric value into an integer?

phil.o 20-Mar-13 7:29am

Think of it 5 minutes : how do you want to convert an alphanumeric value, which can hold more than 100 distinct values, into an integer value between 1 and 99 ? This is simply not possible.
It is a design question IMHO.
You have 2 solutions : you keep your 2-characters values and rebuild your table with a char(2) primary key, but you will hit some performance penalties ; or you let your primary key values go beyond 99, and will have to rework your fixed-width documents and the processes taking care of them.

Jörgen Andersson 20-Mar-13 16:18pm

The performance hit from using a char instead of an integer is only of academical interest.
Why can't you use negative numbers?

Matej Hlatky 20-Mar-13 8:45am

After reading your questions few times, I still don't understand your question.
Anyway, just look at the hexadecimal numeral system (base 16) or Base 36.
If you will be forced to use char(2), just use Country ISO codes (ISO 3166-1) rather than pseudo sequence numbers.

phil.o 20-Mar-13 8:49am

I think you hit the wrong Reply button ^^

Matej Hlatky 20-Mar-13 8:51am

yep. sorry

Maciej Los 20-Mar-13 15:53pm

Agree, +5!

Maciej Los · Answer 2 · 2013-03-20T09:57:00

Solution 2

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

Posted 20-Mar-13 9:57am

Maciej Los

Convert string to unique number/DB performance

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Convert string to unique number/DB performance

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us