Large sql table using last_value+over

Question

First of all, I'm not a DBA and this post my contain some ugly sql code. :)

Context

I have a table that represents all my product stock activities (in the SQL "TP_MOVI"), since the beginning of the process through the end of it. Following this line, one product (in the SQL "CODIGO") may have balance in multiple places (in the SQL "ARMAZEM").

In the table, I have all types of activities and my job is summarize it, for example, a balance of all products until the current date or a parametrized date. My logic about that was to get the last record of all products+place by using the clause LAST_VALUE() OVER()), generating a balance of all the places.

My table currently has over than 1 000 000 records, and right now, it causes some interruptions while I'm trying to retrieve data from this table and, of course, delaying on retrieving the data. I'm pretty sure my SQL code is incorrect and it's causing this problem, could you guys help me out to get this code better?

I've been reading something about the statement WITH NO LOCK, will it 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 are the schema and some example of the data: http://rextester.com/XIXCB97220

Answer 1

Based on your comments & expected result it seems like you simply want the latest row (= row with highest ID) for each (CODIGO,ARMAZEM) combination. And your SALDO_ACUMULADO calculation is exactly the same as SALDO + QUANTIDADE. This translates to 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