July 21, 2009 at 6:19 am
can someone help me optimize this query?
SELECT C.NPG,N.DEM,N.DEN,C.DVC,C.DPG,N.VNE,C.VTT
C.CMT,C.NTT,L.DRF,C.CRD,C.LPG,C.RPG,P.NOME,C.DSY,C.ST,C.LEST,
CASE @RZS_NF WHEN 0 THEN ISNULL(F.RZS,R.RZS) WHEN 1 THEN E.DESCRICAO END DESCRICAO,
Z.DESCRICAO NATUREZA,C.VIRRF,C.VINSS,C.VPIS,C.VCOFINS,C.VCSLL,C.VISS,C.VPCC,(ISNULL(C.VLD,0)+ISNULL(C.GLS,0)+ISNULL((SELECT SUM (CC.VTT) FROM CONTAS_PAGAR CC WHERE CC.NPG_ADIANT=C.NPG),0)) DESCONTO,
((DATEDIFF(DAY,ISNULL(L.DRF,0),ISNULL(C.DPG,0))*ISNULL(C.JMR,0))+ISNULL(C.MLT,0)+ISNULL(C.TXC,0)) ACRE,
(C.VTT-(ISNULL(C.VLD,0)+ISNULL(C.GLS,0)+ISNULL(C.VIRRF,0)+ISNULL(C.VINSS,0)+ISNULL(C.VPIS,0)+ISNULL(C.VCOFINS,0)+ISNULL(C.VCSLL,0)+ISNULL(C.VISS,0)+ISNULL(C.VPCC,0))+
((DATEDIFF(DAY,ISNULL(L.DRF,0),ISNULL(C.DPG,0))*ISNULL(C.JMR,0))+ISNULL(C.MLT,0)+ISNULL(C.TXC,0))) VPGLIQ,
C.NPL,(SELECT COUNT(*) FROM CONTAS_PAGAR PP WHERE PP.NE=C.NE GROUP BY PP.NE) PARC
FROM EORG E,PESSOA_FISICA P,NOTA_ENTRADA_NATUREZAS Z,CONTAS_PAGAR C
LEFT JOIN LANCAMENTOS_CONTABEIS L ON C.LPG=L.NLC,
NOTA_ENTRADA N
LEFT JOIN FORNECEDORES F ON N.FRN=F.EORG
LEFT JOIN RECURSOS R ON N.FRN=R.EORG
WHERE C.EMP=@REC AND E.EORG=C.CRD AND N.NE=C.NE AND P.NPF=C.USR AND C.DVC>@de AND C.DVC<@ATE AND ISNULL(C.ID_SIT,1)=1 AND Z.NZE=N.NZE
AND N.FRN=CASE WHEN @FRN=0 THEN N.FRN ELSE @FRN END
[/code]
tkss [ ]'s
July 21, 2009 at 6:25 am
Please see this thread for what is required from you
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 21, 2009 at 11:15 am
You've got a mixed set of ANSI & non-ANSI joins (some are "tableA, tableB" and some are "TableA JOIN TableB ON"). First thing I'd do is make them all ANSI consistent (that's the one using the JOIN syntax). It'll make it easier to read and troubleshoot.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 21, 2009 at 11:22 am
While your at it, please format the code so that it is easier to read as well. White space is a freind to the eyes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply