0

We have a table that consists of 150 million records and the data goes back all the way to 2010. I'm considering splitting the table into two to improve performance - a history table with data prior to the last 5 years and the current table will be left with the last 5 years. I'm just planning on doing the following queries:

step 1:
select *
into userHistory
from user
where creationdate < dateadd(year,-5,getdate())

step 2:
delete from user where creationdate < dateadd(year,-5,getdate())

I'm also considering having a nightly job that will clean up records from the users' table and insert them into the userHistory table so that the users' table will only have 5 years' worth of data at any given time.

Is this the right approach to this problem or do I consider table partitioning? Are there best practices for this kind of scenario where the table size becomes really large?

Update:
I have two tables: users transactiondetails

users have transactions and these tables are joined by transactid. My stored procedure receives a parameter that either has one user id or multiple user ids. It has to sort all the user transactions by transactdate desc and return the top 10 rows.

if a user has 10,000 rows, my code has to go through all the 10,000 rows, sort them by date desc and retrieve the top 10. some of the users have rows dating back to 2010 and therefore my code has to look through all the transactions dating back to that period before sorting because of the way our tables are structured.

if OBJECT_ID('dbo.users') is not null
begin
    drop table dbo.users
end

if OBJECT_ID('dbo.transactDetails') is not null
begin
    drop table dbo.transactDetails
end

create table dbo.users
(
useridsurrogate int primary key identity(1,1),
userid int,
transactid uniqueidentifier,
created datetime
)

create nonclustered index idx_transactid on dbo.users(userid) include (transactid)

create table dbo.transactDetails
(
transactid uniqueidentifier primary key,
transactname varchar(100),
transactstatus varchar(100),
transactaddress varchar(100),
transactdate datetime
)

Go

set nocount on
declare @i int = 1, @j int = 1, @newid uniqueidentifier

while @i < 5
begin

set @j = 1
while @j <= 100
begin

set @newid = (select newid())

insert into dbo.users values (@i, @newid, getdate())
insert into dbo.transactDetails values (@newid, 'ABC'+convert(varchar(10),@i)+convert(varchar(10),@j), 'in progress', 'XYZ'+convert(varchar(10),@i)+convert(varchar(10),@j)+' dr',getdate())
set @j = @j + 1

end
set @i = @i + 1
end

Go

query 1:
select top 10 td.*
from dbo.transactDetails td
inner join dbo.users u 
on u.transactid = td.transactid
where u.userid = 1
order by td.transactdate desc

query 2:
select top 10 td.*
from dbo.users u
inner join dbo.transactDetails td
on u.transactid = td.transactid
where u.userid  In (1,2)
order by td.transactdate desc

execution plan for query 1: https://www.brentozar.com/pastetheplan/?id=S1VTDTC4j

execution plan for query 2: https://www.brentozar.com/pastetheplan/?id=Sk05v60Ni

I've provided execution plans for a smaller subset of data. There are cases where some users have half a million transactions. It's in those cases that the index scan takes a while because they have data going all the way back to 2010.

0

3 Answers 3

0

Your direct issue is that your queries are getting Clustered Index Scans, despite you only needing a small subset of the data (TOP 10). Even in cases where the user has half a million rows, that's relatively much smaller than the total cardinality of the table, 150 million rows, and should be resulting in index seeks. This is due to hitting the tipping point when the Engine reads the data for the dbo.transactDetails table.

This happens in your particular case based on the indexes you currently have in place. You're trying to select top 10 td.* ... order by td.transactdate desc but your only index is the default clustered index on transactid. This means the entire table / index needs to be sorted, as denoted in the Sort (TopN Sort) operator in your execution plans, so that the engine knows which top 10 rows to return.

If you created your clustered index on (transactid, transactdate) instead, then it will cover your query and persist the data sorted by transactdate (within the sorting of each transactid) already. This will likely eliminate your issue with the tipping point since the entire index won't need to be scanned anymore, and should even eliminate the Sort (TopN Sort) operator from your execution plan too. So one simple change may result in two performance benefits to your query.

Won't it make more sense to create a non-clustered index on (transactdate, transactid)?

I was torn on if I should mention this, but another issue with your code is the fact you're using SELECT * (really SELECT td.* but essentially the same thing), which is an anti-pattern for many reasons, one being performance. You should always explicitly list only the fields you need.

I am assuming since you consciously use the alias to say td.* that maybe you do need all fields from the transactDetails table. If that's true, then it would be silly to make a nonclustered index because it would require you to define it to include all of the fields from the transactDetails table, in order to be covering (since you're SELECTing all of the fields). This would essentially be a duplicate of your clustered index and double the size of your table. The clustered index is the table itself, sorted on the fields you define the index on, therefore it already includes all of the fields from the transactDetails table. This is why it would be easier and better just to modify the clustered index to be defined as (transactid, transactdate).

If you made a nonclustered index on just (transactdate, transactid) without including the other columns then the Engine would need to either do a bunch of key lookup operations on the clustered index anyway to find those other fields or it may decide that operation is too costly and you hit the tipping point again, which would result in a clustered index scan instead of the nonclustered index being used, bringing you back to square one.

If you do decide you don't need all of the fields from the transactDetails table, and only needed one or two more, then it may make more sense to create a separate nonclustered index on (transactdate, transactid) that also included those couple of additional fields.

2
  • Thanks for your time, J.D! Here's another solution I was thinking of. create table #tbltransact(id int identity primary key, transactId) insert into #tblTransact Select top transactId from tables order by transact date select td.* from #tblTransact join transactDetails table order by id The idea is to only order by having one column and get the required rows. Once I have all the rows, join back to the transactDetails table to get all the other columns ordered by the identity column. What do you think about this solution? Nov 8, 2022 at 13:58
  • @HanroRachel You'll still end up with a clustered index scan since you're ordering by transactDate, unless you create a nonclustered index on transactDate too, in addition to the proposed code change above. I was going to offer a way to re-write your query in a similar vein to make it faster, that's sometimes an applicable solution, but in this case I think the easiest solution is just modifying the clustered index to cover transactDate in addition to your primary key.
    – J.D.
    Nov 8, 2022 at 14:54
0

Plan Selection

You want to find the top 10 rows by transaction date descending, given a list of one or more users.

You already have an index to help find the users, with an included column to provide the join key to the detail table. You were expecting SQL Server to produce a plan that finds user records using your index, lookup matching rows in the detail table, then sort by date descending and return the top 10 rows.

Instead, the query optimizer chose the following plan:

merge join plan

This plan uses your index to locate the user rows, sorts them in transactid order, merge joins them with all rows from the detail table, then sorts the result returning the top 10 rows. The sort on transactid is necessary for the merge join algorithm, which requires inputs sorted on the joining column(s). The detail table can be scanned in transactid order without a sort because that matches the clustered index order.

The optimizer chose this plan because it appeared to be cheaper than the alternatives it considered. SQL Server has little idea of the actual capabilities of your hardware, so it uses a generic costing model. Most of the time, this cost model provides a reasonable basis for choosing between plan alternatives.

Why did it prefer the scan and merge join over the 'lookup' plan I described earlier? We can obtain that plan using a hint to override the optimizer's default behaviour:

select top 10 td.*
from dbo.transactDetails td 
    WITH (FORCESEEK) -- hint added
inner join dbo.users u 
on u.transactid = td.transactid
where u.userid = 1
order by td.transactdate desc

The plan is now:

FORCESEEK plan

It looks reasonable enough, but results in a separate seek into the clustered index on the details table for every row returned by the seek on the users table.

You mentioned that a single user might have as many as 500,000 rows in the users table. Even for many fewer rows than that, the estimated cost of many seeks quickly approaches the modelled cost of simply scanning the whole table.

If this seems counter-intuitive, remember that each seek has to navigate multiple levels of the clustered index b-tree. If the clustered index has five levels, that's 5 page accesses and a certain amount of CPU for a binary search at each level. Pages are not guaranteed to be in memory and might need to be brought in from disk. These page accesses are small reads (a single page) and so do not use read-ahead.

In addition, rows arriving from the seek on userid will not generally be in any particular order of the transactid values used to seek into the details table. SQL Server models this as mostly random access to the pages of the clustered index.

The scan alternative does not require a multi-level seek for every row returned. It can directly read whole pages of rows in clustered index order by following the next page pointers at the leaf level of the index. This results in many fewer page reads (logical or physical), less CPU usage, and potentially very efficient use of large read-ahead reads since the access is sequential (in index key order).

All these factors mean SQL Server assesses the merge join plan as lower cost, despite the extra sort. Depending on how much of the detail table is kept in memory and how fast your systems CPU is, you might find the plan with the FORCESEEK hint runs more quickly in practice. You might find it is slower. In any case, the above explains why SQL Server chose the plan it did.

Strategy 2

Another option is to look through rows from the details table in descending date order, until you have found ten matching records in the user table, given the restricted set of users you are interested in.

With this approach, you can stop looking for rows as soon as you've found ten because there can't possibly be any with a more recent date (given the order we're using).

To avoid a sort, you'll need an index on transaction date in the details table:

CREATE NONCLUSTERED INDEX [IX dbo.transactDetails transactdate-] 
ON dbo.transactDetails (transactdate DESC)
WITH (DATA_COMPRESSION = PAGE);

To make seeks into the users table efficient, we'll also promote the transactid column in your existing index from an include to a key:

CREATE NONCLUSTERED INDEX idx_transactid 
ON dbo.users (userid, transactid)
WITH (DROP_EXISTING = ON, DATA_COMPRESSION = PAGE);

As an optimization, we'll find just the primary key of the top 10 matching rows, then retrieve the full set of output columns for just those 10 rows using a separate join. The code to do this with the index changes above is:

WITH 
    Keys AS
    (
        -- At most ten rows
        SELECT
            TD.transactid,
            TD.transactdate
        FROM dbo.transactDetails AS TD
        WHERE
            EXISTS
            (
                SELECT * 
                FROM dbo.users AS U
                WHERE
                    U.transactid = TD.transactid
                    -- user list goes here
                    AND U.userid IN (1, 2)
            )
        ORDER BY
            TD.transactdate DESC
            OFFSET 0 ROWS
            FETCH FIRST 10 ROWS ONLY
    )
-- Fetch the full column set just for
-- the matching rows
SELECT 
    TD2.* 
FROM Keys AS K
JOIN dbo.transactDetails AS TD2
    ON TD2.transactid = K.transactid
ORDER BY
    K.transactdate DESC;

The execution plan is:

Key seek plan

The plan scans the compact new transaction date index in descending key order and checks for matching users using the adjusted index. The first ten matching rows have their full column set retrieved from the clustered index, and the query ends.

This might be a very efficient plan for your purposes, if you only ever search for users that exist and the rows you find have relatively recent transaction dates.

The worst case occurs when you search for a user that doesn't exist in the users table. The whole transaction date index will be scanned (though it will be quite small compared with the full table). More importantly, every row will seek into the users table and not find a match. You can expect those 150 million seeks to take a while.

This is therefore a high-risk option, unless you can guarantee the query will only ever be run for users that exist and have relatively recent transaction dates. If you can guarantee that, this approach will work well.

Redesign

Since you are already considering splitting the table up, you might as well think about fixing the basic table design.

The current arrangement is very strange and not properly normalized. There are a number of probably unnecessary surrogate columns and the transactid column has no business being present on a table named 'users'.

Consider the following:

CREATE TABLE dbo.Users
(
    UserId integer NOT NULL PRIMARY KEY,
    CreatedDt datetime NOT NULL
);

CREATE TABLE dbo.Transactions
(
    UserId integer NOT NULL
        CONSTRAINT [FK dbo.Transactions UserId -> dbo.Users]
            FOREIGN KEY (UserId)
            REFERENCES Users (UserId),
    TranId uniqueidentifier NOT NULL,
    TranName varchar(100) NOT NULL,
    TranStatus varchar(100) NOT NULL,
    TranAddress varchar(100) NOT NULL,
    TranDate datetime NOT NULL,

    CONSTRAINT [PK dbo.Transactions TranId]
        PRIMARY KEY NONCLUSTERED (TranId),
    INDEX [CUQ dbo.Transactions UserId, TranDate] 
        UNIQUE CLUSTERED (UserId, TranDate)
);

Sample data matching that provided with the question, adjusted to fit the new structure:

INSERT dbo.Users 
    (UserId, CreatedDt)
SELECT 
    SV.number, GETUTCDATE()
FROM master.dbo.spt_values AS SV
WHERE 
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 4;

INSERT dbo.Transactions
(
    UserId, 
    TranId, 
    TranName, 
    TranStatus, 
    TranAddress, 
    TranDate
)
SELECT
    U.UserId,
    TranId = NEWID(),
    TranName = 
        CONCAT
        (
            'ABC', 
            U.UserId, 
            SV.number
        ),
    TranStatus = 'in progress',
    TranAddress = 
        CONCAT
        (
            'XYZ', 
            U.UserId, 
            SV.number, 
            ' dr'
        ),
    TranDate = 
        DATEADD
        (
            MILLISECOND, 
            10 * ROW_NUMBER() OVER (ORDER BY U.UserId), 
            GETUTCDATE()
        )
FROM dbo.Users AS U
CROSS JOIN master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 100;

We can now write a query that efficiently finds the users we are interested in, and their 10 most recent transactions. The result is at most ten rows per user, which can then be sorted in descending date order and the ten required rows returned:

SELECT
    U10.*
FROM dbo.Users AS U
CROSS APPLY 
(
    -- Top 10 rows for each requested user
    SELECT 
        T.*
    FROM dbo.Transactions AS T
    WHERE
        T.UserId = U.UserId
    ORDER BY 
        T.TranDate DESC
            OFFSET 0 ROWS
            FETCH NEXT 10 ROWS ONLY
) AS U10
WHERE
    -- User list goes here
    U.UserId IN (1, 2)
ORDER BY
    U10.TranDate DESC
        OFFSET 0 ROWS
        FETCH NEXT 10 ROWS ONLY;

The execution plan is:

New design

This will be very fast, regardless of the number of rows in the tables, and scales well with the number of users searched for.

-2

one more alternative is to separate your table into an NDF file. This way you separate your main table from the others and add it to a separate disk from the others.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/add-data-or-log-files-to-a-database?view=sql-server-ver16

1
  • 2
    It's doubtful that this would solve the OP's actual issue. Additionally, given most current storage architectures, it's likely that all storage goes to the same place eventually, no matter how it's presented to the server. Nov 7, 2022 at 19:22

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.