Comparison of two sql queries

Question

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.

If you want to know which one is faster, you can view the execution time in your SQL client — parker.sikand, Nov 29 '12 at 06:47
You can also check the `EXPLAIN` command for each query, to show the steps that the database takes to execute your query. Just put the `EXPLAIN` keyword before the query. — parker.sikand, Nov 29 '12 at 06:48
EXPLAIN does not exist in SQL Server 2008. Refer to this post: http://stackoverflow.com/questions/3449814/sql-server-equivelant-to-msqsls-explain — parker.sikand, Nov 29 '12 at 06:55
my experience with open source DBs, namely MySQL and PostgreSQL would say that the second query is faster. But this may be different for MS SQL Server 2008. You should be able to find the answer yourself with your tables and execution plan. There is no general SQL answer to your question... — parker.sikand, Nov 29 '12 at 06:57
@parker.sikand not ot mention this also depends on the data - the optimizer will behave totally different with different amount of rows involved... — ppeterka, Nov 29 '12 at 07:47

score 1 · Accepted Answer · answered Nov 30 '12 at 07:49

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.

score 0 · Answer 2 · answered Nov 30 '12 at 07:21

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

score 0 · Answer 3 · answered Dec 12 '12 at 18:13

0

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

answered Dec 12 '12 at 18:13

Eugene Roeder

219
2
7

Comparison of two sql queries

3 Answers3