Link to home
Start Free TrialLog in
Avatar of JnavarroMc
JnavarroMcFlag for Peru

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?


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

Open in new window


I'd really appreciate your help!
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JnavarroMc

ASKER

I was trying to optimize this Query (Original - was trying to replace the "IN" with "EXISTS")

ORIGINAL:

ALTER PROCEDURE [dbo].[st_pro]   
@sistema int,  
@aplicativo int,  
@codi_enc int,  
@codi_usu int    
As  

SELECT DISTINCT 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)
AND per.CODI_PFL IN
    (SELECT CODI_PFL
     FROM SEG_ENCARGATURA_PERFIL
     WHERE CODI_USU = @codi_usu)
  AND eapp.CODI_USU=@codi_usu
ORDER BY per.vNOMB_PFL

Open in new window



NEW:

ALTER PROCEDURE [dbo].st_pro13   
@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)---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

Open in new window


I moved some operators to Where and I don't have the warning anymore.
AND opi.CODI_SIS = api.CODI_SIS
  AND api.codi_apl=eapp.[CODI_APL]

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>> 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..
I've already been able to make the changes.

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

Open in new window


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.

Open in new window



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

Open in new window


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.

Open in new window


both Query's work but EXISTS generates less logical reads.

Thanks!!
Thanks!
Pleased you got there. Cheers, Paul