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:
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:
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:
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:
This will be very fast, regardless of the number of rows in the tables, and scales well with the number of users searched for.
