3

I have a query where OPTION(NO_PERFORMANCE_SPOOL) changes the resultset. I understood that hints affected the physical operations, not the values returned. I'd like help understanding why this is happening.

The context is generating dummy demo data. The schema is a typical Users -< Orders -< OrderItems >- Products setup. I only need Users and Orders to reproduce the phenomenon. Here they are, cut to what's needed.

create table dbo.Users(
    UserId      int identity(1, 1)  NOT NULL,
    UserName    varchar(50)         NOT NULL,
    
    constraint pk_Users
        primary key clustered (UserId),
        
    constraint uq_Users_UserName
        unique (UserName)
);

create table dbo.Orders(
    OrderId     int identity(1, 1)  NOT NULL,
    UserId      int                 NOT NULL,
    
    constraint pk_Orders
        primary key clustered(OrderId),
        
    constraint fk_Orders_Users
        foreign key (UserId)
        references dbo.Users(UserId)
);

Users is populated with 1,000 rows. I would like each user to have a random number of orders. My idea is to read Users for the foreign key, use an APPLY to return multiple rows per user, and generate a random number within the APPLY to control how many order rows there will be per user.

A search delivers a method of generating a new random number per row. I try this to check.

select
    u.UserId,
    o.Random
from dbo.Users u
outer apply(
    select ABS(CHECKSUM(NEWID())) % 100 Random
) o
order by u.UserId;

This gives the expected output

UserId  Random
1       29
2       92
3       4
4       95
etc.

Happy days. So I flesh it out and check the distribution of orders per user.

truncate table dbo.Orders;

insert dbo.Orders(
    UserId
)
select
    u.UserId
from dbo.Users u
outer apply(
    select top(1 + ABS(CHECKSUM(NEWID())) % 100)  -- 1 to 100 Orders per User
        1 dummy
    from dbo.Numbers
) o;

select distinct
    num_users = COUNT_BIG(*) over(partition by COUNT_BIG(*)),
    how_many_orders = COUNT_BIG(*)
from dbo.Orders o
group by o.UserId
order by num_users;

--Results
num_users  how_many_orders
1000       91

Oh dear. All users have the same number of orders. The plan shows a Row Count Spool (Lazy Spool). I realise my error - there is no correlation between the outer and inner branch. Fair enough. So I add OPTION(NO_PERFORMANCE_SPOOL) (for science!) and get the output I wanted.

select
    u.UserId
from dbo.Users u
outer apply(
    select top(1 + ABS(CHECKSUM(NEWID())) % 100)  -- 1 to 100 Orders per User
        1 dummy
    from master..spt_values
) o
OPTION(NO_PERFORMANCE_SPOOL);

num_users  how_many_orders
4          61
4          84
5          11
5          38
6          3
etc.

But now the row count is different. Before it was a whole number of thousands (1,000 users * the random order count). Now the order count differs from user to user so the total number of orders is no longer a multiple of 1,000. It seems to me that this is a problem. A hint should not change the results returned, only the physical implementation. What am I missing here, why is this behaviour OK?

I've run on Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) and also on SQL Server 2017 (RTM-CU31-GDR) (KB5021126) - 14.0.3460.9 (X64) with the same results.

For the record, a non-hinted solution is to reference the outer table in the inner and the inner in the results.

select
    o.UserId            -- taken from the inner query
from dbo.Users u
outer apply(
    select top(1 + ABS(CHECKSUM(NEWID())) % 100)  -- 1 to 100 Orders per User
        u.UserId        --references the outer query
    from dbo.Numbers
) o;

Fiddle: https://dbfiddle.uk/8q73cTLb

0

0

Browse other questions tagged or ask your own question.