1

I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn't able to find much information about I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn't able to find much information about it, so I've decided I'll put it here.

So, in a nutshell, I'm facing terrible performance when accessing internal tables (inserted and deleted) in the trigger defined on partitioned table.

To test the issue I've created some simple tables, exactly the same, but one is partitioned and another is not:

create table [dbo].[Test1](
    [part_id] [int] not null,
    [id] [int] not null,
    [cost] [float] null,
    constraint [pk__Test1] primary key clustered ([part_id] asc, [id] asc) on ps_part(part_id)
);

create table [dbo].[Test2](
    [part_id] [int] not null,
    [id] [int] not null,
    [cost] [float] null,
    constraint [pk__Test1] primary key clustered ([part_id] asc, [id] asc)
);

Then I've filled the tables with some data.I don't have a data generation script right now, I've just used some local data, but there're about 700 different partitions and around 30M rows in these tables.

Then I've just tested how fast updates on these tables are, with very simple queries like

update dbo.Test1 set cost = cost + 0.1 where part_id = ??;
update dbo.Test1 set cost = cost - 0.1 where part_id = ??;

update dbo.Test2 set cost = cost + 0.1 where part_id = ??;
update dbo.Test2 set cost = cost - 0.1 where part_id = ??;

And results were logical - average time of update for partitioned table was around 2 seconds, and average time of update for non-partitioned table was around 4 seconds.

Then I've created simple triggers on both tables

alter trigger [dbo].[Test1__changed] on [dbo].[Test1]
after insert,update,delete
as 
begin
    set nocount on;

    select a.part_id
    into #temp11111111
    from (
        select r.part_id from inserted as r
        union
        select r.part_id from deleted as r
    ) as a;
end

After that I've tried the same test query, and results were really strange - on partitioned table it took on average 3 minutes for the query to finish, while on non-partitioned table the timing was similar to what it was without the triggers - about 4 seconds.

So my question is - does anyone know why this might happen? Any way to workaround this problem?

2
  • 1
    Unlikely to be the issue, but UNION is known for its poor performance. So unless you specifically need its de-duplication function, use UNION ALL.
    – Dale K
    Jul 5, 2022 at 19:53
  • @DanGuzman Then this question should be migrated, and then closed on Database Administrators as a dupe. Jul 5, 2022 at 21:30

0