Effect of PK data types on performance

[jon_sg]

I have a database with two data tables linked in a many to many relationship by a joining table containing the two primary key fields of the data tables as Foreign Keys and its own PK.

The joining table PK is set to Text and the PK's in the data tables are Programatically numbered sequentially as data is entered via the form.

The Joining tbl PK combines the two integer PK's programatically with a "-" inbetween; this significantly eases data validation as it will be impossible to enter duplicate info due to ref integrity.

My concern is performance due to indexing on a text field as the joining table will very quickly contain a lot of data.

I would assume that performing searches / filters that go through the joining table will be slowed by the text PK is this correct?

Also if anyone has any ideas on how to make this process more efficient while maintaining efficient data validation I would be very grateful

Jon

 

[FoFa]

If I understand it correctly, indexes are stored in a serperate "file" sorted so the system can do a binary search on them. Now Hard Drives will send a block (smallest amount of data a hard drive sends/receives basically) of data to the software, which picks out a sector (smallest portion the OS will send/receive basically) and makes individual data elements available from this. So since text items usually take more bytes than say numeric items (I think a long is 4 bytes) you can store more index items in this sector and block of data without making a return trip to the drive (incurring I/O) if it is numeric. So if your text field is 12 bytes, you have basically reduced the stored (memory) capacity of indexed items by 1/3. Indexes speed performance by limiting I/O to the slowest portion of the process (the hard drive). When the index is found, it contains a pointer to the actual data, and the software directs the hardware to get that hunk of data without reading through all the rest of them. There could be a minor amount of time spent in the text comparison vs a numeric comparison, but I would not think that would be as much of a factor. But the example above would not decrease performance 1/3 either as one might assum, it just would increase the I/O if the indexes needed to be read sequentially, which is not as big of an issue. I would guess the performance decrease depends on the size of your text index, and most likely would be in the under 5% area. I would not worry about it personally.

 

[Pat Hartman]

The key to the relation table should NOT be a concatenated field it should be a two field key. To make a key (or index) contain more than one field (Jet supports up to 10 fields), simply select the first one, then hold the control key and select the second. With both fields highlighted, press the key button. Then look at the indexes form to see how the primary key was constructed. You'll see the word PrimaryKey on the first line but blank on the second. If you wanted to add a third field to the pk, you would just insert it after the second, making sure to leave the key name field empty.

You should also create separate non-uinque indexes on each of the foreign keys to facilitate join processing.