SEO

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
@dINIT_DATE datetime=NULL,
@dEND_DATE datetime=NULL
AS
DECLARE @dINIT_DATE_AUX DATETIME,
@dEND_DATE_AUX DATETIME,
@dINIT_DATE_COPY DATETIME,
@dEND_DATE_COPY DATETIME,
@dINIT_DATE_AUX_COPY DATETIME,
@dEND_DATE_AUX_COPY DATETIME,
@iCONTADOR INT,
@CUR_SID VARCHAR(15),
@CUR_NID VARCHAR(15),
@iTOTAL INT,
@iTOTAL_BAZ INT,
@iTOTAL_EKT INT,
@iTOTAL_IUS INT,
@iTOTAL_BAD INT,
@INIT_PROC DATETIME,
@END_PROC DATETIME,
@ID_BEGIN INT,
@ID_END INT,
@CTL_ID_BEGIN INT,
@CTL_DATE_ID_BEGIN DATETIME,
@CTL_ID_END INT,
@CTL_DATE_ID_END DATETIME
SET NOCOUNT ON

SELECT @INIT_PROC = (SELECT GETDATE())

-- POR 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(@dINIT_DATE IS NULL AND @dEND_DATE IS NOT NULL) OR (@dINIT_DATE
IS NOT NULL AND @dEND_DATE IS NULL)
BEGIN
SELECT 'ERROR'=-1,'ERROR_DESC'= '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(@dINIT_DATE IS NULL)
BEGIN
-- CHECO EL DATE_ID_END DE LA TABLA DE CONTROL PARA SABER
HASTA QUE FECHA ME QUEDE
SELECT @dINIT_DATE = (SELECT DATE_ID_END FROM CTL_TABLES WHERE
TABLE_NAME='UB_TOTGRALBYSIDNID')
IF (@dINIT_DATE IS NULL)
BEGIN
-- SIGNIFICA QUE POR ALGUNA RAZON LA TABLA DE CONTROL NO
SABE EN QUE FECHA NOS QUEDAMOS
-- POR LO TANTO, VOY A LA TABLA DESTINO Y REVISO LA
ULTIMA FECHA CAPTURADA
SELECT @dINIT_DATE = (SELECT MAX(PMM_DATETIME) FROM
UB_TOTGRALBYSIDNID)
IF(@dINIT_DATE IS NULL)
BEGIN
-- SIGNIFICA QUE NO HAY INFORMACION CONFIABLE EN LA
TABLA DESTINO
SELECT 'ERROR'=-1,'ERROR_DESC'= '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 @dINIT_DATE = (SELECT
DATEADD(hour,-2,@dINIT_DATE))
/* Add by German Vega, April 10*/
/*******************************/
SELECT @dINIT_DATE=CONVERT(VARCHAR(2), DATEPART(mm,
@dINIT_DATE)) + "/" +
CONVERT(VARCHAR(2), DATEPART(dd, @dINIT_DATE)) + "/" +
CONVERT(VARCHAR(4), DATEPART(yy, @dINIT_DATE)) + " " +
CONVERT(VARCHAR(2), DATEPART(hh, @dINIT_DATE)) + ":
00:00"
/* Add by German Vega, April 10*/
/*******************************/
SELECT @dEND_DATE = (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(@dINIT_DATE > @dEND_DATE)
BEGIN
SELECT 'ERROR'=-1,'ERROR_DESC'= 'LA FECHA INICIAL ES MAYOR
A LA FINAL, FECHAL INICIAL: ' +
CONVERT(VARCHAR(19),CONVERT(VARCHAR(10), @dINIT_DATE,102)
+ ' ' + CONVERT(VARCHAR(8), @dINIT_DATE,108)) + ' FECHA FINAL: ' +
CONVERT(VARCHAR(19),CONVERT(VARCHAR(10), @dEND_DATE,102) +
' ' + CONVERT(VARCHAR(8), @dEND_DATE,108))
RETURN -1
END
END

-- ESTE ES EL ESPACIO MUESTRAL QUE VOY A TRABAJAR, LO INSERTO EN
LA TABLA TEMPORAL
/*
SELECT ID_CSV,
PMM_DATETIME,
SID,
NID,
MINid
INTO #TMP_UB_TOTGRALBYSIDNID
FROM CSV
WHERE PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE
*/
INSERT INTO #TMP_UB_TOTGRALBYSIDNID
SELECT ID_CSV,
PMM_DATETIME,
SID,
NID,
MINid
FROM CSV
WHERE PMM_DATETIME BETWEEN @dINIT_DATE AND @dEND_DATE

IF @@ROWCOUNT = 0
BEGIN
SELECT 'ERROR'=-1,'ERROR_DESC'= 'NO HAY INFORMACION DISPONIBLE EN
EL PERIODO: ' +
CONVERT(VARCHAR(19),CONVERT(VARCHAR(10), @dINIT_DATE,102)
+ ' ' + CONVERT(VARCHAR(8), @dINIT_DATE,108)) + ' AL ' +
CONVERT(VARCHAR(19),CONVERT(VARCHAR(10), @dEND_DATE,102) +
' ' + CONVERT(VARCHAR(8), @dEND_DATE,108))
RETURN -1
END

-- DEBO HACER LA BUSQUEDA DEL @ID_BEGIN MAS PEQUEÑO DENTRO DEL
INTERVALO
SELECT @ID_BEGIN = (SELECT MIN(ID_CSV) FROM
#TMP_UB_TOTGRALBYSIDNID)
-- YA TENGO EL @ID_BEGIN, DEBO ENCONTRAR EL @ID_END
SELECT @ID_END = (SELECT MAX(ID_CSV) FROM #TMP_UB_TOTGRALBYSIDNID)
-- AHORA ENCUENTRO LA FECHA INICIAL Y LA FINAL
SELECT @dINIT_DATE = (SELECT MIN(PMM_DATETIME) FROM
#TMP_UB_TOTGRALBYSIDNID)
SELECT @dEND_DATE = (SELECT MAX(PMM_DATETIME) FROM
#TMP_UB_TOTGRALBYSIDNID)
-- RESPALDO LAS VARIABLES PARA QUE POSTERIORMENTE ACTUALIZE LA
TABLA DE CONTROL
SELECT @CTL_ID_BEGIN = @ID_BEGIN
SELECT @CTL_DATE_ID_BEGIN = @dINIT_DATE
SELECT @CTL_ID_END = @ID_END
SELECT @CTL_DATE_ID_END = @dEND_DATE

/*SELECT @dINIT_DATE,@dEND_DATE
RETURN 0*/

-- SE DEBE AJUSTAR EL @dINIT_DATE A LA HORA QUE PERTENEZCA.
SELECT @dINIT_DATE_AUX = (SELECT CONVERT(DATETIME,
(CONVERT(VARCHAR(10),@dINIT_DATE,102) + ' 00:00:00')))
SELECT @dEND_DATE_AUX = (SELECT CONVERT(DATETIME,
(CONVERT(VARCHAR(10),@dINIT_DATE,102) + ' 00:04:59')))
WHILE (@dINIT_DATE_AUX <= @dEND_DATE_AUX)
BEGIN
IF ((@dINIT_DATE >= @dINIT_DATE_AUX) AND (@dINIT_DATE <=
@dEND_DATE_AUX))
BEGIN
SELECT @dINIT_DATE = @dINIT_DATE_AUX
SELECT @dINIT_DATE_AUX = NULL
BREAK
END
ELSE
BEGIN
SELECT @dINIT_DATE_AUX = (SELECT DATEADD(ss,
300,@dINIT_DATE_AUX))
SELECT @dEND_DATE_AUX = (SELECT DATEADD(ss,
299,@dINIT_DATE_AUX))
END
END
-- AJUSTO EL PRIMER INTERVALO DE BUSQUEDA
SELECT @dEND_DATE_AUX = (SELECT DATEADD(ss,299,@dINIT_DATE))
SELECT @iCONTADOR = 0

/*
SELECT @dINIT_DATE AS 'dINIT_DATE',
@dEND_DATE AS 'dEND_DATE',
@dINIT_DATE_AUX AS 'dINIT_DATE_AUX',
@dEND_DATE_AUX AS 'dEND_DATE_AUX'
RETURN 0
*/

-- INICIO CURSOR PARA OBTENER LAS ESTADISTICAS POR 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 = 1
ORDER BY SID
OPEN CURSIDS
FETCH CURSIDS INTO @CUR_SID,@CUR_NID
-- RESPALDO LAS VARIABLES
SELECT @dINIT_DATE_COPY = @dINIT_DATE
SELECT @dEND_DATE_COPY = @dEND_DATE
SELECT @dINIT_DATE_AUX_COPY = @dINIT_DATE_AUX
SELECT @dEND_DATE_AUX_COPY = @dEND_DATE_AUX
WHILE (@@sqlstatus = 0)
BEGIN
SELECT @iTOTAL = (SELECT ISNULL(COUNT(PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID
WHERE PMM_DATETIME BETWEEN @dINIT_DATE AND
@dEND_DATE_AUX AND
SID = @CUR_SID AND NID = @CUR_NID)
SELECT @iTOTAL_BAZ = (SELECT ISNULL(COUNT(A.PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID AS A
LEFT JOIN usr_mines AS B ON A.MINid =
B.MIN_id
WHERE A.PMM_DATETIME BETWEEN @dINIT_DATE
AND @dEND_DATE_AUX AND
A.SID = @CUR_SID AND A.NID = @CUR_NID
GROUP BY B.corpo_id
HAVING B.corpo_id = 'BAZ')
SELECT @iTOTAL_EKT = (SELECT ISNULL(COUNT(A.PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID AS A
LEFT JOIN usr_mines AS B ON A.MINid =
B.MIN_id
WHERE A.PMM_DATETIME BETWEEN @dINIT_DATE
AND @dEND_DATE_AUX AND
A.SID = @CUR_SID AND A.NID = @CUR_NID
GROUP BY B.corpo_id
HAVING B.corpo_id = 'EKT')
SELECT @iTOTAL_IUS = (SELECT ISNULL(COUNT(A.PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID AS A
LEFT JOIN usr_mines AS B ON A.MINid =
B.MIN_id
WHERE A.PMM_DATETIME BETWEEN @dINIT_DATE
AND @dEND_DATE_AUX AND
A.SID = @CUR_SID AND A.NID = @CUR_NID
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 = @CUR_SID,
NID = @CUR_NID,
BAZ = ISNULL(@iTOTAL_BAZ,0),
EKT = ISNULL(@iTOTAL_EKT,0),
IUS = ISNULL(@iTOTAL_IUS,0),
TOT = ISNULL(@iTOTAL,0)
WHERE PMM_DATETIME = @dINIT_DATE AND
SID = @CUR_SID AND NID = @CUR_NID

IF @@ROWCOUNT = 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(@dINIT_DATE,@CUR_SID,@CUR_NID,ISNULL(@iTOTA L_BAZ,
0),ISNULL(@iTOTAL_EKT,0),ISNULL(@iTOTAL_IUS,0),ISN ULL(@iTOTAL,0))
END

-- INCREMENTO LAS VARIABLES.
SELECT @iCONTADOR = @iCONTADOR + 1
SELECT @dINIT_DATE = (SELECT DATEADD(ss,300,@dINIT_DATE))
SELECT @dEND_DATE_AUX = (SELECT DATEADD(ss,299,@dINIT_DATE))
-- SE OBTIENE TODA LA INFORMACION DE LA TABLA DE ORIGEN DESDE
EL INTERVALO INICIAL AJUSTADO HASTA EL INTERVALO FINAL EXACTO.
WHILE (@dINIT_DATE <= @dEND_DATE)
BEGIN
SELECT @iTOTAL = (SELECT ISNULL(COUNT(PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID
WHERE PMM_DATETIME BETWEEN
@dINIT_DATE AND @dEND_DATE_AUX AND
SID = @CUR_SID AND NID = @CUR_NID)
SELECT @iTOTAL_BAZ = (SELECT
ISNULL(COUNT(A.PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID AS A
LEFT JOIN usr_mines AS B ON
A.MINid = B.MIN_id
WHERE A.PMM_DATETIME BETWEEN
@dINIT_DATE AND @dEND_DATE_AUX AND
A.SID = @CUR_SID AND A.NID =
@CUR_NID
GROUP BY B.corpo_id
HAVING B.corpo_id = 'BAZ')
SELECT @iTOTAL_EKT = (SELECT
ISNULL(COUNT(A.PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID AS A
LEFT JOIN usr_mines AS B ON
A.MINid = B.MIN_id
WHERE A.PMM_DATETIME BETWEEN
@dINIT_DATE AND @dEND_DATE_AUX AND
A.SID = @CUR_SID AND A.NID =
@CUR_NID
GROUP BY B.corpo_id
HAVING B.corpo_id = 'EKT')
SELECT @iTOTAL_IUS = (SELECT
ISNULL(COUNT(A.PMM_DATETIME),0)
FROM #TMP_UB_TOTGRALBYSIDNID AS A
LEFT JOIN usr_mines AS B ON
A.MINid = B.MIN_id
WHERE A.PMM_DATETIME BETWEEN
@dINIT_DATE AND @dEND_DATE_AUX AND
A.SID = @CUR_SID AND A.NID =
@CUR_NID
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 = @CUR_SID,
NID = @CUR_NID,
BAZ = ISNULL(@iTOTAL_BAZ,0),
EKT = ISNULL(@iTOTAL_EKT,0),
IUS = ISNULL(@iTOTAL_IUS,0),
TOT = ISNULL(@iTOTAL,0)
WHERE PMM_DATETIME = @dINIT_DATE AND
SID = @CUR_SID AND NID = @CUR_NID

IF @@ROWCOUNT = 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(@dINIT_DATE,@CUR_SID,@CUR_NID,ISNULL(@iTOTA L_BAZ,
0),ISNULL(@iTOTAL_EKT,0),ISNULL(@iTOTAL_IUS,0),ISN ULL(@iTOTAL,0))
END

-- INCREMENTO LAS VARIABLES.
SELECT @iCONTADOR = @iCONTADOR + 1
SELECT @dINIT_DATE = (SELECT DATEADD(ss,
300,@dINIT_DATE))
SELECT @dEND_DATE_AUX = (SELECT DATEADD(ss,
299,@dINIT_DATE))
END
-- RECUPERO LAS VARIABLES.
SELECT @dINIT_DATE = @dINIT_DATE_COPY
SELECT @dEND_DATE = @dEND_DATE_COPY
SELECT @dINIT_DATE_AUX = @dINIT_DATE_AUX_COPY
SELECT @dEND_DATE_AUX = @dEND_DATE_AUX_COPY
FETCH CURSIDS INTO @CUR_SID,@CUR_NID
END
CLOSE CURSIDS
DEALLOCATE cursor CURSIDS

UPDATE CTL_TABLES
SET ID_BEGIN = @CTL_ID_BEGIN,
DATE_ID_BEGIN = @CTL_DATE_ID_BEGIN,
ID_END = @CTL_ID_END,
DATE_ID_END = @CTL_DATE_ID_END
WHERE TABLE_NAME='UB_TOTGRALBYSIDNID'

SELECT @END_PROC = (SELECT GETDATE())
SELECT 'ERROR'=0,'ERROR_DESC'= 'MOVIMIENTO EXITOSO SE REALIZARON '
+ CONVERT(VARCHAR(12),@iCONTADOR) + ' INSERCIONES A LA TABLA
UB_TOTGRALBYSIDNID ' +
' EL PERIODO DE EJECUCION FUE DE: ' +
CONVERT(VARCHAR(19),CONVERT(VARCHAR(10),@INIT_PROC ,102) + ' ' +
CONVERT(VARCHAR(8),@INIT_PROC,108)) +
' HASTA ' + CONVERT(VARCHAR(19),CONVERT(VARCHAR(10),@END_PROC, 102)
+ ' ' + CONVERT(VARCHAR(8),@END_PROC,108))
RETURN 0

Regards,
Jorge

On Apr 11, 11:46*pm, Jorge Reyes <jorg_re...@hotmail.com> wrote:
> 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: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

(1)Remove "cursor" from the stored proc and apply temp table logic
there. i.e. write a code to apply the logic using temp table instead
of cursors. cursors perform very slow compare to the plain sql

(2)Get showplan for the proc and find out if any queries inside the
proc preforms "Table Scan". You can find showplan for a proc without
actually executing it by turning "set fmtonly" to on

-HTH
Manish Negandhi
[TeamSybase Intern]