Buenas Tardes:
Alguien me podria decir como puedo hacer este stored procedure mas
rapido?? estoy usando SYBASE y tarda 00:13:47 en ejecutarse, necesito
que tarde menos de 6 minutos en ejecutarse. Ojala alguien pueda
ayudarme.
Gracias.
CREATE PROCEDURE PR_POP_UB_USRDETBYSIDNID
@[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]
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())
-- 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_USRDETBYSIDNID2 (
ID_CSV int NOT NULL,
PMM_DATETIME datetime NOT NULL,
SID varchar(15) NULL,
NID varchar(15) NULL,
MINid varchar(15) NOT NULL
)
CREATE INDEX IX_#TMP_UB_USRDETBYSIDNID2
ON #TMP_UB_USRDETBYSIDNID2(PMM_DATETIME, SID, NID)
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_USRDETBYSIDNID')
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_USRDETBYSIDNID)
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_USRDETBYSIDNID2 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]
))
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"
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
INSERT INTO #TMP_UB_USRDETBYSIDNID2
SELECT ID_CSV,
PMM_DATETIME,
SID,
NID,
MINid
FROM CSV
WHERE PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
AND @[EMAIL PROTECTED]
ORDER BY PMM_DATETIME
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_USRDETBYSIDNID2)
-- YA TENGO EL @[EMAIL PROTECTED]
DEBO ENCONTRAR EL @[EMAIL PROTECTED]
SELECT @[EMAIL PROTECTED]
=3D (SELECT MAX(ID_CSV) FROM #TMP_UB_USRDETBYSIDNID2)
-- AHORA ENCUENTRO LA FECHA INICIAL Y LA FINAL
SELECT @[EMAIL PROTECTED]
=3D (SELECT MIN(PMM_DATETIME) FROM
#TMP_UB_USRDETBYSIDNID2)
SELECT @[EMAIL PROTECTED]
=3D (SELECT MAX(PMM_DATETIME) FROM
#TMP_UB_USRDETBYSIDNID2)
-- 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]
-- 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
CREATE TABLE #TMP2_UB_USRDETBYSIDNID2 (
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_#TMP2_UB_USRDETBYSIDNID2
ON #TMP2_UB_USRDETBYSIDNID2(PMM_DATETIME, SID, NID)
-- INICIO CURSOR PARA OBTENER LAS ESTADISTICAS **** CADA UNO DE LOS
SID/NID 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]
@[EMAIL PROTECTED]
(@[EMAIL PROTECTED]
=3D 0)
BEGIN
-- RESPALDO LAS VARIABLES
SELECT
@[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
@[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
@[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
-- INSERTO LOS REGISTROS QUE SEAN IGUALES AL SID Y NID
CORRESPONDIENTES EN LA 2DA TABLA TEM****AL
INSERT INTO #TMP2_UB_USRDETBYSIDNID2
SELECT
ID_CSV,
PMM_DATETIME,
@[EMAIL PROTECTED]
@[EMAIL PROTECTED]
MINid
FROM #TMP_UB_USRDETBYSIDNID2
WHERE
SID =3D @[EMAIL PROTECTED]
AND
NID =3D @[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 0,
@[EMAIL PROTECTED]
=3D 0,
@[EMAIL PROTECTED]
=3D 0,
@[EMAIL PROTECTED]
=3D 0,
@[EMAIL PROTECTED]
=3D 0,
@[EMAIL PROTECTED]
=3D 0,
@[EMAIL PROTECTED]
=3D 0,
@[EMAIL PROTECTED]
=3D 0
SELECT @[EMAIL PROTECTED]
=3D COUNT(DISTINCT(A.MINid))
FROM #TMP2_UB_USRDETBYSIDNID2 A, usr_mines B
WHERE A.PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
AND
@[EMAIL PROTECTED]
AND A.SID =3D @[EMAIL PROTECTED]
AND A.NID =3D @[EMAIL PROTECTED]
AND B.corpo_id
=3D 'BAZ'
AND A.MINid =3D B.MIN_id
SELECT @[EMAIL PROTECTED]
=3D COUNT(DISTINCT(A.MINid))
FROM #TMP2_UB_USRDETBYSIDNID2 A, usr_mines B
WHERE A.PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
AND
@[EMAIL PROTECTED]
AND A.SID =3D @[EMAIL PROTECTED]
AND A.NID =3D @[EMAIL PROTECTED]
AND B.corpo_id
=3D 'EKT'
AND A.MINid =3D B.MIN_id
DELETE #TMP2_UB_USRDETBYSIDNID2
FROM #TMP2_UB_USRDETBYSIDNID2 A, usr_mines B
WHERE A.PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
AND
@[EMAIL PROTECTED]
AND A.SID =3D @[EMAIL PROTECTED]
AND A.NID =3D @[EMAIL PROTECTED]
AND (B.corpo_id
=3D 'EKT'
OR B.corpo_id =3D 'BAZ')
AND A.MINid =3D B.MIN_id
SELECT @[EMAIL PROTECTED]
=3D COUNT(DISTINCT(MINid))
FROM #TMP2_UB_USRDETBYSIDNID2
WHERE
PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
AND @[EMAIL PROTECTED]
AND
SID =3D @[EMAIL PROTECTED]
AND
NID =3D @[EMAIL PROTECTED]
SELECT @[EMAIL PROTECTED]
ISNULL(@[EMAIL PROTECTED]
)+ISNULL(@[EMAIL PROTECTED]
)+ISNULL(@[EMAIL PROTECTED]
)
-- ACTUALIZA EL REGISTRO EXISTENTE CON LAS NUEVAS
CANTIDADES OBTENIDAS.
UPDATE UB_USRDETBYSIDNID
SET SID =3D @[EMAIL PROTECTED]
NID =3D @[EMAIL PROTECTED]
USRS_BAZ =3D ISNULL(@[EMAIL PROTECTED]
),
USRS_EKT =3D ISNULL(@[EMAIL PROTECTED]
),
USRS_IUS =3D ISNULL(@[EMAIL PROTECTED]
),
USRS_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_USRDETBYSIDNID
VALUES(@[EMAIL PROTECTED]
(@[EMAIL PROTECTED]
),ISNULL(@[EMAIL PROTECTED]
),ISNULL(@[EMAIL PROTECTED]
),ISNULL(@[EMAIL PROTECTED]
))
END
DELETE #TMP2_UB_USRDETBYSIDNID2
WHERE
PMM_DATETIME BETWEEN @[EMAIL PROTECTED]
AND @[EMAIL PROTECTED]
SID =3D @[EMAIL PROTECTED]
AND
NID =3D @[EMAIL PROTECTED]
-- 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]
@[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
@[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
DELETE #TMP_UB_USRDETBYSIDNID2
WHERE
SID =3D @[EMAIL PROTECTED]
AND
NID =3D @[EMAIL PROTECTED]
FETCH
CURSIDS
INTO
@[EMAIL PROTECTED]
@[EMAIL PROTECTED]
-- LIMPIO LA TABLA LA 2DA TABLA TEM****AL
TRUNCATE TABLE #TMP2_UB_USRDETBYSIDNID2
END
CLOSE CURSIDS
DEALLOCATE cursor CURSIDS
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_USRDETBYSIDNID2 ' +
' 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
|
