Query Performance.

[Warrio]

Hello!

Can you tell me which one of these queries is the best to get the quickest
result?



Q1= "SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Field1=Table2.Field1
WHERE Table2.Field2 ='hello' "



Q2= "SELECT * FROM Table1 INNER JOIN (SELECT Table2.* FROM Table2 WHERE
Table2.Field2='hello') AS Table2 ON Table1.Field1=Table2.Field1





The only syntax diffrence in Q2 is that the WHERE clause is first excectued
on the Table2 and then the result is joined to Table1. Whereas Q1 links
first the two tables and then excutes the filter on the result.

 

[Allen Browne]

The only way to know for sure will be to run some timing test, on your
particlar data, with the actual numbers of records you anticipate in each
table, with the indexes you will use.

Let us know what you find. I'll stick my neck out and go for Q1.

 

[giorgio rancati]

Hi Warrio,

This is the showplan.out of Q1
----
01) Restrict rows of table Table2
by scanning
testing expression "Table2.Field2='hello'"
02) Inner Join result of '01)' to table 'Table1'
using index 'Table1!PrimaryKey'
join expression "Table2.Field1=Table1.Field1"