SQL Server - Ayuda con este scrip

Hola foristas
Hace algun tiempo encontre este codigo posteado por Liliana
Isaias , tambien posteo uno igual

el srip es muy bueno
con 500 registros no hay problema pero con 9000 registros la consulta se hace lenta
1 minuto 20 segundos
es esto normal , o se puede mejorar la velocidad

Ident = es un campo identity

Select A.Ident ,Sum ( B.Entradas - B.Salidas ) + Acumulado
From
Tabla As A
Left Join
Tabla As B
On
(A.Ident = B.Ident )
or
( A.Ident > B.Ident )
Group By
A.CIdent

Saludos

¿Tienes indices declarados en tus tablas?

Hola Isaias , gracias por responder
veras le agrege una llave primaria , y un indice en la tabla
pero los tiempos de respuesta solo mejoran un poco
te mando el plan de ejecucion , talvez eso pueda ayudar

Saludos

Alter Table Tabla
Add Constraint PK_tabla primary key (Ident)

CREATE INDEX IX_Tabla
ON Tabla
(Ident)

SET SHOWPLAN_TEXT ON
go
----------------------------------------------------------------------
Select A.Ident ,Sum ( B.Entrada - B.Salida ) As Acumulado
From
Tabla As A
Left Join
Tabla As B
On
(A.Ident = B.Ident )
or
( A.Ident > B.Ident )
Group By
A.Ident
go
----------------------------------------------------------------------
SET SHOWPLAN_TEXT OFF
go


/*
01 minuto 29 segundos sin llaves ni indices

con llave primaria
1 minuto 8 segundos

mas un Indice 1 minuto 11 segundos

*/

Este es el plan de ejecucion al ejecutar el scrip
despues de crear llave primaria y un indice
no se pq razon aplica un sort

|--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1016]=0) then NULL else [Expr1017]))
|--Stream Aggregate(GROUP BY:([A].[Ident]) DEFINE:([Expr1016]=COUNT_BIG([B].[Entrada]-[B].[Salida]), [Expr1017]=SUM([B].[Entrada]-[B].[Salida])))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([A].[Ident]))
|--Index Scan(OBJECT:([BD_1].[dbo].[Tabla].[IX_Tabla] AS [A]), ORDERED FORWARD)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))
|--Merge Interval
| |--Sort(TOP 2, ORDER BY:([Expr1013] DESC, [Expr1014] ASC, [Expr1010] ASC, [Expr1015] DESC))
| |--Compute Scalar(DEFINE:([Expr1013]=4&[Expr1012]=4 AND NULL=[Expr1010], [Expr1014]=4&[Expr1012], [Expr1015]=16&[Expr1012]))
| |--Concatenation
| |--Compute Scalar(DEFINE:([A].[Ident]=[A].[Ident], [A].[Ident]=[A].[Ident], [Expr1009]=62))
| | |--Constant Scan
| |--Compute Scalar(DEFINE:([Expr1010]=NULL, [A].[Ident]=[A].[Ident], [Expr1012]=10))
| |--Constant Scan
|--Clustered Index Seek(OBJECT:([BD_1].[dbo].[Tabla].[PK_tabla] AS [B]), SEEK:([B].[Ident] > [Expr1010] AND [B].[Ident] < [Expr1011]) ORDERED FORWARD)

-----------------------------------------------------------------------------------
Plan , con la tabla solo con indice , sin llave primaria
El plan de ejecucion usando solo indice es mas simple pero el tiempo de demora es parecido
1 minuto 20 segundos
-----------------------------------------------------------------------------------

|--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1007]=0) then NULL else [Expr1008]))
|--Stream Aggregate(GROUP BY:([A].[Ident]) DEFINE:([Expr1007]=COUNT_BIG([B].[Entrada]-[B].[Salida]), [Expr1008]=SUM([B].[Entrada]-[B].[Salida])))
|--Nested Loops(Left Outer Join, WHERE:([A].[Ident]=[B].[Ident] OR [A].[Ident]>[B].[Ident]))
|--Index Scan(OBJECT:([BD_1].[dbo].[Tabla].[IX_Tabla] AS [A]), ORDERED FORWARD)
|--Table Spool
|--Table Scan(OBJECT:([BD_1].[dbo].[Tabla] AS [B]))

Saludos

Mira, creale un INDICE, pero CLUSTER

CREATE CLUSTERED INDEX myIndex
ON table (Ident)

Y seteas

SET STATISTICS IO ON

Me mandas el resultado

Hola ,otra vez Isaias
hice lo q dijiste cree el indice y cluster y
SET STATISTICS IO ON

pero la consulta sigue tardando 1 minuto 8 segundos
o talvez hay algo mal escrito en la consulta ,
cuanto deberia ser el tiempo normal para esta consulta
originalmente la tabla q te doy como referencia era una variable tipo tabla q se creaba en un sp , pero en vista a q esta parte del scrip se demoraba tanto , decidide hacer la consulta en base a una tabla real pero , como ves los problemas siguen , el tiempo de demora es el mismo

W XPspack 2
AMD Athlon (tm) Xp 2400+
2.00 Ghz ,224 Mb de Ram

Saludos , y gracias por la ayuda me estas prestando

---------------------------------------------------------
--los 3 principales campos de la tabla

CREATE TABLE [Tabla] (
[Ident] [int] IDENTITY (1, 1) NOT NULL ,
[Entrada] [decimal](18, 2) NULL ,
[Salida] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
---------------------------------------------------------

|--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1016]=0) then NULL else [Expr1017]))
|--Stream Aggregate(GROUP BY:([A].[Ident]) DEFINE:([Expr1016]=COUNT_BIG([B].[Entrada]-[B].[Salida]), [Expr1017]=SUM([B].[Entrada]-[B].[Salida])))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([A].[Ident]))
|--Clustered Index Scan(OBJECT:([BD_1].[dbo].[Tabla].[my_index] AS [A]), ORDERED FORWARD)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))
|--Merge Interval
| |--Sort(TOP 2, ORDER BY:([Expr1013] DESC, [Expr1014] ASC, [Expr1010] ASC, [Expr1015] DESC))
| |--Compute Scalar(DEFINE:([Expr1013]=4&[Expr1012]=4 AND NULL=[Expr1010], [Expr1014]=4&[Expr1012], [Expr1015]=16&[Expr1012]))
| |--Concatenation
| |--Compute Scalar(DEFINE:([A].[Ident]=[A].[Ident], [A].[Ident]=[A].[Ident], [Expr1009]=62))
| | |--Constant Scan
| |--Compute Scalar(DEFINE:([Expr1010]=NULL, [A].[Ident]=[A].[Ident], [Expr1012]=10))
| |--Constant Scan
|--Clustered Index Seek(OBJECT:([BD_1].[dbo].[Tabla].[my_index] AS [B]), SEEK:([B].[Ident] > [Expr1010] AND [B].[Ident] < [Expr1011]) ORDERED FORWARD)

Hola Isaias espero no estar quitandote mucho de tu tiempo ,pero le hice una leve modificacion al query y el plan de ejecucion se acorto
el tiempo de demora sigue siendo 1 minuton 2 segundos

el stream agreagte 34 %
el nested loop ocupa 50 %
el clustered index scan 0 %
el clusterer idex seek 16 %

Saludos

Select A.Ident ,Sum ( B.Entrada - B.Salida ) As Acumulado
From
Tabla As A
Left Join
Tabla As B
On
( A.Ident >= B.Ident)
Group By
A.Ident

|--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1007]=0) then NULL else [Expr1008]))
|--Stream Aggregate(GROUP BY:([A].[Ident]) DEFINE:([Expr1007]=COUNT_BIG([B].[Entrada]-[B].[Salida]), [Expr1008]=SUM([B].[Entrada]-[B].[Salida])))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([A].[Ident]))
|--Clustered Index Scan(OBJECT:([BD_1].[dbo].[Tabla].[my_index] AS [A]), ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([BD_1].[dbo].[Tabla].[my_index] AS [B]), SEEK:([B].[Ident] <= [A].[Ident]) ORDERED FORWARD)

¿De acuerdo a tu tabla, como sabes cuando son ENTRADAS y cuando SALIDAS?, como es que la distingues?

Hola Isaias ,

He esta tabla previamente se ha llenado con datos de otras tablas
y tiene la stg forma

Ident Entrada Salida
1 15 0
2 5 0
3 0 10
4 0 4

ejecute el comando de nuevo
y estos son los resultados del analizador de conultas
parece q lo reas son altos en cada parte del query
las estadisticas estan hechas con la ultima consulta q postee

SET STATISTICS IO ON

Select A.CIdent ,Sum ( B.Entrada - B.Salida ) As Acumulado
From
Ta As A
Left Join
Ta As B
On
( A.CIdent >= B.CIdent)
Group By
A.CIdent

SELECT
Recuento Estimado de Filas 9078
Costo Estimado de Subarbol 341
Costo 0 %

COMPUTE SCALAR
Recuento Estimado de Filas 9078
Tamaño Estimado de Filas 28
Costo de E/S Estimado 0
Costo de CPU Estimado 115
Numero Estimado de ejecuciones 1.0
Costo Estimado de Subarbol 341

Costo 0 %

COMPUTE SCALAR (Stream agregate/Agregate)
Recuento Estimado de Filas 9078
Tamaño Estimado de Filas 28
Costo de E/S Estimado 0
Costo de CPU Estimado 115
Numero Estimado de ejecuciones 1.0
Costo Estimado 116.00 (34%)
Costo Estimado de Subarbol 341

NESTED LOOPS /LEFT OUTER JOIN
Recuento Estimado de Filas 4
Tamaño Estimado de Filas 73
Costo de E/S Estimado 0
Costo de CPU Estimado 172
Numero Estimado de ejecuciones 1.0
Costo Estimado 172.00 (50%)
Costo Estimado de Subarbol 225
|
|
|
--------
|-------CLUSTER INDEX SCAN (MY_INDEX)
| Recuento Estimado de Filas 9077
| Tamaño Estimado de Filas 32
| Costo de E/S Estimado 0
| Costo de CPU Estimado 0
| Numero Estimado de ejecuciones 1.0
| Costo Estimado de Subarbol 0

| Costo 0 %

|-------CLUSTER INDEX SEEK (MY_INDEX)
| Recuento Estimado de Filas 4538
| Tamaño Estimado de Filas 50
| Costo de E/S Estimado 5
| Costo de CPU Estimado 5
| Numero Estimado de ejecuciones 9077
| Costo Estimado 53.00 (16%)
| Costo Estimado de Subarbol 53


Saludos

¿Que fue lo que detectaste con el SET STATISTICS IO ON?, donde se hacen los mayores READ????

Hola Isaias ,

Esta tabla previamente se ha llenado con datos de otras tablas
y tiene la stg forma

Ident Entrada Salida
1 15 0
2 5 0
3 0 10
4 0 4

ejecute el comando de nuevo
y estos son los resultados del analizador de consultas
parece q lo reads son altos en cada parte del query
las estadisticas estan hechas con la ultima consulta q postee

Saludos

SET STATISTICS IO ON

Select A.Ident ,Sum ( B.Entrada - B.Salida ) As Acumulado
From
Tabla As A
Left Join
Tabla As B
On
( A.Ident >= B.Ident)
Group By
A.Ident

SELECT
Recuento Estimado de Filas 9078
Costo Estimado de Subarbol 341
Costo 0 %

COMPUTE SCALAR
Recuento Estimado de Filas 9078
Tamaño Estimado de Filas 28
Costo de E/S Estimado 0
Costo de CPU Estimado 115
Numero Estimado de ejecuciones 1.0
Costo Estimado de Subarbol 341

Costo 0 %

COMPUTE SCALAR (Stream agregate/Agregate)
Recuento Estimado de Filas 9078
Tamaño Estimado de Filas 28
Costo de E/S Estimado 0
Costo de CPU Estimado 115
Numero Estimado de ejecuciones 1.0
Costo Estimado 116.00 (34%)
Costo Estimado de Subarbol 341

NESTED LOOPS /LEFT OUTER JOIN
Recuento Estimado de Filas 4
Tamaño Estimado de Filas 73
Costo de E/S Estimado 0
Costo de CPU Estimado 172
Numero Estimado de ejecuciones 1.0
Costo Estimado 172.00 (50%)
Costo Estimado de Subarbol 225
|
|
|
--------
|-------CLUSTER INDEX SCAN (MY_INDEX)
| Recuento Estimado de Filas 9077
| Tamaño Estimado de Filas 32
| Costo de E/S Estimado 0
| Costo de CPU Estimado 0
| Numero Estimado de ejecuciones 1.0
| Costo Estimado de Subarbol 0

| Costo 0 %

|-------CLUSTER INDEX SEEK (MY_INDEX)
| Recuento Estimado de Filas 4538
| Tamaño Estimado de Filas 50
| Costo de E/S Estimado 5
| Costo de CPU Estimado 5
| Numero Estimado de ejecuciones 9077
| Costo Estimado 53.00 (16%)
| Costo Estimado de Subarbol 53

Dime si funciona:

SELECT a.Ident, (SUM(Entrada)- x.Salida) as Saldo
FROM Tabla a JOIN (SELECT Ident, SUM(Salida) FROM Tabla GROUP BY Ident) as x
ON a.Ident = x.Ident
GROUP BY a.Ident

Hola una vez mas Isaias
probe el scrip q publicaste

SELECT a.Ident, (SUM(Entrada)- x.Salida) as Saldo
FROM Tabla a JOIN (SELECT Ident, SUM(Salida) As Salida FROM Tabla GROUP BY Ident) as x
ON a.Ident = x.Ident
GROUP BY a.Ident

/*
Obtube este error
No se ha especificado ninguna columna para la columna 2 de 'x'.

Supongo q es pq le faltaba el Alias a la columna Sum (Salida) de la tabla x
le agrege el alias pero ahora obtengo el error

Servidor: mensaje 8120, nivel 16, estado 1, línea 3
La columna 'x.Salida' de la lista de selección no es válida, porque no está contenida en una función de agregado ni en la cláusula GROUP BY.
Debe ser por q no esta en el grup by ,lo puse en el group by
pero no se obtiene el valor acumulado

*/

Saludos ,

Cambiemos entonces la sentencia:

SELECT a.Ident, (SUM(Entrada)- x.Salida) as Saldo
FROM Tabla a JOIN (SELECT Ident, SUM(Salida) As Salida FROM Tabla GROUP BY Ident) as x
ON a.Ident = x.Ident
GROUP BY a.Ident , x.Salida

Hola Isaias
gracias por tu ayuda
estos son los resultados

SELECT a.Ident, (SUM(Entrada)- x.Salida) as Saldo
FROM Tabla a JOIN (SELECT Ident, SUM(Salida) As Salida FROM Tabla GROUP BY Ident) as x
ON a.Ident = x.Ident
GROUP BY a.Ident , x.Salida
Order By a.Ident

declare @ent decimal (18,2) ,@sal decimal (18,2)
Set @ent = (select sum (Entrada) from Tabla)
Set @sal = (select sum (Salida) from Tabla)

--No esta acumulando pq este deberia ser el resultado final

Select @ent As Entrada , @sal As Salida , @ent - @sal As Saldo
--687037.34 526854.99 160182.35
/*
Saldo Final
160182.35
*/

Si ordeno la consulta lo q me esta mostrand es la resta entre
el campo Entrada - salida sin acumular

Ident Entrada Salida *Resultado_de_scrip
1 15 0 15
2 5 0 5
3 0 10 -10
4 0 4 -4

Saludos
,

Pues esta haciendo lo que se le pido, ¿Que deseas obtener?, algo asi como que cada linea lleve un SALDO ACUMULADO????

¿Podrias darme un ejemplo de como quieres obtener la informacion?

Hola Isaias

Este es un ejemplo de la tabla

Ident Entrada Salida
1 15 0
2 5 0
3 0 10
4 0 4

El objetivo del scrip inicial es obtener un campo calculado
( Entrada - Salida ) + El Acumulado del Registro Anterior
por decirlo de alguna manera

Entro 15 Acumulado 15
Entro 5 Acumulado 20
Salio 10 Acumulado 10
Salio 4 Acumulado 6

por lo tanto el scrip deberia quedar asi
*le faltaria el order by Ident al final del scrip

Ident Acumulado
1 15
2 20
3 10
4 6

Con el scrip q me pasaste estoy obteniendo esto
Solo esta mostrando la resta entre Entrada - Salida

Ident Acumulado
1 15
2 5
3 -10
4 -4

Saludos , una vez mas gracias por tu tiempo
-----------------------------------------------------

Con esta explicacion, nos hubieramos ahorrado algunas postas, ajusta este codigo:

id Entrada Salida Saldo
1 10 0 10
2 0 5 5
3 1 0 6
4 1 1 6


-- Codigo
select
a.id,
a.entrada,
a.salida,
(
select sum(b.entrada - b.salida)
from dbo.t1 as b
where b.id <= a.id
) as saldo
from dbo.t1 as a
order by a.id