CODEWITHSUNDEEP
sqlsql-server-2008
Vishwajeet
Vishwajeet

Reputation: 1683

Comparison of two sql queries

I have two sql queries as following

SELECT rc.stateId,rs.stateName FROM
(SELECT DISTINCT cityid FROM HolidayPackageCity) AS hpc
 INNER JOIN 
 (SELECT cityid,stateid FROM RegCity WHERE countryCode='IN' AND stateId IS NOT NULL) AS rc
ON hpc.cityId=rc.cityId
INNER JOIN
RegState AS rs
ON rc.stateId=rs.stateId

vs

SELECT DISTINCT rc.stateId,rs.stateName 
FROM HolidayPackageCity AS hpc
INNER JOIN 
  RegCity  AS rc
  ON hpc.cityId=rc.cityId
  INNER JOIN
    RegState AS rs
    ON rc.stateId=rs.stateId
WHERE rc.countryCode='IN' AND rc.stateId IS NOT NULL

In first query first i filter the data of a particular table then apply joining and in second table first i apply joins then i apply where condition to filter the data. The thing i want to know is which one is faster from both of that and why.

Upvotes: 0

Views: 171

Answers (3)

Eugene Roeder
Eugene Roeder

Reputation: 219

General rule is that sub queries will be slower especially if joins are using proper indexes.

Upvotes: 0

veljasije
veljasije

Reputation: 7092

Second query is faster, because optimizer would first filter table with where clause, and then create internal temp table which later use for join. Conclusion - better solution is join with tables which contains smaller data sets.

By the way, optimizer easier deal with second query, which no contains subqueries.

Upvotes: 1

Bernhard Barker
Bernhard Barker

Reputation: 55609

You can display the execution plan in SQL Server 2008. There is a button there somewhere. Just put both queries in the same window and it will tell you which % of the time was spent on each. The correctness of the results probably depend on the queries actually taking more than a millisecond or two.

I believe the second query gives the optimiser much more freedom, while the first one forces it into some possibly inefficient path (but it can also force it into a more efficient path that the optimiser wouldn't try). I'm pretty sure the difference in efficiency would depend on the table layout, though I'd doubt the optimised query would be much less efficient for such a simple query.

Note that (according to me) the big thing about SQL is not telling it how to do things, and just let the optimiser handle it, so the second query is preferred (although the optimiser doesn't always do a good job, so I'd suggest just letting the optimiser handle it, do some stress testing and see if things are slow, maybe using a profiler to identify the problematic queries, and force it into a more efficient path).

Upvotes: 0

Related Questions