x

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?

more ▼

asked Mar 16, 2010 at 04:51 AM in Default

avatar image

GautamRy
2 2 2 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Mar 16, 2010 at 09:46 AM

avatar image

graz ♦
525 2 6 10

(comments are locked)
10|1200 characters needed characters left

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 ...".

more ▼

answered Mar 16, 2010 at 04:23 PM

avatar image

Brian Perrin
11

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 16, 2010 at 09:06 PM

avatar image

JohnFx
84 3 2 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x311
x162

asked: Mar 16, 2010 at 04:51 AM

Seen: 859 times

Last Updated: Mar 16, 2010 at 09:45 AM

Copyright 2017 Redgate Software. Privacy Policy