CODEWITHSUNDEEP
sqlsql-server
Force
Force

Reputation: 3358

First and Last name tables to increase performance?

Let's say that I have a customer table:

CustomerID | FirstName | LastName 
1          | John      | Smith     
2          | John      | Adams
3          | Kevin     | Smith
4          | Kevin     | Adams

Now imagine that this table has 200k+ rows. Would it increase performance to create a separate FirstName and LastName table as shown below and then using joins to get the view above?

Example:

FirstNameID | FirstName
1           | John
2           | Kevin

LastNameID | LastName
1          | Adam
2          | Smith

CustomerID | FirstNameID | LastNameID
1          | 1           | 2
2          | 1           | 1
3          | 2           | 2
4          | 2           | 1

Upvotes: 2

Views: 225

Answers (3)

usr
usr

Reputation: 171178

It depends on your query workload. This is a simple form of data compression. Reducing the set of data that is needed to answer a given query can improve performance.

On the other hand you introduce overhead in many places. It is a trade-off. If you want to retrieve the values of those columns you now need to join. DML becomes slower as well.

Since the name columns are probably rather small it is hard to imagine a query workload that would benefit from this scheme.

DATA_COMPRESSION and backup compression can be alternatives. They make very different trade-offs.

Just to prove that there is merit to your scheme: Imagine very long names and a huge (TB-sized) table. The space savings would be significant because there are rather few distinct names.

Upvotes: 2

Scott Saunders
Scott Saunders

Reputation: 30394

There isn't any action involving the names that would be made faster by having to join three tables.

Short answer: No.

Upvotes: 0

John Bollinger
John Bollinger

Reputation: 180256

Whether such a move would increase performance depends on what queries you perform, but it is likely to do the opposite for many of the queries that I envision you wanting to perform.

Upvotes: 4

Related Questions