Query Performance

M

MoinJ

I have 2 tables, Make and Model where Make is the master table and Model has
a reference of make table by Make_Id since there can be multiple models for a
single make. Now, i have 4 SQL's below and i want to know which SQL should
perform better.

select make.make_name, model.model_name
from make, model
where make.make_id = 1000
and make.make_id = model.make_id


select make.make_name, model.model_name
from make, model
where make.make_id = 1000
and model.make_id = make.make_id


select make.make_name, model.model_name
from make, model
where make.make_id = model.make_id
and make.make_id = 1000


select make.make_name, model.model_name
from make, model
where model.make_id = make.make_id
and make.make_id = 1000

Thanks,
Moin
 
J

John Vinson

I have 2 tables, Make and Model where Make is the master table and Model has
a reference of make table by Make_Id since there can be multiple models for a
single make. Now, i have 4 SQL's below and i want to know which SQL should
perform better.
Click to expand...

How about none of the above? If you're using Access (this isn't
typical Access SQL so I have to wonder) you may have better luck using
a SQL/92 JOIN expression rather than the WHERE clause join:

select make.make_name, model.model_name
from make INNER JOIN model
ON make.make_id = model.make_id
where make.make_id = 1000;

If Make_ID is indexed (uniquely in MAKE, nonuniquely in MODEL) this
should work instantly unless you have hundreds of thousands of
records.

John W. Vinson[MVP]
 
M

MoinJ

ok, i just want to know if the response time will really change dramatically
using that "Access SQL" instead of the more standardized SQL?
 
D

Dirk Goldgar

MoinJ said:
ok, i just want to know if the response time will really change
dramatically using that "Access SQL" instead of the more standardized
SQL?
Click to expand...

Um, the JOIN syntax *is* standard SQL.
 
J

John Vinson

ok, i just want to know if the response time will really change dramatically
using that "Access SQL" instead of the more standardized SQL?
Click to expand...

The ANSI SQL-92 standard, specifying INNER or OUTER JOIN instead of
the WHERE clause joins, was issued in <duh!> 1992. It is the
"official" standard join method for SQL queries.

Some vendors (Oracle frex) have been a bit slow to come around to this
standard and still use the older SQL-88 standard.

And yes, in my experience, the preformance will be noticably faster on
large recordsets.

John W. Vinson[MVP]
 
Top