July 2, 2009 at 6:38 am
Hi,
What is a solution to optimize the SUM function in SQL, my query is taking more than 4 minutes .. causing a time out.
thanks!
July 2, 2009 at 7:12 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2009 at 7:43 am
CREATE PROC DBO.PNX_16_EXAMES_IDENTIFICADOR
@tt INT=NULL,@D1 DATETIME=NULL,@D2 DATETIME=NULL,@ARG INT=NULL,@CMBC INT=NULL,@CMBI INT=NULL,@REC INT=NULL,
@CKF INT=NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @T TABLE(CNV INT,SCNV VARCHAR(255),ID_PS INT,SID VARCHAR(255),NCT INT,NOME VARCHAR(255),RF1 VARCHAR(50),DAT DATETIME,VLR MONEY,CEX VARCHAR(100),DAB VARCHAR(255),
SNH VARCHAR(255),VLC MONEY)
IF @CKF=1
BEGIN
INSERT INTO @T
SELECT W.CNV,V.DESCRICAO SCNV,I.ID_PS,I.DESCRICAO SID,K.NCT,F.NOME,P.RF1,P.DAT,
(SELECT SUM(RR.VLC) FROM FATURAMENTO_CONTAS_ITENS RR,PRODUTOS_SERVICOS SS WHERE RR.NCT=K.NCT AND RR.COD=SS.COD AND SS.ID_PS=CASE WHEN @arg=0 THEN SS.ID_PS ELSE @arg END),
S.CEX,S.DAB,R.SNH,IR.VLC
FROM FATURAMENTO_PARAMETROS W,PRODUTOS_SERVICOS_ID I,PRODUTOS_SERVICOS S,FATURAMENTO_CONTAS_ITENS IR,
PACIENTES_REQUISICOES R,FATURAMENTO_CONTAS K,PACIENTES P,EORG V,PESSOA_FISICA F
WHERE R.CHV=IR.CHV AND IR.NCT=K.NCT AND K.PAC=P.PAC AND P.FTP=W.FTP AND R.COD=S.COD AND S.ID_PS=I.ID_PS AND R.ID_COB3
AND W.CNV=V.EORG AND P.NPF=F.NPF AND P.DAT>=@D1 AND P.DAT<@D2 AND K.VLR IS NOT NULL AND W.REC=@REC
AND W.CNV=CASE WHEN @CMBC=0 THEN W.CNV ELSE @CMBC END
AND S.ID_PS=CASE WHEN @CMBI=0 THEN S.ID_PS ELSE @CMBI END
END
ELSE IF @CKF1
BEGIN
INSERT INTO @T
SELECT W.CNV,V.DESCRICAO SCNV,I.ID_PS,I.DESCRICAO SID,K.NCT,F.NOME,P.RF1,P.DAT,
(SELECT SUM(RR.VLC) FROM FATURAMENTO_CONTAS_ITENS RR,PRODUTOS_SERVICOS SS WHERE RR.NCT=K.NCT AND RR.COD=SS.COD AND SS.ID_PS=CASE WHEN @arg=0 THEN SS.ID_PS ELSE @arg END),
S.CEX,S.DAB,R.SNH,IR.VLC
FROM FATURAMENTO_PARAMETROS W,PRODUTOS_SERVICOS_ID I,PRODUTOS_SERVICOS S,FATURAMENTO_CONTAS_ITENS IR,
PACIENTES_REQUISICOES R,FATURAMENTO_CONTAS K,PACIENTES P,EORG V,PESSOA_FISICA F
WHERE R.CHV*=IR.CHV AND R.NCT=K.NCT AND K.PAC=P.PAC AND P.FTP=W.FTP AND R.COD=S.COD AND S.ID_PS=I.ID_PS AND R.ID_COB3
AND W.CNV=V.EORG AND P.NPF=F.NPF
AND W.CNV=CASE WHEN @CMBC=0 THEN W.CNV ELSE @CMBC END
AND S.ID_PS=CASE WHEN @CMBI=0 THEN S.ID_PS ELSE @CMBI END
AND P.DAT>=@D1 AND P.DAT<@D2 AND K.VLR IS NULL AND W.REC=@REC
END
SELECT CNV,SCNV,ID_PS,SID,NCT,NOME,RF1,DAT,VLR,CEX,DAB,SNH,VLC,
(SELECT SUM(YY.VLC) FROM @T YY WHERE YY.CNV=Y.CNV AND YY.ID_PS=Y.ID_PS) TOTID,
(SELECT SUM(YY.VLC) FROM @T YY WHERE YY.CNV=Y.CNV) TOT
FROM @T Y
July 2, 2009 at 7:46 am
Table definitions, index definitions and execution plan please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2009 at 9:21 am
I have taken the time to rewrite your stored procedure a bit and you may want to see if it returns the same data as yours. Not having anything to test it against, I can't make any promises. I do think it is easier to understand and uses ANSI Standard JOIN syntax.
There are other changes I'd make as well.
1. Change from a table variable to a temporary table
2. Move each of the queries in the IF ELSE block to separate stored procedures
3. Use the INSERT ... EXEC storedproc in the IF ELSE block instead to populate the temporary table
(Can't do that with a table variable in SQL Server 2000 or SQL Server 2005)
Here is my code:
CREATE PROC DBO.PNX_16_EXAMES_IDENTIFICADOR
@tt INT = NULL,
@D1 DATETIME = NULL,
@D2 DATETIME = NULL,
@arg INT = NULL,
@CMBC INT = NULL,
@CMBI INT = NULL,
@REC INT = NULL,
@CKF INT = NULL
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @T TABLE(
CNV INT,
SCNV VARCHAR(255),
ID_PS INT,
SID VARCHAR(255),
NCT INT,
NOME VARCHAR(255),
RF1 VARCHAR(50),
DAT DATETIME,
VLR MONEY,
CEX VARCHAR(100),
DAB VARCHAR(255),
SNH VARCHAR(255),VLC MONEY
)
IF @CKF = 1
BEGIN
INSERT INTO @T
SELECT
W.CNV,
V.DESCRICAO SCNV,
I.ID_PS,
I.DESCRICAO SID,
K.NCT,
F.NOME,
P.RF1,
P.DAT,
-- ( SELECT
-- SUM(RR.VLC)
-- FROM
-- FATURAMENTO_CONTAS_ITENS RR
-- INNER JOIN PRODUTOS_SERVICOS SS
-- ON (RR.COD = SS.COD)
-- WHERE
-- RR.NCT = K.NCT AND
-- SS.ID_PS = CASE WHEN @arg = 0 THEN SS.ID_PS ELSE @arg END),
S.CEX,
S.DAB,
R.SNH,
IR.VLC,
SUM(RR.VLC)
FROM
FATURAMENTO_PARAMETROS W
INNER JOIN EORG V
ON (W.CNV = V.EORG)
INNER JOIN PACIENTES P
ON (W.FTP = P.FTP)
INNER JOIN FATURAMENTO_CONTAS K
ON (P.PAC = K.PAC)
INNER JOIN PESSOA_FISICA F
ON (P.NPF = F.NPF)
INNER JOIN FATURAMENTO_CONTAS_ITENS IR
ON (K.NCT = IR.NCT)
INNER JOIN PACIENTES_REQUISICOES R
ON (IR.CHV = R.CHV)
INNER JOIN PRODUTOS_SERVICOS S
ON (R.COD = S.COD)
INNER JOIN PRODUTOS_SERVICOS_ID I
ON (S.ID_PS = I.ID_PS)
INNER JOIN FATURAMENTO_CONTAS_ITENS RR
ON (RR.NCT = K.NCT)
INNER JOIN PRODUTOS_SERVICOS SS
ON (RR.COD = SS.COD)
WHERE
R.ID_COB 3 AND
P.DAT >= @D1 AND
P.DAT < @D2 AND
K.VLR IS NOT NULL AND
W.REC = @REC AND
W.CNV = CASE WHEN @CMBC = 0 THEN W.CNV ELSE @CMBC END AND
S.ID_PS = CASE WHEN @CMBI = 0 THEN S.ID_PS ELSE @CMBI ENDAND
SS.ID_PS = CASE WHEN @arg = 0 THEN SS.ID_PS ELSE @arg END
GROUP BY
W.CNV,
V.DESCRICAO,
I.ID_PS,
I.DESCRICAO,
K.NCT,
F.NOME,
P.RF1,
P.DAT,
S.CEX,
S.DAB,
R.SNH,
IR.VLC
END
ELSE IF @CKF1
BEGIN
INSERT INTO @T
SELECT
W.CNV,
V.DESCRICAO SCNV,
I.ID_PS,
I.DESCRICAO SID,
K.NCT,
F.NOME,
P.RF1,
P.DAT,
-- ( SELECT
-- SUM(RR.VLC)
-- FROM
-- FATURAMENTO_CONTAS_ITENS RR
-- INNER JOIN PRODUTOS_SERVICOS SS
-- ON (RR.COD = SS.COD)
-- WHERE
-- RR.NCT = K.NCT AND
-- SS.ID_PS = CASE WHEN @arg = 0 THEN SS.ID_PS ELSE @arg END),
S.CEX,
S.DAB,
R.SNH,
IR.VLC,
SUM(RR.VLC)
FROM
FATURAMENTO_PARAMETROS W
INNER JOIN EORG V
ON (W.CNV = V.EORG)
INNER JOIN PACIENTES P
ON (W.FTP = P.FTP)
INNER JOIN PESSOA_FISICA F
ON (P.NPF = F.NPF)
INNER JOIN FATURAMENTO_CONTAS K
ON (P.PAC = K.PAC)
INNER JOIN PACIENTES_REQUISICOES R
ON (K.NCT = R.NCT)
INNER JOIN PRODUTOS_SERVICOS S
ON (R.COD = S.COD)
INNER JOIN PRODUTOS_SERVICOS_ID I
ON (S.ID_PS = I.ID_PS)
INNER JOIN FATURAMENTO_CONTAS_ITENS RR
ON (K.NCT = RR.NCT)
INNER JOIN JOIN PRODUTOS_SERVICOS SS
ON (RR.COD = SS.COD)
LEFT OUTER JOIN FATURAMENTO_CONTAS_ITENS IR
ON (R.CHV = IR.CHV)
WHERE
R.ID_COB 3 AND
W.CNV = CASE WHEN @CMBC=0 THEN W.CNV ELSE @CMBC END AND
S.ID_PS = CASE WHEN @CMBI=0 THEN S.ID_PS ELSE @CMBI END AND
P.DAT >= @D1 AND
P.DAT < @D2 AND
K.VLR IS NULL AND
W.REC = @REC AND
SS.ID_PS = CASE WHEN @arg = 0 THEN SS.ID_PS ELSE @arg END
GROUP BY
W.CNV,
V.DESCRICAO,
I.ID_PS,
I.DESCRICAO,
K.NCT,
F.NOME,
P.RF1,
P.DAT,
S.CEX,
S.DAB,
R.SNH,
IR.VLC
END
SELECT
CNV,
SCNV,
ID_PS,
SID,
NCT,
NOME,
RF1,
DAT,
VLR,
CEX,
DAB,
SNH,
VLC,
SUM(Y1.VLC) TOTID,
SUM(Y2,VLC) TOT
-- ( SELECT
-- SUM(YY.VLC)
-- FROM
-- @T YY
-- WHERE
-- YY.CNV = Y.CNV AND
-- YY.ID_PS = Y.ID_PS) TOTID,
-- ( SELECT
-- SUM(YY.VLC)
-- FROM
-- @T YY
-- WHERE
-- YY.CNV=Y.CNV) TOT
FROM
@T Y
INNER JOIN @T Y1
ON (Y.CNV = Y1.CNV
AND Y.ID_PS = Y1.PS)
INNER JOIN @T Y2
ON (Y.CNV = Y2.CNV)
GROUP BY
CNV,
SCNV,
ID_PS,
SID,
NCT,
NOME,
RF1,
DAT,
VLR,
CEX,
DAB,
SNH,
VLC
END
July 4, 2009 at 12:51 pm
Also don't use table variables for this.
The minimum amount of data read by SQL Server is one page (8k).
As long as the amount of data is less than one page, there is really no difference between a table variable and temp table.
But when the data exceeds one page, using a table variable can lead to really unpredicted results.
Why? There is no statistics on a table variable so the optimizer always, ALWAYS, assumes there is one record (and one record only) in the table variable, no matter the real count!
N 56°04'39.16"
E 12°55'05.25"
July 4, 2009 at 3:07 pm
luan.wp (7/2/2009)
Hi,What is a solution to optimize the SUM function in SQL, my query is taking more than 4 minutes .. causing a time out.
thanks!
Looks like you've had some time to review the posts on this thread, so how about so feedback? What's going on? Do you need some help clarifying anything?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply