Hi i need Ideas for making faster this store procedure, first Let me
tell you the scenario:
I have 2 tables, origin(i lookup and calculate information) and
destiny(insert or update the calculated information), this are the
details:
ORIGIN (CSV)
INDEX: UNIQUE NONCLUSTERED
PK_CSV(PMM_DATETIME,MINid,Sesion,StbUsed,HAE,PDE)
DESTINY (UB_TOTGRALBYSIDNID)
INDEX: UNIQUE NONCLUSTERED PK_UB_TOTGRALBYSIDNID(PMM_DATETIME, SID,
NID)
the origin table has 2,242.013 rows (since January) and the destiny
table has 7,488 rows, this is just April 01 (00:00-23:59) so the
problem is my
sp PR_POP_UB_TOTGRALBYSIDNID it takes at least 3 hours running, please
give me ideas to decrease the running time. Thanks in advanced
CREATE PROCEDURE PR_POP_UB_TOTGRALBYSIDNID
@[EMAIL PROTECTED]
datetime=3DNULL,
@[EMAIL PROTECTED]
datetime=3DNULL
AS
DECLARE @[EMAIL PROTECTED]
DATETIME,
@[EMAIL PROTECTED]
DATETIME,
@[EMAIL PROTECTED]
DATETIME,
@[EMAIL PROTECTED]
DATETIME,
@[EMAIL PROTECTED]
DATETIME,
@[EMAIL PROTECTED]
DATETIME,
@[EMAIL PROTECTED]
INT,
@[EMAIL PROTECTED]
VARCHAR(15),
@[EMAIL PROTECTED]
VARCHAR(15),
@[EMAIL PROTECTED]
INT,
@[EMAIL PROTECTED]
INT,
@[EMAIL PROTECTED]
INT,
@[EMAIL PROTECTED]
INT,
@[EMAIL PROTECTED]
INT,
@[EMAIL PROTECTED]
DATETIME,
@[EMAIL PROTECTED]
DATETIME,
@[EMAIL PROTECTED]
INT,
@[EMAIL PROTECTED]
INT,
@[EMAIL PROTECTED]
INT,
@[EMAIL PROTECTED]
DATETIME,
@[EMAIL PROTECTED]
INT,
@[EMAIL PROTECTED]
DATETIME
SET NOCOUNT ON
SELECT @[EMAIL PROTECTED]
=3D (SELECT GETDATE())
-- **** INTEGRIDAD, SE ELIMINAN DE LA TABLA DE ORIGEN TODOS
AQUELLOS POSIBLES REGISTROS QUE SEAN MAYORES A ESTE MOMENTO.
-- DELETE CSV WHERE PMM_DATETIME > GETDATE()
-- VALIDO LA INTEGRIDAD DE LOS PARAMETROS
IF(@[EMAIL PROTECTED]
IS NULL AND @[EMAIL PROTECTED]
IS NOT NULL) OR (@[EMAIL PROTECTED]
NOT NULL AND @[EMAIL PROTECTED]
IS NULL)
BEGIN
SELECT 'ERROR'=3D-1,'ERROR_DESC'=3D 'SOLO EXISTEN 2 POSIBILIDADES
DE EJECUTAR ESTE SP. 1) ENVIE FECHA INICIAL Y FECHA FINAL 2) NO ENVIE
NADA EL SP CONSULTARA A LA TABLA DE CONTROL'
RETURN -1
END
CREATE TABLE #TMP_UB_TOTGRALBYSIDNID (
ID_CSV int NOT NULL,
PMM_DATETIME datetime NULL,
SID varchar(15) NOT NULL,
NID varchar(15) NOT NULL,
MINid varchar(15) NOT NULL
)
CREATE INDEX IX_TMP_UB_TOTGRALBYSIDNID
ON #TMP_UB_TOTGRALBYSIDNID(PMM_DATETIME, SID, NID)
CREATE INDEX SX_TMP_UB_TOTGRALBYSIDNID
ON #TMP_UB_TOTGRALBYSIDNID(MINid)
IF(@[EMAIL PROTECTED]
IS NULL)
BEGIN
-- CHECO EL DATE_ID_END DE LA TABLA DE CONTROL PARA SABER
HASTA QUE FECHA ME QUEDE
SELECT @[EMAIL PROTECTED]
=3D (SELECT DATE_ID_END FROM CTL_TABLES WHERE
TABLE_NAME=3D'UB_TOTGRALBYSIDNID')
IF (@[EMAIL PROTECTED]
IS NULL)
BEGIN
-- SIGNIFICA QUE **** ALGUNA RAZON LA TABLA DE CONTROL NO
SABE EN QUE FECHA NOS QUEDAMOS
-- **** LO TANTO, VOY A LA TABLA DESTINO Y REVISO LA
ULTIMA FECHA CAPTURADA
SELECT @[EMAIL PROTECTED]
=3D (SELECT MAX(PMM_DATETIME) FROM
UB_TOTGRALBYSIDNID)
IF(@[EMAIL PROTECTED]
IS NULL)
BEGIN
-- SIGNIFICA QUE NO HAY INFORMACION CONFIABLE EN LA
TABLA DESTINO
SELECT 'ERROR'=3D-1,'ERROR_DESC'=3D 'AL CONSULTAR LA TABLA
UB_TOTGRALBYSIDNID NO FUE POSIBLE ESTABLECER LA FECHA INICIAL DE
BUSQUEDA PARA ESTA OPERACION'
RETURN -1
END
END
-- DE AQUI RESTO 2 HORAS PARA ASEGURAR OBTENER LA INFORMACION
MAS RECIENTE
SELECT @[EMAIL PROTECTED]
=3D (SELECT
DATEADD(hour,-2,@[EMAIL PROTECTED]
))
/* Add by German Vega, April 10*/
/*******************************/
SELECT @[EMAIL PROTECTED]
(VARCHAR(2), DATEPART(mm,
@[EMAIL PROTECTED]
)) + "/" +
CONVERT(VARCHAR(2), DATEPART(dd, @[EMAIL PROTECTED]
)) + "/" +
CONVERT(VARCHAR(4), DATEPART(yy, @[EMAIL PROTECTED]
)) + " " +
CONVERT(VARCHAR(2), DATEPART(hh, @[EMAIL PROTECTED]
)) + ":
00:00"
/* Add by German Vega, April 10*/
/*******************************/
SELECT @[EMAIL PROTECTED]
=3D (SELECT GETDATE())
END
ELSE
BEGIN
-- SIGNIFICA QUE EL USUARIO HA ELEGIDO EL PERIODO DE TIEMPO
EN EL CUAL BUSCARA INFORMACION EN LA TABLA CSV.
-- VALIDO QUE LA FECHA INICIAL SEA MENOR O IGUAL A LA FECHA
FINAL.
IF(@[EMAIL PROTECTED]
> @[EMAIL PROTECTED]
)
BEGIN
SELECT 'ERROR'=3D-1,'ERROR_DESC'=3D 'LA FECHA INICIAL ES MAYOR
A LA FINAL, FECHAL INICIAL: ' +
CONVERT(VARCHAR(19),CONVERT(VARCHAR(10), @[EMAIL PROTECTED]
)
+ ' ' + CONVERT(VARCHAR(8), @[EMAIL PROTECTED]
)) + ' FECHA FINAL: ' +
CONVERT(VARCHAR(19),CONVERT(VARCHAR(10), @[EMAIL PROTECTED]
) +
' ' + CONVERT(VARCHAR(8), @[EMAIL PROTECTED]
))
RETURN -1
END
END
-- ESTE ES EL ESPACIO MUESTRAL QUE VOY A TRABAJAR, LO INSERTO EN
LA TABLA TEM****AL
/*
SELECT ID_CSV,
PMM_DATETIME,
SID,
NID,
MINid
INTO #TMP_UB_TOTGRALBYSIDNID
FROM CSV
WHERE PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
AND @[EMAIL PROTECTED]
*/
INSERT INTO #TMP_UB_TOTGRALBYSIDNID
SELECT ID_CSV,
PMM_DATETIME,
SID,
NID,
MINid
FROM CSV
WHERE PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
AND @[EMAIL PROTECTED]
IF @[EMAIL PROTECTED]
=3D 0
BEGIN
SELECT 'ERROR'=3D-1,'ERROR_DESC'=3D 'NO HAY INFORMACION DISPONIBLE EN
EL PERIODO: ' +
CONVERT(VARCHAR(19),CONVERT(VARCHAR(10), @[EMAIL PROTECTED]
)
+ ' ' + CONVERT(VARCHAR(8), @[EMAIL PROTECTED]
)) + ' AL ' +
CONVERT(VARCHAR(19),CONVERT(VARCHAR(10), @[EMAIL PROTECTED]
) +
' ' + CONVERT(VARCHAR(8), @[EMAIL PROTECTED]
))
RETURN -1
END
-- DEBO HACER LA BUSQUEDA DEL @[EMAIL PROTECTED]
MAS PEQUE=D1O DENTRO DEL
INTERVALO
SELECT @[EMAIL PROTECTED]
=3D (SELECT MIN(ID_CSV) FROM
#TMP_UB_TOTGRALBYSIDNID)
-- YA TENGO EL @[EMAIL PROTECTED]
DEBO ENCONTRAR EL @[EMAIL PROTECTED]
SELECT @[EMAIL PROTECTED]
=3D (SELECT MAX(ID_CSV) FROM #TMP_UB_TOTGRALBYSIDNID)
-- AHORA ENCUENTRO LA FECHA INICIAL Y LA FINAL
SELECT @[EMAIL PROTECTED]
=3D (SELECT MIN(PMM_DATETIME) FROM
#TMP_UB_TOTGRALBYSIDNID)
SELECT @[EMAIL PROTECTED]
=3D (SELECT MAX(PMM_DATETIME) FROM
#TMP_UB_TOTGRALBYSIDNID)
-- RESPALDO LAS VARIABLES PARA QUE POSTERIORMENTE ACTUALIZE LA
TABLA DE CONTROL
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
/*SELECT @[EMAIL PROTECTED]
RETURN 0*/
-- SE DEBE AJUSTAR EL @[EMAIL PROTECTED]
A LA HORA QUE PERTENEZCA.
SELECT @[EMAIL PROTECTED]
=3D (SELECT CONVERT(DATETIME,
(CONVERT(VARCHAR(10),@[EMAIL PROTECTED]
) + ' 00:00:00')))
SELECT @[EMAIL PROTECTED]
=3D (SELECT CONVERT(DATETIME,
(CONVERT(VARCHAR(10),@[EMAIL PROTECTED]
) + ' 00:04:59')))
WHILE (@[EMAIL PROTECTED]
<=3D @[EMAIL PROTECTED]
)
BEGIN
IF ((@[EMAIL PROTECTED]
>=3D @[EMAIL PROTECTED]
) AND (@[EMAIL PROTECTED]
<=3D
@[EMAIL PROTECTED]
))
BEGIN
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
SELECT @[EMAIL PROTECTED]
=3D NULL
BREAK
END
ELSE
BEGIN
SELECT @[EMAIL PROTECTED]
=3D (SELECT DATEADD(ss,
300,@[EMAIL PROTECTED]
))
SELECT @[EMAIL PROTECTED]
=3D (SELECT DATEADD(ss,
299,@[EMAIL PROTECTED]
))
END
END
-- AJUSTO EL PRIMER INTERVALO DE BUSQUEDA
SELECT @[EMAIL PROTECTED]
=3D (SELECT DATEADD(ss,299,@[EMAIL PROTECTED]
))
SELECT @[EMAIL PROTECTED]
=3D 0
/*
SELECT @[EMAIL PROTECTED]
AS 'dINIT_DATE',
@[EMAIL PROTECTED]
AS 'dEND_DATE',
@[EMAIL PROTECTED]
AS 'dINIT_DATE_AUX',
@[EMAIL PROTECTED]
AS 'dEND_DATE_AUX'
RETURN 0
*/
-- INICIO CURSOR PARA OBTENER LAS ESTADISTICAS **** CADA UNO DE
LOS SID ACTIVOS EN LA TABLA SID_NID_CENTRALES
DECLARE CURSIDS CURSOR FOR
SELECT DISTINCT(SID) AS 'SIDS',
NID AS 'NID'
FROM SID_NID_CENTRALES
WHERE BSTATUS =3D 1
ORDER BY SID
OPEN CURSIDS
FETCH CURSIDS INTO @[EMAIL PROTECTED]
-- RESPALDO LAS VARIABLES
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
(@[EMAIL PROTECTED]
=3D 0)
BEGIN
SELECT @[EMAIL PROTECTED]
=3D (SELECT ISNULL(COUNT(PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID
WHERE PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
AND
@[EMAIL PROTECTED]
AND
SID =3D @[EMAIL PROTECTED]
AND NID =3D @[EMAIL PROTECTED]
)
SELECT @[EMAIL PROTECTED]
=3D (SELECT ISNULL(COUNT(A.PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID AS A
LEFT JOIN usr_mines AS B ON A.MINid =3D
B.MIN_id
WHERE A.PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
@[EMAIL PROTECTED]
AND
A.SID =3D @[EMAIL PROTECTED]
AND A.NID =3D @[EMAIL PROTECTED]
GROUP BY B.corpo_id
HAVING B.corpo_id =3D 'BAZ')
SELECT @[EMAIL PROTECTED]
=3D (SELECT ISNULL(COUNT(A.PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID AS A
LEFT JOIN usr_mines AS B ON A.MINid =3D
B.MIN_id
WHERE A.PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
@[EMAIL PROTECTED]
AND
A.SID =3D @[EMAIL PROTECTED]
AND A.NID =3D @[EMAIL PROTECTED]
GROUP BY B.corpo_id
HAVING B.corpo_id =3D 'EKT')
SELECT @[EMAIL PROTECTED]
=3D (SELECT ISNULL(COUNT(A.PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID AS A
LEFT JOIN usr_mines AS B ON A.MINid =3D
B.MIN_id
WHERE A.PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
@[EMAIL PROTECTED]
AND
A.SID =3D @[EMAIL PROTECTED]
AND A.NID =3D @[EMAIL PROTECTED]
GROUP BY B.corpo_id
HAVING B.corpo_id IS NULL)
-- VALIDA SI YA HAY INFORMACION EN LA TABLA DESTINO PARA EL
INTERVALO DE TIEMPO EN CUESTION, SI ASI ES ENTONCES
-- ACTUALIZA EL REGISTRO EXISTENTE CON LAS NUEVAS CANTIDADES
OBTENIDAS.
UPDATE UB_TOTGRALBYSIDNID
SET SID =3D @[EMAIL PROTECTED]
NID =3D @[EMAIL PROTECTED]
BAZ =3D ISNULL(@[EMAIL PROTECTED]
),
EKT =3D ISNULL(@[EMAIL PROTECTED]
),
IUS =3D ISNULL(@[EMAIL PROTECTED]
),
TOT =3D ISNULL(@[EMAIL PROTECTED]
)
WHERE PMM_DATETIME =3D @[EMAIL PROTECTED]
AND
SID =3D @[EMAIL PROTECTED]
AND NID =3D @[EMAIL PROTECTED]
IF @[EMAIL PROTECTED]
=3D 0
BEGIN
-- SIGNIFICA QUE NO HAY INFORMACION EN LA TABLA DESTINO
PARA EL INTERVALO DE TIEMPO EN CUESTION, ES NUEVO
INSERT INTO UB_TOTGRALBYSIDNID
VALUES(@[EMAIL PROTECTED]
(@[EMAIL PROTECTED]
),ISNULL(@[EMAIL PROTECTED]
),ISNULL(@[EMAIL PROTECTED]
),ISNULL(@[EMAIL PROTECTED]
))
END
-- INCREMENTO LAS VARIABLES.
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
+ 1
SELECT @[EMAIL PROTECTED]
=3D (SELECT DATEADD(ss,300,@[EMAIL PROTECTED]
))
SELECT @[EMAIL PROTECTED]
=3D (SELECT DATEADD(ss,299,@[EMAIL PROTECTED]
))
-- SE OBTIENE TODA LA INFORMACION DE LA TABLA DE ORIGEN DESDE
EL INTERVALO INICIAL AJUSTADO HASTA EL INTERVALO FINAL EXACTO.
WHILE (@[EMAIL PROTECTED]
<=3D @[EMAIL PROTECTED]
)
BEGIN
SELECT @[EMAIL PROTECTED]
=3D (SELECT ISNULL(COUNT(PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID
WHERE PMM_DATETIME BETWEEN
@[EMAIL PROTECTED]
AND @[EMAIL PROTECTED]
AND
SID =3D @[EMAIL PROTECTED]
AND NID =3D @[EMAIL PROTECTED]
)
SELECT @[EMAIL PROTECTED]
=3D (SELECT
ISNULL(COUNT(A.PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID AS A
LEFT JOIN usr_mines AS B ON
A.MINid =3D B.MIN_id
WHERE A.PMM_DATETIME BETWEEN
@[EMAIL PROTECTED]
AND @[EMAIL PROTECTED]
AND
A.SID =3D @[EMAIL PROTECTED]
AND A.NID =3D
@[EMAIL PROTECTED]
GROUP BY B.corpo_id
HAVING B.corpo_id =3D 'BAZ')
SELECT @[EMAIL PROTECTED]
=3D (SELECT
ISNULL(COUNT(A.PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID AS A
LEFT JOIN usr_mines AS B ON
A.MINid =3D B.MIN_id
WHERE A.PMM_DATETIME BETWEEN
@[EMAIL PROTECTED]
AND @[EMAIL PROTECTED]
AND
A.SID =3D @[EMAIL PROTECTED]
AND A.NID =3D
@[EMAIL PROTECTED]
GROUP BY B.corpo_id
HAVING B.corpo_id =3D 'EKT')
SELECT @[EMAIL PROTECTED]
=3D (SELECT
ISNULL(COUNT(A.PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID AS A
LEFT JOIN usr_mines AS B ON
A.MINid =3D B.MIN_id
WHERE A.PMM_DATETIME BETWEEN
@[EMAIL PROTECTED]
AND @[EMAIL PROTECTED]
AND
A.SID =3D @[EMAIL PROTECTED]
AND A.NID =3D
@[EMAIL PROTECTED]
GROUP BY B.corpo_id
HAVING B.corpo_id IS NULL)
-- VALIDA SI YA HAY INFORMACION EN LA TABLA DESTINO
PARA EL INTERVALO DE TIEMPO EN CUESTION, SI ASI ES ENTONCES
-- ACTUALIZA EL REGISTRO EXISTENTE CON LAS NUEVAS
CANTIDADES OBTENIDAS.
UPDATE UB_TOTGRALBYSIDNID
SET SID =3D @[EMAIL PROTECTED]
NID =3D @[EMAIL PROTECTED]
BAZ =3D ISNULL(@[EMAIL PROTECTED]
),
EKT =3D ISNULL(@[EMAIL PROTECTED]
),
IUS =3D ISNULL(@[EMAIL PROTECTED]
),
TOT =3D ISNULL(@[EMAIL PROTECTED]
)
WHERE PMM_DATETIME =3D @[EMAIL PROTECTED]
AND
SID =3D @[EMAIL PROTECTED]
AND NID =3D @[EMAIL PROTECTED]
IF @[EMAIL PROTECTED]
=3D 0
BEGIN
-- SIGNIFICA QUE NO HAY INFORMACION EN LA TABLA
DESTINO PARA EL INTERVALO DE TIEMPO EN CUESTION, ES NUEVO
INSERT INTO UB_TOTGRALBYSIDNID
VALUES(@[EMAIL PROTECTED]
(@[EMAIL PROTECTED]
),ISNULL(@[EMAIL PROTECTED]
),ISNULL(@[EMAIL PROTECTED]
),ISNULL(@[EMAIL PROTECTED]
))
END
-- INCREMENTO LAS VARIABLES.
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
+ 1
SELECT @[EMAIL PROTECTED]
=3D (SELECT DATEADD(ss,
300,@[EMAIL PROTECTED]
))
SELECT @[EMAIL PROTECTED]
=3D (SELECT DATEADD(ss,
299,@[EMAIL PROTECTED]
))
END
-- RECUPERO LAS VARIABLES.
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
FETCH CURSIDS INTO @[EMAIL PROTECTED]
CURSIDS
DEALLOCATE cursor CURSIDS
UPDATE CTL_TABLES
SET ID_BEGIN =3D @[EMAIL PROTECTED]
DATE_ID_BEGIN =3D @[EMAIL PROTECTED]
ID_END =3D @[EMAIL PROTECTED]
DATE_ID_END =3D @[EMAIL PROTECTED]
WHERE TABLE_NAME=3D'UB_TOTGRALBYSIDNID'
SELECT @[EMAIL PROTECTED]
=3D (SELECT GETDATE())
SELECT 'ERROR'=3D0,'ERROR_DESC'=3D 'MOVIMIENTO EXITOSO SE REALIZARON '
+ CONVERT(VARCHAR(12),@[EMAIL PROTECTED]
) + ' INSERCIONES A LA TABLA
UB_TOTGRALBYSIDNID ' +
' EL PERIODO DE EJECUCION FUE DE: ' +
CONVERT(VARCHAR(19),CONVERT(VARCHAR(10),@[EMAIL PROTECTED]
) + ' ' +
CONVERT(VARCHAR(8),@[EMAIL PROTECTED]
)) +
' HASTA ' + CONVERT(VARCHAR(19),CONVERT(VARCHAR(10),@[EMAIL PROTECTED]
)
+ ' ' + CONVERT(VARCHAR(8),@[EMAIL PROTECTED]
))
RETURN 0
Regards,
Jorge
|
