Large sql table using last_value + over
First of all I am not a DBA and this post contains some ugly sql codes. :)
Context
I have a table representing all of my product actions (in SQL "TP_MOVI") from the beginning of the process to the end. After this line, one product (in SQL "CODIGO") can have a balance in several places (in SQL "ARMAZEM").
In a table, I have all the activities and my work summarizes them, for example, the balance of all products to the current date or a parameterized date. My logic was to get the last entry of all products + place using sentence LAST_VALUE() OVER()
), creating a balance of all places.
My table currently has over 1,000,000 records and it is causing some interrupts right now while I am trying to fetch data from this table and of course delay fetching the data. I'm pretty sure my SQL code is wrong and causing this problem, could you guys help me get this code better?
I read something about the statement WITH NO LOCK
, does this help?
Content - SQL Code
CREATE TABLE [MOVIMENTACOES](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DATA] [datetime] NULL,
[CODIGO] [varchar](20) NULL,
[ARMAZEM] [int] NULL,
[TP_MOVI] [varchar](10),
[QUANTIDADE] [float] NULL,
[SALDO] [float] NULL,
[ATV] [bit] NULL)
INSERT INTO [MOVIMENTACOES]([DATA],[CODIGO],[ARMAZEM],[TP_MOVI],[QUANTIDADE],[SALDO],[ATV])VALUES('2017-04-24 05:54:59.340','123456',18,'PROD',0,10,1)
INSERT INTO [MOVIMENTACOES]([DATA],[CODIGO],[ARMAZEM],[TP_MOVI],[QUANTIDADE],[SALDO],[ATV])VALUES('2017-04-24 05:54:59.340','123456',18,'PROD',10,15,1)
INSERT INTO [MOVIMENTACOES]([DATA],[CODIGO],[ARMAZEM],[TP_MOVI],[QUANTIDADE],[SALDO],[ATV])VALUES('2017-04-24 05:54:59.340','456789',19,'PROD',0,20,1)
INSERT INTO [MOVIMENTACOES]([DATA],[CODIGO],[ARMAZEM],[TP_MOVI],[QUANTIDADE],[SALDO],[ATV])VALUES('2017-04-24 05:54:59.340','456789',19,'PROD',20,15,1)
INSERT INTO [MOVIMENTACOES]([DATA],[CODIGO],[ARMAZEM],[TP_MOVI],[QUANTIDADE],[SALDO],[ATV])VALUES('2017-04-24 05:54:59.340','123456',28,'PROD',0,6,1)
INSERT INTO [MOVIMENTACOES]([DATA],[CODIGO],[ARMAZEM],[TP_MOVI],[QUANTIDADE],[SALDO],[ATV])VALUES('2017-04-24 05:54:59.340','123456',28,'SALE',6,-6,1)
SELECT
MOV.ID,
MOV.DATA, MOV.CODIGO, MOV.ARMAZEM, MOV.TP_MOVI,
MOV.SALDO,
MOV.QUANTIDADE,
MOV.SALDO + LAST_VALUE(MOV.QUANTIDADE) OVER(ORDER BY MOV.DATA ROWS UNBOUNDED PRECEDING) AS SALDO_ACUMULADO
FROM MOVIMENTACOES MOV
LEFT OUTER JOIN MOVIMENTACOES MOV2 ON
MOV2.CODIGO = MOV.CODIGO AND
MOV2.ARMAZEM = MOV.ARMAZEM AND
MOV2.ID > MOV.ID
AND MOV2.DATA <= '2017-04-25 07:00:00'
WHERE
MOV2.ID IS NULL
AND MOV.DATA <= '2017-04-25 07:00:00'
Here is a schematic and some sample data: http://rextester.com/XIXCB97220
source to share
Based on your comments and the expected result, it seems like you just need the last line (= the line with the highest ID) for each combination (CODIGO,ARMAZEM)
. And your calculation is SALDO_ACUMULADO
exactly the same as SALDO + QUANTIDADE
. This results in a simple ROW_NUMBER:
WITH cte AS
(
SELECT
MOV.ID,
MOV.DATA, MOV.CODIGO, MOV.ARMAZEM, MOV.TP_MOVI,
MOV.SALDO,
MOV.QUANTIDADE,
MOV.SALDO + MOV.QUANTIDADE AS SALDO_ACUMULADO,
ROW_NUMBER() OVER(PARTITION BY CODIGO, ARMAZEM ORDER BY MOV.ID DESC) AS rn
FROM MOVIMENTACOES MOV
WHERE DATA <= '2017-04-25 07:00:00'
)
SELECT *
FROM cte
WHERE rn = 1
source to share
A calculated column containing your calculated results can improve performance SELECT
.
You may need to check indexes and keys as well.
For example, I don't see any primary key, setting the column [ID]
as the primary key will help
It builds CLUSTERED INDEX
on it
(which will be used to mitigate performance issues with SELECT
).
source to share