September 5, 2019 at 10:22 am
Hi everyone,
I have a T-SQL procedure that runs on 2 different servers, on the same db, has different execution times:
server 1, not in production, 4 miutes
server 2, in production, 18 minutes
hardware and database equal on both servers
why?
this is the code I added before the problem, I run a .txt file and based on the Type Record I do or I don't
----------- 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
September 5, 2019 at 10:41 am
I have a T-SQL procedure that runs on 2 different servers, on the same db, has different execution times:
server 1, not in production, 4 minutes
server 2, in production, 18 minutes
hardware and database equal on both servers
why?
Due to dozen reasons and here are some of them:
In your case I would detect the slow part (statement) of the code you posted and check it closely.
September 5, 2019 at 10:47 am
ok, thanks. Servers and database ( index, statistics ) are equal. the code has second problems for you?
September 5, 2019 at 11:26 am
the code I posted has problems?
September 5, 2019 at 2:58 pm
the code I posted has problems?
yes the code has problems, since . the code you pasted is incomplete.
for what you pasted, besides having a lot of whitespace so the web site cannot render it visible;
the code as pasted has 15 preceding tab characters on each line, removing it would be easier to read.
anyway the code snippet is incomplete, missing a lot of code above it, since it starts with .
i don't see any glaring issues like functions on a column name in the WHERE clauses, but getting an estimated execution plan might give us something to assist with.
----------- INIZIO 'PRT' -----------
ELSE IF @TIPOREC IN ('PRT')
BEGIN
Lowell
September 6, 2019 at 8:58 am
Every single time I hear "Performance is different, but everything is the same" it always comes around to things not being the same. Don't focus on the code. Focus on finding the difference. The service pack or CU could be different. I know you said the database is the same, but is the data, the statistics? There are so many server settings (cost threshold for parallelism, etc.) database settings, connection settings, a million and one things that could be different. Assuming the same code and the same structures and the same data and the same statistics running on absolutely identical hardware with identical versions and service packs and all the rest, performance should be the same. If it's different, something is different. Go focus on finding it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 6, 2019 at 9:11 am
ok, thanks.
I update you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply