Ciao a tutti,
ho una procedura SQL che importa in della Tabelle un file .txt in base al tipo record, tutto molto semplice.
La stessa procedura eseguita su 2 server con, stesso database e stesso hardware,
- Server 1, 4 minuti, server non in produzione
- Server 2, 18 minuti, server in produzione
Da cosa puo dipendere?
il pezzo che ho aggiunto nelle procedura è il seguente:
----------- INIZIO 'PRT' -----------
ELSE IF @TIPOREC IN ('PRT')
BEGIN
set @SMAMSG = null
SET @SMAMSG = '14/1 SMA_RICE - Inizio Tipo Record PRT file: ' + @FILE + ' riga: ' + @RIGA
BEGIN
set @SMAMSG = null
SET @SMAMSG = '14/2 SMA_RICE - Tipo Record PRT - INSERT INTO sma_PROD_USCITE_TEMP file: ' + @FILE + ' riga: ' + @RIGA
INSERT INTO sma_PROD_USCITE_TEMP (
PRU_KCODDLO, PRU_KCODPRU, PRU_DATAUSC,
PRU_NUMUSC, PRU_QSCORTA, PRU_ABARCODE,
PRU_ASOTTO, PRU_IPRZCOP, PRU_PERCDEF,
PRU_PERCSCB, PRU_NALTEZZ, PRU_NLARGHE,
PRU_NSPESSO, PRU_NPESO, PRU_NCOMPRV,
PRU_KCODLVR, PRU_ADESDIF, PRU_ADESCRI,
PRU_KCODEDT, PRU_KCODPER, PRU_KCODTPR,
PRU_KCODMIT, PRU_NCODTES, PRU_NCODVAR,
PRU_KCODASE, PRU_NCODDIF, PRU_KCODRIV,
PRU_IPRZNET,
PRU_ANOTE, PRU_ABARCODEPV, PRU_RIFERIMENTO)
VALUES ( @DL, CAST(SUBSTRING(@RIGA,7,8)AS INT), CONVERT(date, SUBSTRING(@RIGA,15,8), 104),
CAST(SUBSTRING(@RIGA,23,5)AS INT), CAST(SUBSTRING(@RIGA,28,8)AS INT), LTRIM(RTRIM(SUBSTRING(@RIGA,36,18))),
LTRIM(RTRIM(SUBSTRING(@RIGA,54,50))), CAST(SUBSTRING(@RIGA,104,8)AS float)/100, CAST(SUBSTRING(@RIGA,112,8)AS float)/100,
CAST(SUBSTRING(@RIGA,448,8)AS float)/100, CAST(SUBSTRING(@RIGA,128,8)AS float), CAST(SUBSTRING(@RIGA,136,8)AS float),
CAST(SUBSTRING(@RIGA,144,8)AS float), CAST( SUBSTRING(@RIGA,152,8)AS float), CAST(SUBSTRING(@RIGA,160,9)AS float)/1000000,
SUBSTRING( @RIGA,169,1), LTRIM(RTRIM(SUBSTRING(@RIGA,170,200))), LTRIM(RTRIM(SUBSTRING(@RIGA,370,50))),
ISNULL(CAST(SUBSTRING(@RIGA,420,4)AS INT),0), ISNULL(CAST(SUBSTRING(@RIGA,424,2)AS INT),0), ISNULL(CAST(SUBSTRING(@RIGA,426,1)AS INT),0),
ISNULL(CAST(SUBSTRING(@RIGA,427,3)AS INT),0), ISNULL(CAST(SUBSTRING(@RIGA,430,7)AS INT),0), ISNULL(CAST(SUBSTRING(@RIGA,437,2)AS INT),0),
ISNULL(CAST(SUBSTRING(@RIGA,439,1)AS INT),0), ISNULL(CAST(SUBSTRING(@RIGA,440,8)AS INT),0), CAST(SUBSTRING(@RIGA,465,8)AS INT),
ISNULL(CAST(SUBSTRING(@RIGA,456,9)AS float)/10000,0) ,
NULL, LTRIM(RTRIM(SUBSTRING(@RIGA,36,18))), NULL
)
END
END
----------- INIZIO 'PSC' -----------
ELSE IF @TIPOREC IN ('PSC')
BEGIN
set @SMAMSG = null
SET @SMAMSG = '14/3 SMA_RICE - Inizio Tipo Record PSC file: ' + @FILE + ' riga: ' + @RIGA
-- se ESISTE già il PRU x questa RIV ELIMINO LA RIGA E LA REINSERISCO --
IF EXISTS (SELECT 'S' FROM sma_PROD_USCITE
WHERE PRU_KCODDLO = @DL
AND PRU_KCODPRU = CAST(SUBSTRING(@RIGA,7,8)AS bigint)
AND PRU_KCODRIV = CAST(SUBSTRING(@RIGA,15,8)AS INT)
)
BEGIN
set @SMAMSG = null
SET @SMAMSG = '14/5 SMA_RICE - Tipo Record PRU ESISTE E LO AGGIORNO - file: ' + @FILE + ' riga: ' + @RIGA
/*
DELETE sma_PROD_USCITE
WHERE PRU_KCODDLO = @DL
AND PRU_KCODPRU = CAST(SUBSTRING(@RIGA,7,8)AS bigint)
AND PRU_KCODRIV = CAST(SUBSTRING(@RIGA,15,8)AS INT)
*/
SET @updPSC_ADESCRI = NULL
SET @updPSC_DATAUSC = NULL
SET @updPSC_NUMUSC = NULL
SET @updPSC_QSCORTA = NULL
SET @updPSC_ABARCODE = NULL
SET @updPSC_ASOTTO = NULL
SET @updPSC_IPRZCOP = NULL
SET @updPSC_PERCDEF = NULL
SET @updPSC_NCOMPRV = NULL
SET @updPSC_KCODLVR = NULL
SET @updPSC_KCODEDT = NULL
SET @updPSC_KCODPER = NULL
SET @updPSC_KCODTPR = NULL
SET @updPSC_KCODMIT = NULL
SET @updPSC_NCODTES = NULL
SET @updPSC_NCODVAR = NULL
SET @updPSC_NCODDIF = NULL
BEGIN
SELECT @updPSC_ADESCRI = PRU_ADESCRI,
@updPSC_DATAUSC = PRU_DATAUSC,
@updPSC_NUMUSC = PRU_NUMUSC,
@updPSC_QSCORTA = PRU_QSCORTA,
@updPSC_ABARCODE = PRU_ABARCODE,
@updPSC_ASOTTO = PRU_ASOTTO,
@updPSC_IPRZCOP = PRU_IPRZCOP,
@updPSC_PERCDEF = PRU_PERCDEF,
@updPSC_NCOMPRV = PRU_NCOMPRV,
@updPSC_KCODLVR = PRU_KCODLVR,
@updPSC_KCODEDT = PRU_KCODEDT,
@updPSC_KCODPER = PRU_KCODPER,
@updPSC_KCODTPR = PRU_KCODTPR,
@updPSC_KCODMIT = PRU_KCODMIT,
@updPSC_NCODTES = PRU_NCODTES,
@updPSC_NCODVAR = PRU_NCODVAR,
@updPSC_NCODDIF = PRU_NCODDIF
FROM sma_PROD_USCITE_TEMP
WHERE PRU_KCODDLO = @DL
AND PRU_KCODPRU = CAST(SUBSTRING(@RIGA,7,8)AS bigint)
AND PRU_KCODRIV = 0;
END
BEGIN
UPDATE sma_PROD_USCITE
SET
PRU_ADESCRI = @updPSC_ADESCRI,
PRU_DATAUSC = @updPSC_DATAUSC, --CONVERT(date, @updPSC_DATAUSC , 104) ,
PRU_NUMUSC = @updPSC_NUMUSC,
PRU_QSCORTA = @updPSC_QSCORTA,
PRU_ABARCODE = @updPSC_ABARCODE,
PRU_ASOTTO = @updPSC_ASOTTO,
PRU_IPRZCOP = @updPSC_IPRZCOP,
PRU_PERCDEF = @updPSC_PERCDEF,
PRU_NCOMPRV = @updPSC_NCOMPRV,
PRU_KCODLVR = @updPSC_KCODLVR,
PRU_KCODEDT = @updPSC_KCODEDT,
PRU_KCODPER = @updPSC_KCODPER,
PRU_KCODTPR = @updPSC_KCODTPR,
PRU_KCODMIT = @updPSC_KCODMIT,
PRU_NCODTES = @updPSC_NCODTES,
PRU_NCODVAR = @updPSC_NCODVAR,
PRU_NCODDIF = @updPSC_NCODDIF,
PRU_PERCSCB = CAST(SUBSTRING(@RIGA,23,8)AS float)/100,
PRU_IPRZNET = ISNULL(CAST(SUBSTRING(@RIGA,31,9)AS float)/10000,0)
WHERE PRU_KCODDLO = @DL
AND PRU_KCODPRU = CAST(SUBSTRING(@RIGA,7,8)AS bigint)
AND PRU_KCODRIV = CAST(SUBSTRING(@RIGA,15,8)AS INT)
END
END
else
BEGIN --INSERT
set @SMAMSG = null
SET @SMAMSG = '14/5 SMA_RICE - Tipo Record PRU INSERT INTO sma_PROD_USCITE file: ' + @FILE + ' riga: ' + @RIGA
INSERT INTO sma_PROD_USCITE (
PRU_KCODDLO, PRU_KCODPRU, PRU_DATAUSC,
PRU_NUMUSC, PRU_QSCORTA, PRU_ABARCODE,
PRU_ASOTTO, PRU_IPRZCOP, PRU_PERCDEF,
PRU_PERCSCB, PRU_NALTEZZ, PRU_NLARGHE,
PRU_NSPESSO, PRU_NPESO, PRU_NCOMPRV,
PRU_KCODLVR, PRU_ADESDIF, PRU_ADESCRI,
PRU_KCODEDT, PRU_KCODPER, PRU_KCODTPR,
PRU_KCODMIT, PRU_NCODTES, PRU_NCODVAR,
PRU_KCODASE, PRU_NCODDIF, PRU_KCODRIV,
PRU_IPRZNET,
PRU_ANOTE, PRU_ABARCODEPV, PRU_RIFERIMENTO)
select PRU_KCODDLO, PRU_KCODPRU, PRU_DATAUSC,
PRU_NUMUSC, PRU_QSCORTA, PRU_ABARCODE,
PRU_ASOTTO, PRU_IPRZCOP, PRU_PERCDEF,
--PRU_PERCSCB--
CAST(SUBSTRING(@RIGA,23,8)AS float)/100, PRU_NALTEZZ, PRU_NLARGHE,
PRU_NSPESSO, PRU_NPESO, PRU_NCOMPRV,
PRU_KCODLVR, PRU_ADESDIF, PRU_ADESCRI,
PRU_KCODEDT, PRU_KCODPER, PRU_KCODTPR,
PRU_KCODMIT, PRU_NCODTES, PRU_NCODVAR,
PRU_KCODASE, PRU_NCODDIF, CAST(SUBSTRING(@RIGA,15,8)AS INT),
ISNULL(CAST(SUBSTRING(@RIGA,31,9)AS float)/10000,0), --PRU_IPRZNET,
PRU_ANOTE, PRU_ABARCODEPV, PRU_RIFERIMENTO
FROM sma_PROD_USCITE_TEMP
WHERE PRU_KCODDLO = @DL
AND PRU_KCODPRU = CAST(SUBSTRING(@RIGA,7,8)AS bigint)
AND PRU_KCODRIV = 0
END
END
Grazie e buon lavoro
Emanuele
