I am designing a multi-module C#/SQL Server-based application. My design is to hold all generic look up values in a table called KeyTypeValues. This table is related to KeyTypes which defines what type of data it is.
For example:
oms.KeyTypes
------------
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY
KeyTypeName VARCHAR(40) NOT NULL
...
oms.KeyTypeValues
-----------------
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY
KeyTypeId INT NOT NULL (FOREIGN KEY to oms.KeyTypes Id)
KeyTypeValueMeaning VARCHAR(80) NOT NULL
...
oms.KeyTypes sample data:
Id KeyTypeName KeyTypeDescription
-- ----------- ------------------
1 RES_MFGS Resource Manufacturers
2 RES_OWNERSHIP_TYPES Resource Ownership Types
...
oms.KeyTypeValues sample data:
Id KeyTypeId KeyTypeValueMeaning
-- --------- -------------------
1 1 Ford
2 1 Chevrolet
3 2 Owned
4 2 Leased
...
So the idea is that I don't have to create separate Manufacturers, OwnershipType, Model, etc. etc. tables as we don't really need any additional information about these values other than their value. Currently I have about 88 already defined and the design has worked well for me.
I'm working on a problem query that is giving me performance issues when joining from a table called res.ResourceItems. I have to join it onto the KeyTypeValues table 6 times for different lookups.
Part of ResourceItems definition:
res.ResourceItems
-----------------
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY
OwnsershipTypeId INT NOT NULL
ManufacturerId INT NOT NULL
...
If I eliminate my problem key type (RES_OWNERSHIP_TYPES), I can run it wide open and it pulls back ~112,000 rows with 70+ columns in about 17 seconds. The performance isn't fantastic, but considering I'm having to join onto 9 additional tables, it is acceptable. However, when I add the join to retrieve RES_OWNERSHIP_TYPES, the execution time jumps to 45 seconds. The the RES_OWNERSHIP_TYPES key type only has 3 possible values at this point and oms.KeyTypeValues only has about 3,000 total records in it. It will continue to grow slowly over time as we add more to the system being built.
I realize that pulling Ownership Types out and creating an enum instead would be a more efficient way to handle this as it is unlikely that we'll have anymore ownership types; however, I am concerned with the overall design where there is such a dramatic performance hit.
I do have Foreign Key relationships from res.ResourceItems to oms.KeyTypeValues for all the Id values. I also have Non-Unique, Non-Clustered index setup on oms.KeyTypeValues.Id column. I've rebuilt them to eliminate fragmentation.
As a test, I created separate KeyTypes and KeyTypeValues tables in my res schema and loaded just with RES_OWNERSHIP_TYPES values and joined to it, and the execution time went back to about 17 seconds. I'd rather not carry this out as it sort of defeats my purpose and seems to be putting a band-aid on a bigger issue.
I cannot determine why there is such a big hit just with that join and was hoping someone might have insight into what I might be overlooking. I'll be happy to share more of the database design if needed.
If adding a join to a table with 3 records is impacting your performance that significantly, it means the query plan is changing dramatically with the addition of that join.
The quick and dirty thing to try is to run sp_updatestats one-time to see if that solves your problem (and I think it likely that it will). This will tell SQL Server to update the statistics on all tables and indexes and help it make better choices with query plans.
The more thorough solution is to compare the execution plans of the two variations of the query to understand why performance is suffering. This should shed light on how to improve performance. I do not advise removing the table and going a code-only enum. If things are working correctly, there should be no discernible performance difference in joining in that extra table.