1
\$\begingroup\$

This is a ledger kind of table, initial amount is when the movement is null and all transactions on it is based on the Waste_Seq

the query take the last entry (id) based on the movement and change the initial amount

this query seem to work for my scenario and seem fast, is there a better or alternative way of doing what the query does?

long term this table will have more than 5-10 millions rows easily, this is a new empty table for now


Schema and data:

CREATE TABLE [dbo].[fredou](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Movement] [int] NULL,
    [Waste_Seq] [int] NOT NULL,
    [Amount] [int] NOT NULL,
    [operation] [int] NOT NULL,
 CONSTRAINT [PK_fun_fred_test] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NonClusteredIndex] ON [dbo].[fredou]
(
    [Waste_Seq] ASC,
    [Movement] ASC,
    [Id] DESC
)
INCLUDE (   [Amount],
    [operation]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

insert into fredou values (         null,         1,      1000,         1)
insert into fredou values (         1,         1,       500,        -1)
insert into fredou values (         1,         1,       450,        -1)
insert into fredou values (         1,         1,       475,        -1)
insert into fredou values (         1,         1,       475,        -1)
insert into fredou values (         2,         1,       300,         1)
insert into fredou values (         2,         1,       200,         1)
insert into fredou values (         2,         1,       100,         1)
insert into fredou values (         3,         1,       300,         0)
insert into fredou values (         3,         1,       350,         0)
insert into fredou values (         4,         1,        50,        -1)
insert into fredou values (         4,         1,        50,         0)
insert into fredou values (         null,         2,      5000,         1)
insert into fredou values (        50,         2,      1000,        -1)
insert into fredou values (        50,         2,      1000,         0)
insert into fredou values (        51,         2,       500,        -1)
insert into fredou values (        53,         2,       400,        -1)
insert into fredou values (        51,         2,       300,        -1);

Query:

DECLARE @SEQ int = 1

SELECT SUM(f.Amount * f.Operation) as Amount
FROM 
(
    SELECT  FIRST_VALUE(a.Amount)       OVER (partition BY a.Movement order by a.id desc) as Amount,
            FIRST_VALUE(a.Operation)    OVER (partition BY a.Movement order by a.id desc) as Operation,
            ROW_NUMBER()                OVER (partition BY a.Movement order by a.id desc) as rownum
    FROM [dbo].[fredou] a
    WHERE a.Waste_Seq = @SEQ 
) f
where f.rownum = 1;

Query plan

Link to sql fiddle

\$\endgroup\$
  • \$\begingroup\$ As @paparazzo said, it's the same PARTITION BY and the same ORDER BY for FIRST_VALUE and ROW_NUMBER. Thus no need for FIRST_VALUE and a simplified plan, too. \$\endgroup\$ – dnoeth May 19 '18 at 10:42
2
\$\begingroup\$

I doubt FIRST_VALUE is necessary since you have where f.rownum = 1;

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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