0

I am looking to understand a typical approach to indexes in a database parent-child design. This is a basic financial calculator for calculating complex commissions used monthly and driven by stored procedures.

Currently, the base number of transactions driving this is about 400,000 rows. Every month this increases approximately 3-5%. Each transaction may have 1-5 (average of 3) commission records stored, meaning that each month a million records or more are inserted into the below tables.

Often, commission is run and deleted multiple times depending on analysts adjustments.

Environment: SQL Server 2016 standard, 32Gb memory, 8 cpu cores

Design:

transaction_mstr - Table that stores each commission run date, period, and user details (who ran it, what date and time, etc.)
transaction_map - Table that stores every account being commission in the period
transaction_hdr - Table that stores every transaction associated with the account
transaction_dtl - Table that stores commission details per transaction

Keys

transaction_mstr: id
transaction_map: id, mstr_id
transaction_hdr: id, mstr_id, map_id
transaction_dtl: id, mstr_id, map_id, hdr_id

Simple Query to illustrate the relationships:

SELECT *
FROM transaction_mstr a
     JOIN transaction_map b ON a.id = b.mstr_id
     JOIN transaction_hdr c ON b.id = c.map_id
     JOIN transaction_dtl d ON c.id = d.hdr_id;

Current keys

The ID column in each table is a primary key (unique auto increment value).
A single foreign key is created on each table relating referencing the parent primary key.

The Ask In it's current state, inserting and deleting commission from the hdr and dtl tables is very slow. I have asked the infrastructure team to ensure the environment is resourced appropriately, but I'd like to understand if a better key design would improve performance.

In addition, I have narrowed down the wait time to the actual insert and delete. The calculations run rather quickly (3-5 mins), but the inserts into the various tables take upwards of 30 minutes. The deletes take approximately 20 mins per commission period.

Create table / index scripts

CREATE TABLE [dbo].[transaction_dtl](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [mstr_id] [smallint] NULL,
    [map_id] [bigint] NULL,
    [hdr_id] [bigint] NULL,
    [trx_id] [bigint] NULL,
    [trx_parent_id] [int] NULL,
    [agent_id] [smallint] NULL,
    [rate_id] [smallint] NULL,
    [frequency_id] [tinyint] NULL,
    [period_days] [tinyint] NULL,
    [commission] [numeric](38, 6) NULL,
    [row_create_ts] [datetime] NULL,
 CONSTRAINT [PK_transaction_dtl] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[transaction_hdr](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [mstr_id] [smallint] NULL,
    [map_id] [bigint] NULL,
    [trx_id] [bigint] NULL,
    [trx_parent_id] [int] NULL,
    [trx_other_id] [int] NULL,
    [level] [tinyint] NULL,
    [days_held] [smallint] NULL,
    [units_bal] [numeric](38, 6) NULL,
    [reversal] [int] NULL,
    [sortkey] [varchar](300) NULL,
    [row_create_ts] [datetime] NULL,
 CONSTRAINT [PK_transaction_hdr] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[transaction_map](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [mstr_id] [smallint] NULL,
    [fund_id] [tinyint] NULL,
    [account_id] [int] NULL,
    [wholesaler_id] [int] NULL,
    [unbalanced] [bit] NULL,
    [nobalance] [bit] NULL,
    [row_create_ts] [datetime] NULL,
 CONSTRAINT [PK_transaction_map] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[transaction_mstr](
    [id] [smallint] IDENTITY(1,1) NOT NULL,
    [caller_sid] [varbinary](85) NOT NULL,
    [caller_name] [sysname] NOT NULL,
    [status] [tinyint] NOT NULL,
    [start_time] [datetimeoffset](7) NULL,
    [end_time] [datetimeoffset](7) NULL,
    [commission_type] [tinyint] NULL,
    [commission_date] [date] NULL,
    [run_date] [date] NULL,
    [frequency_id] [tinyint] NULL,
    [closed] [tinyint] NOT NULL,
    [row_create_ts] [datetime] NOT NULL,
    [row_modified_ts] [datetime] NULL,
 CONSTRAINT [PK_transaction_mstr] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[transaction_dtl] ADD  CONSTRAINT [DF_commission_dtl_row_create_ts]  DEFAULT (getdate()) FOR [row_create_ts]
GO
ALTER TABLE [dbo].[transaction_hdr] ADD  CONSTRAINT [DF_transaction_hdr_reversal]  DEFAULT ((0)) FOR [reversal]
GO
ALTER TABLE [dbo].[transaction_hdr] ADD  CONSTRAINT [DF_commission_hdr_row_create_ts]  DEFAULT (getdate()) FOR [row_create_ts]
GO
ALTER TABLE [dbo].[transaction_map] ADD  CONSTRAINT [DF_transaction_map_balanced]  DEFAULT ((0)) FOR [unbalanced]
GO
ALTER TABLE [dbo].[transaction_map] ADD  CONSTRAINT [DF_transaction_map_nobalance]  DEFAULT ((0)) FOR [nobalance]
GO
ALTER TABLE [dbo].[transaction_map] ADD  CONSTRAINT [DF_commission_map_row_create_ts]  DEFAULT (getdate()) FOR [row_create_ts]
GO
ALTER TABLE [dbo].[transaction_mstr] ADD  CONSTRAINT [DF_transaction_master_closed]  DEFAULT ((0)) FOR [closed]
GO
ALTER TABLE [dbo].[transaction_mstr] ADD  CONSTRAINT [DF_transaction_master_create_row_ts]  DEFAULT (getdate()) FOR [row_create_ts]
GO
ALTER TABLE [dbo].[transaction_dtl]  WITH CHECK ADD  CONSTRAINT [FK_transaction_dtl_hdr] FOREIGN KEY([hdr_id])
REFERENCES [dbo].[transaction_hdr] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[transaction_dtl] CHECK CONSTRAINT [FK_transaction_dtl_hdr]
GO
ALTER TABLE [dbo].[transaction_hdr]  WITH CHECK ADD  CONSTRAINT [FK_transaction_hdr_map] FOREIGN KEY([map_id])
REFERENCES [dbo].[transaction_map] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[transaction_hdr] CHECK CONSTRAINT [FK_transaction_hdr_map]
GO
ALTER TABLE [dbo].[transaction_map]  WITH CHECK ADD  CONSTRAINT [FK_transaction_map_mstr] FOREIGN KEY([mstr_id])
REFERENCES [dbo].[transaction_mstr] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[transaction_map] CHECK CONSTRAINT [FK_transaction_map_mstr]
GO

Cascading Deletes

I have been on the fence about using cascading deletes. I can easily enough script this, but thought it might be useful for referential integrity.

Updated Index Code (slowed performance significantly)

CREATE TABLE [dbo].[transaction_dtl](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [mstr_id] [int] NOT NULL,
    [map_id] [bigint] NOT NULL,
    [hdr_id] [bigint] NOT NULL,
    [trx_id] [bigint] NULL,
    [trx_parent_id] [int] NULL,
    [agent_id] [smallint] NULL,
    [rate_id] [smallint] NULL,
    [frequency_id] [tinyint] NULL,
    [period_days] [tinyint] NULL,
    [commission] [numeric](38, 6) NULL,
    [row_create_ts] [datetime] NULL,
 CONSTRAINT [PK_transaction_dtl] PRIMARY KEY CLUSTERED 
(
    [mstr_id] ASC,
    [map_id] ASC,
    [hdr_id] ASC,
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[transaction_hdr](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [mstr_id] [int] NOT NULL,
    [map_id] [bigint] NOT NULL,
    [trx_id] [bigint] NULL,
    [trx_parent_id] [int] NULL,
    [trx_other_id] [int] NULL,
    [level] [tinyint] NULL,
    [days_held] [smallint] NULL,
    [units_bal] [numeric](38, 6) NULL,
    [reversal] [int] NULL,
    [sortkey] [varchar](300) NULL,
    [row_create_ts] [datetime] NULL,
 CONSTRAINT [PK_transaction_hdr] PRIMARY KEY CLUSTERED 
(
    [mstr_id] ASC,
    [map_id] ASC,
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[transaction_map](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [mstr_id] [int] NOT NULL,
    [fund_id] [tinyint] NULL,
    [account_id] [int] NULL,
    [wholesaler_id] [int] NULL,
    [unbalanced] [bit] NULL,
    [nobalance] [bit] NULL,
    [row_create_ts] [datetime] NULL,
 CONSTRAINT [PK_transaction_map] PRIMARY KEY CLUSTERED 
(
    [mstr_id] ASC,
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[transaction_mstr](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [caller_sid] [varbinary](85) NOT NULL,
    [caller_name] [sysname] NOT NULL,
    [status] [tinyint] NOT NULL,
    [start_time] [datetimeoffset](7) NULL,
    [end_time] [datetimeoffset](7) NULL,
    [commission_type] [tinyint] NULL,
    [commission_date] [date] NULL,
    [run_date] [date] NULL,
    [frequency_id] [tinyint] NULL,
    [closed] [tinyint] NOT NULL,
    [row_create_ts] [datetime] NOT NULL,
    [row_modified_ts] [datetime] NULL,
 CONSTRAINT [PK_transaction_mstr] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[transaction_dtl] ADD  CONSTRAINT [DF_commission_dtl_row_create_ts]  DEFAULT (getdate()) FOR [row_create_ts]
GO
ALTER TABLE [dbo].[transaction_hdr] ADD  CONSTRAINT [DF_transaction_hdr_reversal]  DEFAULT ((0)) FOR [reversal]
GO
ALTER TABLE [dbo].[transaction_hdr] ADD  CONSTRAINT [DF_commission_hdr_row_create_ts]  DEFAULT (getdate()) FOR [row_create_ts]
GO
ALTER TABLE [dbo].[transaction_map] ADD  CONSTRAINT [DF_transaction_map_balanced]  DEFAULT ((0)) FOR [unbalanced]
GO
ALTER TABLE [dbo].[transaction_map] ADD  CONSTRAINT [DF_transaction_map_nobalance]  DEFAULT ((0)) FOR [nobalance]
GO
ALTER TABLE [dbo].[transaction_map] ADD  CONSTRAINT [DF_commission_map_row_create_ts]  DEFAULT (getdate()) FOR [row_create_ts]
GO
ALTER TABLE [dbo].[transaction_mstr] ADD  CONSTRAINT [DF_transaction_master_closed]  DEFAULT ((0)) FOR [closed]
GO
ALTER TABLE [dbo].[transaction_mstr] ADD  CONSTRAINT [DF_transaction_master_create_row_ts]  DEFAULT (getdate()) FOR [row_create_ts]
GO
ALTER TABLE [dbo].[transaction_dtl]  WITH CHECK ADD  CONSTRAINT [FK_transaction_dtl_transaction_hdr] FOREIGN KEY([mstr_id], [map_id], [hdr_id])
REFERENCES [dbo].[transaction_hdr] ([mstr_id], [map_id], [id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[transaction_dtl] CHECK CONSTRAINT [FK_transaction_dtl_transaction_hdr]
GO
ALTER TABLE [dbo].[transaction_hdr]  WITH CHECK ADD  CONSTRAINT [FK_transaction_hdr_transaction_map] FOREIGN KEY([mstr_id], [map_id])
REFERENCES [dbo].[transaction_map] ([mstr_id], [id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[transaction_hdr] CHECK CONSTRAINT [FK_transaction_hdr_transaction_map]
GO
ALTER TABLE [dbo].[transaction_map]  WITH CHECK ADD  CONSTRAINT [FK_transaction_map_transaction_mstr] FOREIGN KEY([mstr_id])
REFERENCES [dbo].[transaction_mstr] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[transaction_map] CHECK CONSTRAINT [FK_transaction_map_transaction_mstr]
GO
Improve this question
9
  • 1
    Please provide the CREATE TABLE statements and all CREATE INDEX statements. Also. Describe how you're doing the INSERTs. A system half that powerful should be able to add 1M rows to each table in <5m.
    – Michael Kutz
    Commented Nov 10, 2021 at 10:48
  • Agreed with Michael, your runtimes seem suspiciously slow. Knowing what resources your server is provisioned with would be helpful (feel free to update the post once you hear back from your infrastructure team), but that's not likely the issue. I'm addition to your Table and Index definitions (as Michael asked for), please provide the exact INSERT and DELETE statements you're using, as those will determine what the proper indexing should be (and may also provide a clue why your queries are running so slow). Even your calculations taking minutes on a table that size seems under-performant.
    – J.D.
    Commented Nov 10, 2021 at 13:04
  • @J.D. Will update with table and index scripts. The primary calculation is very complicated and required the use of a recursive query. I am confident in the query that drives the result sets, and the time it takes for those to complete. When I run the calculation scripts to screen("SELECT"), they complete in full in a few minutes. As soon as I "INSERT INTO X" table, I see 20-30 minute wait times.
    – warrenk
    Commented Nov 10, 2021 at 20:07
  • Well here's some perspective, recursion in SQL Server can be very performant. I've repeatedly ran recursive calculations on tables with ~1,000,000 rows in about 1 second total runtime, on modest hardware. To take minutes on the amount of data you're talking is either indicative of the same problem your DML queries are facing, or indicative of the way you architected your calculation queries possibly not being the most efficient relational implementation, such as using a cursor instead of a recursive CTE. Without knowing your calculation queries though, I can only assume it might be the former.
    – J.D.
    Commented Nov 11, 2021 at 0:19
  • @J.D. I’m using a recursive CTE. Again, this component runs with a reasonable duration and it’s not really the focus of the question. (Though, I’m sure there is opportunity to improve it)
    – warrenk
    Commented Nov 11, 2021 at 1:43

2 Answers 2

Reset to default
0

There's right way to store parent/child hierarchies.

Put the ID for each child table at the end, so each table has a Primary Key clustered index where the parent key is the leading columns. eg the primary keys should be:

transaction_mstr: id
transaction_map:  mstr_id, id
transaction_hdr:  mstr_id, map_id, id
transaction_dtl:  mstr_id, map_id, hdr_id, id

That way all the child rows for each parent are stored together, and there's an index that supports efficient lookup of a child from a parent, and prevents a full table scan when deleting a parent row.

But with compound keys, you need to join using the full foreign key. eg

SELECT *
FROM transaction_mstr a
     JOIN transaction_map b 
        ON b.mstr_id = a.id
     JOIN transaction_hdr c 
        ON  c.mstr_id = b.mstr_id
        AND c.map_id = c.id
     JOIN transaction_dtl d 
        ON d.mstr_id = c.mstr_id 
        AND d.map_id = c.map_id
        AND d.hdr_id = c.id
Improve this answer
16
  • In case map/hdr/dtl rows are going to be referred to directly, not via parent relationships, then you will want a non-clustered unique index on id for each too. In fact even without that requirement you possibly want a unique constraint/index on id anyway to guard against bugs breaking that bit of data integrity — so perhaps have id as a non-clustered primary key in each case and the compound keys as above as the clustering keys. This will increase storage requirements, as the NCI will contain the other columns from the CI too, but data integrity is usually far more important than that.
    – David Spillett
    Commented Nov 10, 2021 at 17:07
  • With the primary key's defined as above, would any foreign keys be required? (As per David Browne)
    – warrenk
    Commented Nov 10, 2021 at 20:01
  • Yes. Foreign Keys are still required, but you don’t need additional indexes to support them. As the PK plays that role too.
    – David Browne - Microsoft
    Commented Nov 10, 2021 at 20:40
  • @DavidBrowne-Microsoft Do you recommend the cascading deletes?
    – warrenk
    Commented Nov 10, 2021 at 21:47
  • 1
    @DavidBrowne-Microsoft thanks for the update, query performance improved! On another note, I realize I need to utilize partitioning to manage these datasets due to size. I’d like to keep the most recent 4 quarters on primary disk and archive / move older datasets to another file group on cheaper disk. The two tables I’d like to partition are transaction_hdr and transaction_dtl by mstr_id. Would doing this mean I’d have to adjust keys yet again?
    – warrenk
    Commented Nov 16, 2021 at 1:53
0

The simplest approach is to find your query in Query_Store and see the recommended index. Any way I can bet on the fact that you have triggers (probably for audit) on your tables or million of unused indexes. There is no over possible explanation for such timings.

Improve this answer
1
  • No triggers at all. With the indexes I mentioned in my first post already created, the query_store has no other recommended indexes.
    – warrenk
    Commented Nov 10, 2021 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.