JnavarroMc
asked on
No Join predicate warning
Hi!
I was trying to review the execution plan of a Query and saw the Nested Loop parameter "No join predicate" . How could I correct this Warning?
I'd really appreciate your help!
I was trying to review the execution plan of a Query and saw the Nested Loop parameter "No join predicate" . How could I correct this Warning?
CREATE PROCEDURE [dbo].prd_3
@sistema int,
@aplicativo int,
@codi_enc int,
@codi_usu int
As
SELECT per.codi_pfl AS 'ID',
per.vNOMB_PFL AS 'DESCRIPCION',
api.vNOMB_APL
FROM SEG_PERFIL AS per
INNER JOIN SEG_PERFIL_OPCION AS opc ON per.CODI_PFL = opc.CODI_PFL
INNER JOIN SEG_OPCION AS opi ON opi.CODI_OPC = opc.CODI_OPC
INNER JOIN SEG_APLICACION AS api ON api.CODI_APL = opi.CODI_APL
AND opi.CODI_SIS = api.CODI_SIS
INNER JOIN SEG_ENCARGATURA_APLICACION eapp ON api.codi_sis =eapp.CODI_SIS
AND api.codi_apl=eapp.[CODI_APL]
WHERE api.CODI_SIS = @sistema
AND api.codi_apl= @aplicativo
AND eapp.CODI_ENC=@codi_enc
AND per.codi_pfl NOT IN (148,
158,
159)---perfil no a listar
AND EXISTS
(SELECT 1
FROM SEG_ENCARGATURA_PERFIL
inner join SEG_PERFIL ON
per.CODI_PFL = SEG_ENCARGATURA_PERFIL.CODI_PFL
WHERE CODI_USU = @codi_usu)
AND eapp.CODI_USU=@codi_usu
GROUP BY
per.codi_pfl,
per.vNOMB_PFL,
api.vNOMB_APL
ORDER BY per.vNOMB_PFL
GO
I'd really appreciate your help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> PLEASE, do NOT use "inner join" inside the EXISTS:
PortletPaul, pls check the revised code I've shared along with the comment and comment accordingly..
PortletPaul, pls check the revised code I've shared along with the comment and comment accordingly..
ASKER
I've already been able to make the changes.
Option 1:
Logical Reads:
Op2:
both Query's work but EXISTS generates less logical reads.
Thanks!!
Option 1:
ALTER PROCEDURE [dbo].st_pro1
@sistema int,
@aplicativo int,
@codi_enc int,
@codi_usu int
As
SELECT per.codi_pfl AS 'ID',
per.vNOMB_PFL AS 'DESCRIPCION',
api.vNOMB_APL
FROM SEG_PERFIL AS per
INNER JOIN SEG_PERFIL_OPCION AS opc ON per.CODI_PFL = opc.CODI_PFL
INNER JOIN SEG_OPCION AS opi ON opi.CODI_OPC = opc.CODI_OPC
INNER JOIN SEG_APLICACION AS api ON api.CODI_APL = opi.CODI_APL
INNER JOIN SEG_ENCARGATURA_APLICACION eapp ON api.codi_sis =eapp.CODI_SIS
WHERE api.CODI_SIS = @sistema
AND api.codi_apl= @aplicativo
AND opi.CODI_SIS = api.CODI_SIS
AND api.codi_apl=eapp.[CODI_APL]
AND eapp.CODI_ENC=@codi_enc
AND per.codi_pfl NOT IN (148,
158,
159)
AND EXISTS
(SELECT 1
FROM SEG_ENCARGATURA_PERFIL
WHERE CODI_USU = @codi_usu
and per.CODI_PFL = SEG_ENCARGATURA_PERFIL.CODI_PFL)
AND eapp.CODI_USU=@codi_usu
GROUP BY
per.codi_pfl,
per.vNOMB_PFL,
api.vNOMB_APL
ORDER BY per.vNOMB_PFL
Logical Reads:
Table 'SEG_ENCARGATURA_APLICACION'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SEG_PERFIL_OPCION'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SEG_PERFIL'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SEG_ENCARGATURA_PERFIL'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SEG_OPCION'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SEG_APLICACION'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Op2:
ALTER PROCEDURE [dbo].st_pro2
@sistema int,
@aplicativo int,
@codi_enc int,
@codi_usu int
As
SELECT per.codi_pfl AS 'ID',
per.vNOMB_PFL AS 'DESCRIPCION',
api.vNOMB_APL
FROM SEG_PERFIL AS per
INNER JOIN SEG_PERFIL_OPCION AS opc ON per.CODI_PFL = opc.CODI_PFL
INNER JOIN SEG_OPCION AS opi ON opi.CODI_OPC = opc.CODI_OPC
INNER JOIN SEG_APLICACION AS api ON api.CODI_APL = opi.CODI_APL
INNER JOIN SEG_ENCARGATURA_APLICACION eapp ON api.codi_sis =eapp.CODI_SIS
Inner join SEG_ENCARGATURA_PERFIL iap ON per.CODI_PFL = iap.CODI_PFL
and iap.CODI_USU = @codi_usu
WHERE api.CODI_SIS = @sistema
AND api.codi_apl= @aplicativo
AND opi.CODI_SIS = api.CODI_SIS
AND api.codi_apl=eapp.[CODI_APL]
AND eapp.CODI_ENC=@codi_enc
AND per.codi_pfl NOT IN (148,
158,
159)
AND eapp.CODI_USU=@codi_usu
GROUP BY
per.codi_pfl,
per.vNOMB_PFL,
api.vNOMB_APL
ORDER BY per.vNOMB_PFL
Table 'SEG_ENCARGATURA_APLICACION'. Scan count 13, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SEG_PERFIL_OPCION'. Scan count 4, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SEG_PERFIL'. Scan count 0, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SEG_ENCARGATURA_PERFIL'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SEG_OPCION'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SEG_APLICACION'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
both Query's work but EXISTS generates less logical reads.
Thanks!!
ASKER
Thanks!
Pleased you got there. Cheers, Paul
ASKER
ORIGINAL:
Open in new window
NEW:
Open in new window
I moved some operators to Where and I don't have the warning anymore.
Open in new window
Is this change also valid?
I'll keep making changes! I look forward to hearing from you.
Thank you very much.
originalquery10.sqlplan
modify13.sqlplan