IS My Query Affecting Performance?

Question

question

GautamRy asked Mar 16, '10

IS My Query Affecting Performance?

1)

select  tblProcessProfile.prpf_value                     
from tblProcessProfile,tblprocess, tblProfile                     
where tblProcessProfile.prpf_pr_pid=tblprocess.pr_pid                     
and tblProcessProfile.prpf_pr_pid in                     
  (                    
    select pr_pid                     
    from tblprocess                     
    where pr_parent_pr_pid in (19696)                    
  )                     
and  tblProcessProfile.prpf_prf_pid=tblProfile.prf_pid                     
and  tblProcessProfile.prpf_prf_pid in                     
  (                    
      select prf_pid                     
      from tblProfile                     
       where prf_name like 'file_naming_convention'                    
   )

2)

select tblProcessProfile.prpf_value                     
from tblProcessProfile                     
inner join  tblprocess on  tblProcessProfile.prpf_pr_pid=tblprocess.pr_pid                      
inner join tblProfile on tblProcessProfile.prpf_prf_pid=tblProfile.prf_pid                     
where tblProcessProfile.prpf_pr_pid in                     
  (                    
     select pr_pid                     
     from tblprocess                     
     where     pr_parent_pr_pid in (19696)                    
  )                     
and tblProcessProfile.prpf_prf_pid in                     
   (                    
      select prf_pid                     
      from tblProfile where prf_name                     
      like 'file_naming_convention'                    
   )

The above two queries give the same output.
In Query (1), I didn't use any inner join. In Query (2), I used inner join. Now my question is does inner join improve performance?

performance joins

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-16T09:46:02Z

graz answered Mar 16, '10

I find very rare situations where writing inner joins can lead to a better query plan. You should be able to compare the query plans of the two queries and see if they are different.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-03-16T16:23:43Z

Brian Perrin answered Mar 16, '10

you're joining each table twice, which you don't need to do. You can do:

select distinct tblProcessProfile.prpf_value from tblProcessProfile inner join tblprocess on tblProcessProfile.prpf_pr_pid=tblprocess.pr_pid inner join tblProfile on tblProcessProfile.prpf_prf_pid=tblProfile.prf_pid where pr_parent_pr_pid in (19696) and prf_name like 'file_naming_convention

Notice that I assumed you probably want "select distinct ...".

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-03-16T21:06:16Z

JohnFx answered Mar 16, '10

Why guess? Turn on statistics and run it both ways.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question