Joining on a Combined Synthetic Primary Key Instead of Multiple Fields

Introduction

When working with SQL queries that involve joining multiple tables, it’s not uncommon to encounter situations where we need to join on one or more columns. In the context of the given Stack Overflow post, the question revolves around whether using a combined synthetic primary key instead of individual fields for joining leads to significant performance losses. This article aims to delve into this topic, exploring its implications and providing insights on how to approach similar queries.

Understanding Synthetical Primary Keys

In SQL, a synthetic primary key is an expression used in the SELECT clause that creates a new column as part of the result set. The columns involved in this expression can be used for joining purposes if they satisfy certain conditions. In the context of the original query, we create two temporary view-like tables (WINNERS_NEW and POINTS_NEW) to replace individual fields with synthetic primary keys.

Benefits of Synthetic Primary Keys

On one hand, creating a synthetic primary key can offer benefits such as:

• Simplifying queries: When dealing with multiple join conditions or joining on complex logic, using synthetic primary keys allows us to avoid cumbersome column names and focus more on the core logic behind our queries.
• Query readability: By hiding underlying table structures from the query surface level, we can make our SQL statements easier to understand.

Performance Implications

However, creating synthetic primary keys has a notable performance drawback:

• Index utilization: When using individual columns or fields for joining purposes, SQL Server (or similar databases) tends to favor indexing on these columns for better performance. If you decide to use an expression instead of individual fields for your join condition, you prevent the database from making optimal use of existing indexes.

-- Index Creation Example

CREATE INDEX IX_Winners_PPlayer ON WINNERS (PLAYER);
CREATE INDEX IX_Winners_Team ON WINNERS (TEAM);

-- Query that joins with only PLAYER and TEAM columns
SELECT
    W.PLAYER
  , W.TEAM
  , P.POINTS
FROM WINNERS W
INNER JOIN POINTS P
    ON W.PLAYER = P.PLAYER AND W.TEAM = P.TEAM;

In the synthetic primary key scenario, you create new indexes on these expression fields:

-- Index Creation Example

CREATE INDEX IX_Winners_NewID ON WINNERS_NEW(ID);
CREATE INDEX IX_Winners_NeWPlayer ON WINNERS_NEW(PLAYER);
CREATE INDEX IX_Winners_NewTeam ON WINNERS Newman (TEAM);

-- Query that joins with synthetic primary key
SELECT
    WN.PLAYER
  , WN.TEAM
  , PN.POINTS
FROM WINNERS_NEW WN
INNER JOIN POINTS_NEW PN
    ON WN.ID = PN.ID;

As demonstrated in the example, creating separate indexes for these new columns can significantly slow down query performance. This is because databases cannot utilize existing index structures as efficiently.

Limitations and Workarounds

While using synthetic primary keys simplifies certain aspects of your queries, keep the following considerations in mind:

• Limited expressiveness: Synthetic primary keys can become cumbersome to work with if you need complex logic for joining purposes. In these situations, it’s often better to stick with individual column names.
• Index creation challenges: When using an expression as a join condition, SQL Server can’t create indexes on those columns in the way that it would for individual fields.

-- Using Expression-Based Join with Existing Indexes

CREATE INDEX IX_Points_NewID ON POINTS_NEW(ID);

SELECT
    P.PLAYER
  , P.TEAM
  , W.POINTS
FROM WINNERS W
INNER JOIN POINTS_NEW PN
    ON W.PLAYER = PN.ID AND W.TEAM = PN.ID;

In general, it’s more efficient to stick with individual column names when creating join conditions. However, for specific use cases where a synthetic primary key improves readability or reduces complexity in your SQL statements, you can still create expression-based joins.

Conclusion

Using synthetic primary keys instead of individual fields for joining purposes might seem like an attractive alternative due to improved query readability and reduced maintenance costs. Nevertheless, there are essential factors that come into play when deciding whether this approach is suitable for your specific use case.

When using a synthetic primary key as part of the join condition, you will encounter performance drawbacks due to index utilization limitations. These can lead to slower query execution times if not addressed through proper indexing strategies.

Last modified on 2023-10-18