Search/Query Speed

[laa]

I have an odd question. How much faster will Progress search through an Integer index versus a Character index? Say you have a basic maintenance table that contains an alphanumeric code and description. Is there really any time gained by adding an integer field as the primary index to the maintenance table that is used to link the table to the transaction tables?

Just hoping for some insight here.

Anne.

 

[bendaluz2]

I believe it is faster, yes - though I am not sure how much. The main reason I would do what you are suggesting is not for query speed, but so that the user can change their codes at will. Consider the following. A system is set up with a number of suppliers and used for a number of years. Then, a change in practice elsewhere in the business means that all the supplier codes are recoded. If you used the codes as your primary key throughout the system, you would have to trawl through all the data and change all the codes consistently so that current and historical data would relate properly. Or, just remove all the historical data. If you used internal integer codes (which the user never sees) to relate all the tables, the user would simply be able to change all of their codes, and everything would still relate correctly. Personally, I always do this, I find it saves an enormous amount of time further down the line, and if it also speeds up my queries slightly, then all the better

 

[laa]

Thank you for your insight. I like your reasoning for having the numeric index. That may have had more to do with the decision than the query speed. Sometimes it is hard to look at an undocumented system and try to figure out the reasoning behind the structure.

Anne.

 

[toby.Harman]

As well as conforming to Codd's rules of database design!

I am a huge fan of using data that the user never sees and is unique for the Primary Key. Duplicate key problems are easier to solve, and replication implementation is a lot simpler.

I worked on a system a while ago which had a 250GB database which 90% consisted of 1 table, which had 11 indices, none of which were unique. That was a little tiresome, especially to implement replication!

Index speed will largely be determined by the number of index entries in a block which will (in turn) depend on the compression of indices which you can achieve. I believe that an integer index will compress better than a character index, and therefore 1 block will contain more keys which will enable faster searches. I don't think that the mechanism for scanning characters is particularly slower or faster than the one for integers.

Toby