Will there be any performance difference between these 2 Queries?

Question

1)

select * from student_tbl A ,result_tbl B where
A.student_name = B.student_name and
A.student_name = "xyz" ;

2)

select * from student_tbl A ,result_tbl B where
A.student_name = "xyz" and
A.student_name = B.student_name ;

I know that the result of these queries are the same. Is there any performance difference? If so, please explain why.

Answer 1

Queries are not executed like programs. They are not procedures which do step 1 and then step 2. Instead, they are declarative statements about what results you want. In most modern RDBMS's any given query can be executed via a number of different query plans. Generally, different query plans are created and then evaluated for which plan will run fastest. In creating the set of query plans, it will consider things like which conditions should be evaluated first, whether joins should be done before or after evaluating conditions and other things like that to try to figure out which will be fastest (based on its knowledge of the table sizes and guesses about what percentage of a table will be included in a given condition). Many of them also look at previous results to inform future decisions for when their approximations are wrong.

Most likely, in any modern RDBMS, those two queries would generate the same set of query plans, and hence the same choice would be made, resulting in the same query plan being executed for both queries. Depending on which RDBMS you are using, there are generally tools available to look at the particular query plans which are being selected for a given query, so you can use that to answer the question absolutely for two specific queries on a particular database.

Now, saying that, I should note that this is not equivalent to saying "Any two queries which will always produce the same answer on the same data will always take the same amount of time." It is possible to write really bad queries, mostly through needless complexity, and there's no guarantee that the query planner will realize that you've overdone it. It will probably catch simple cases. So, for instance:

SELECT * FROM student_tbl A, result_tbl B WHERE 
A.student_name = B.student_name AND
A.student_name = 'xyz' AND
B.student_name = A.student_name

will also probably produce the same query plan. And this is also likely to:

SELECT * FROM student_tbl A, result_tbl B WHERE 
A.student_name = B.student_name AND
A.student_name = 'xyz' AND
B.student_name = 'xyz'

But if you do something really complex like

(SELECT * FROM student_tbl A, result_tbl B WHERE
 A.student_name = B.student_name AND
 A.student_name = 'xyz')
UNION
(SELECT * FROM student_tbl A, result_tbl B WHERE
 A.student_name = B.student_name AND
 B.student_name = 'xyz')
INTERSECT
(SELECT * FROM student_tbl A, result_tbl B WHERE
 A.student_name = 'xyz')

It may run a more complex query plan. (Even though, that completely unnecessarily complex query will produce the same results as the other two (assuming no NULLs)).

So, the optimizers are not omniscient, but they do tend to recognize that X AND Y is the same thing as Y AND X and that A=B AND B=C is the same thing as A=C and A=B and adjust accordingly for those cases. They actually do a variety of transformations to try to find the best query, and are generally quite good at finding it. It is possible to override the decisions of the query planner, but that should only be done when you're certain that there's a better way to do the query and that data changes aren't likely to change that.

Answer 2

Assuming you are using Sql server you can display an execution plan for each and see what is actually happening, this will show you the cost for each operation and what the query actually does to each table.

For a less in depth look you could also just run the queries and check the execution time.

I suspect the real question here is "Does the order of conditions in the where clause affect performance?" in this case you may wish to read this SO post Does the order of columns in a WHERE clause matter?

Answer 3

These are the same. The condition in the where clause means both A.student_name and B.student_name are "xyz". It's more than likely that the query optimizer will generate the same execution plan for both, but you can check this by examining the execution plan (for example in SQL Server Management Studio, if you use MS SQL Server).