Can a joined query be as efficient as a single table?

I have one table that has data that is meant to be immutable. It has a hash nvarchar(64) as a unique key. I want to add other tables that have data that is related to it. Those other tables may be updated & therefore I think it makes sense to keep them separately. For the sake of explaining it, the main table is a financial ledger which has records that relate to things like sales/refunds/discounts/etc. The ancillary tables have additional data for each of those types. Each with different column headers.

The problem is that there are ~20M records in the financial ledger. I've tried by making the hash unique on both tables, making sure it is indexed on both & setting a foreign key relationship. But querying a month's worth of data takes about 5 minutes on just 2 tables VS 1 minute on 1 table.

Is there anything I can do to divide this data? If the shared keys were integers would that make a difference? Other than unique & indexed keys with a pk/fk relationship is there anything else I can do to optimize performance?

As it stands now I have a single, massive table with a lot of null columns. It works, but I can't believe that this is the best approach there is.

Yes, Use an integer surrogate key in the related tables rather than nvarchar(64).

Other performance issues would relate to indexing, but we would need many more details before being able to address any index issues.

1 Like

You might also want to look at sparse columns.

1 Like

Thanks, I'll try changing the pk/fk to an int.

This is essentially the schema to (2) of the tables. I've dropped columns that are irrelevant to the point. Can you provide any more insight knowing this? CREATE TABLE [tmp].[financial]( [id] [int] IDENTITY(1,1) NOT NULL, [dateadde - Pastebin.com

Thanks. If possible, I'd rather divide the concepts into different tables. Otherwise, sparse columns are definitely the way to go.

I'd recommend separate tables rather than sparse columns (for one thing, it's a more normalized design).

Get rid of the $IDENTITY column in the child tables, and instead use the unique ID from the main table. It can (still) be defined as a unique, primary key, clustered, it just shouldn't be an IDENTITY() column. If the main table doesn't have an IDENTITY() column, add one to it. Despite what some people say, not every table should have an IDENTITY() column. That id column from the main table should be linking fk.

Btw, the nvarchar(64) should still be there, in the main table, it's just not used for a fk.

2 Likes