Performance Enhancing in SQL 2000 Opinion

[dj shane]

Hey guys, I was told specifying dbo.tablename for each field in a query could
enhance performance. Even though I assume this would have more impact on larger
queries, I've given this theory a shot with the below two queries and turned on
DEBUG in CFADMIN. I don't know a difference, both queries are taking 15ms to
run.

Do you all follow the practice of owner.tablename.field?



<cfquery name="test" datasource="#application.dsn#">
SELECT d.Department_ID,
d.Lob_ID,
e.ID,
e.First_Name,
e.Last_Name
FROM Lob_Department_TBL d
INNER JOIN Employees e ON d.Department_ID = e.Dept_ID
WHERE d.Lob_ID = 2
ORDER BY e.First_Name
</cfquery>

<cfquery name="test2" datasource="#application.dsn#">
SELECT dbo.lob_department_tbl.Department_ID,
dbo.lob_department_tbl.lob_ID,
dbo.employees.ID,
dbo.employees.first_name,
dbo.employees.last_name
FROM Lob_Department_TBL
INNER JOIN Employees ON dbo.lob_department_tbl.Department_ID =
dbo.employees.dept_ID
WHERE dbo.lob_department_tbl.lob_ID = 2
ORDER BY dbo.employees.first_name
</cfquery>

[SafariTECH]

doesn't actually matter in any application we have created

you can get a slight performance boost by calling columns in the same order
they appear in the DB, and you can get a substantial boost (depending on query
sizes) if you cache the queries that are always the same for everyone over a
period of time using the cachedWithin attribute.

[mxstu]

Honestly, I never use "owner." unless it is required. IMO, unless your
reference or article can prove there is a measurable performance difference
using the "owner" qualifier, it is not really something I would worry about.
There are more important areas to focus on, ones that can signifigantly impact
performance... by more than a millisecond or two.

IMO, using "owner.tablename.field" makes large queries difficult to read. I
prefer using aliases instead.