Index & FK Relationship On LookUp Tables

Last Post 19 Jul 2007 10:15 AM by GoldenGal. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
wackoyacky
New Member
New Member

--
11 Jun 2007 03:49 PM
Hi!

It's been always said that it is best to put index on commonly joined fields in the table. But putting too much index on the table would cause the table to be slow on insert and update.

My question is, how do you deal with your fields that uses look up tables? Like for example for these fields

- CountryID(smallint)
- CreatedBy(int)
- ModifiedBy(int)
- Status(tinyint)

Those fields don't come a big part in the table, though when I query the table I always join them with their respective primary table to get their respective text value. Do I still need to put Index & FK relationships to these fields?

What fields are normally good candidates for index or fk relationships?

Thanks,
Enzo
GoldenGal
New Member
New Member

--
19 Jul 2007 10:15 AM
I'm going to join the other replies to your question and state that, rule of thumb, index foreign key columns and other columns that you continuously use for range of value searches and sorts (ORDER BY).

Looking at your examples:
- CountryID(smallint)
- CreatedBy(int)
- ModifiedBy(int)
- Status(tinyint)

CountryID, high selectivity, you'll want to index this one.

CreatedBy and ModifiedBy, probably not. Have you considered doing a bit of refactoring, like altering the insert(s) and update(s) that populate these two columns and modify them to carry not the numeric identifier, but the text string itself? You'd be doing a little denormalizing, sure, but if you're all the time joining to get the text value, and if you don't have a whole lot of people or programs that are inserting and updating (low selectivity), then creating an index on these columns won't help much. If your benchmarking shows that this is a drag on performance, consider this approach.

Status, high selectivity, but this one lends itself to refactoring, also. In many of my columns I've suggested that lookup tables will benefit from a "natural" key rather than a numeric identifier -- and here's a perfect example why. Instead of using a number (1,2,3), derive a 1, 2, or 3-letter code from the status value (OP, CL, WT) and use that as the primary key for the lookup table. Or, better yet, if the status is expressed as a single short word (OPEN, CLOSE, WAIT), use that as the primary key. There's no rule that says you can't have a single-column lookup table. Now, in this foreign key column, instead of a number, you'll be able to see the value, either as a 3-letter code or a short phrase -- both of which are self-explanatory, neither of which require joining to the lookup table in order to decipher.

Indexing for joins is grand, but if you don't have to join, that's the best option!


SQLUSA
New Member
New Member

--
22 Jul 2007 04:00 PM
Michelle,

The country lookup table ( < 200 rows) maybe too small for indexing consideration.

Also, there is usually a dominant country (like United States).

Loading it up into a @tablevariable at the beginning of a sproc maybe the best solution.

Kalman Toth, MCITP: Database Administrator
SQL Server 2005 Training - http://www.sqlusa.com
GoldenGal
New Member
New Member

--
23 Jul 2007 01:53 PM
Kalman,

>> The country lookup table ( < 200 rows) maybe too small for indexing consideration.

Could be a single page I/O for the entire table. When I think of a lookup table, I consider how it's going to be used, and a lookup table, in addition to being part of many join operations, is going to be called upon to populate drop-down and pick lists. Because of this dual requirement, I think "index the table, and what's more, dump the entire table into a clustered, covering index based on country name, if that's the order needed in the drop-down list."

The point is, no matter how small the content of the table, if you need the content to display in a specific order, then having the data stored in display order is a very efficient solution. Storing the content in a heap structure will require an ORDER BY statement behind every drop-down list -- not efficient. Being able to just write "SELECT * FROM Country", and know that the data will be returned in alpha order by country name with nothing more complicated that a single (or two-page) I/O on SQL Server's part, very efficient.

I know that SQL is supposed to abstract the user (in the case, the programmer) from knowing how the data is stored, but hey, we're now tuning the query. So, if you know that your lookup table is stored in a specific order, then you can also write your joins more efficiently, also.

>>> Also, there is usually a dominant country (like United States).

That's easy -- add a smallint (or even tinyint, if you think the list will stay very small) column called SortOrder, manually order the rows as you want them to show in the drop-down list, then cluster on SortOrder.

>>> Loading it up into a @tablevariable at the beginning of a sproc maybe the best solution.

That's an excellent idea, depending on how many times that sproc is going to be called during the day. Have you used PINTABLE to leave the table in memory? How does that work for you? Seems like this example (a 1-page table) would be a good candidate... need to test.
SQLUSA
New Member
New Member

--
24 Oct 2007 09:35 PM
>Being able to just write "SELECT * FROM Country", and know that the data will be returned in alpha order by country name with nothing more complicated that a single (or two-page) I/O on SQL Server's part, very efficient.

Michelle,

I have to disagree. That kind of coding is not portable and also not maintainable.

You pass the code to a maintainance SQL developer who may not be as familiar with the intricacies of lookup table optimization, and in no time you would see an ORDER BY attached to the query " just to be on the safeside".

I acknowledge though that it is delightful to read your views on this topic.

Kalman Toth, MCDBA, MCITP
SQL Server Training - http://www.sqlusa.com/order2005grandslam



You are not authorized to post a reply.

Acceptable Use Policy