CODEWITHSUNDEEP
sqlsql-server
Stivan
Stivan

Reputation: 1127

SQL Performance between two queries

I have two queries which output the same result. I would like to know if there is a difference in performance of the two and which one is better to use. Please provide an explanation why one is better than the other, or if they are the same why?

Query2 is 12 ms faster than Query1 but that could be result of random server delay. For example if run it again I get different times for each.

Query 1: Time 66ms

select      
    *
from        
    table1, 
    table2,
    table3,
    table4,
    table5,
    table6,
    table7
where       
    table1.type1 = 402 and
    table1.pid = table2.pid and
    table1.aid = table3.aid and
    table1.tid = table4.tid and
    table4.type2 = table5.code and
    table1.status = table6.code2 and
    table7.eaid = table1.caid

Query 2: Time - 54ms

select      
    *
from 
    table1
        join table2 on table2.pid= table1.pid
        join table3 on table1.aid = table3.aid
        join table4 on table1.tid = table4.tid
        join table5 on table4.type2= table5.code
        join table6 on table1.status= table6.code2
        join table7 on table7.eaid= table1.caid
where
    table1.type1 = 402

Upvotes: 1

Views: 62

Answers (1)

Martin Smith
Martin Smith

Reputation: 453940

There is no inherent performance difference between either of those queries.

Chances are that they will get the exact same plan.

It is possible that they might not though, different starting positions might cause a different plan to be found first and determined to be "good enough" but that will be just down to luck not anything inherent in the syntax.

You should prefer the second option though. It is easy in the first option to miss a join condition and get an accidental cross join. Also it is easier to convert to an outer join should you decide the semantics aren't quite correct.

Upvotes: 2

Related Questions