By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,759 Members | 1,778 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,759 IT Pros & Developers. It's quick & easy.

query difference

P: n/a
Hi All,

Is there a difference in preformance between the two;

TblNme has 36 fields across a record

a query that selects all fields and the report only uses 75% of the
fields, ie; Select TblNme.* where OffCde='123'

or

a query that selects the 27 fields used in the report
ie; Select TblNme.FieldNme, TblNme.FieldNme, etc.... where
OffCde='123'

In the first senerio does the query first bring down all the
data(including all the fields) and then select out the fields form the
criteria?
thanks
bobh.
Jun 27 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a
If you are talking about a Jet or ACE database engine DB... the defaults
with Access 2003 and earlier, and Access 2007, respectively, they are
file-server databases, with all the processing and manipulation done on the
user's machine, so both will bring across the LAN all the selected Records
(if the Records are selected on the basis of the Primary Key, or other
indexed Field, only the index will be brought across first, then only the
selected Records... but you are asking about _Fields_, so that is not an
issue here).

There is no Jet or ACE "server" running on the shared folder or "server" to
do any extraction or manipulation.

If you have linked tables in a server DB: MS or Sybase SQL Server, Oracle,
Informix, etc., then the extraction may be done at the server, thus reducing
traffic on the network. But, you will pay a price in complexity of
installation and maintenance of the server DB -- all of them need
"administration", which is to say "ongoing tender loving care". That is a
tradeoff only the developers and users can make -- do the benefits justify
the costs?

Larry Linson
Microsoft Office Access MVP

"bobh" <vu******@yahoo.comwrote in message
news:c1**********************************@y38g2000 hsy.googlegroups.com...
Hi All,

Is there a difference in preformance between the two;

TblNme has 36 fields across a record

a query that selects all fields and the report only uses 75% of the
fields, ie; Select TblNme.* where OffCde='123'

or

a query that selects the 27 fields used in the report
ie; Select TblNme.FieldNme, TblNme.FieldNme, etc.... where
OffCde='123'

In the first senerio does the query first bring down all the
data(including all the fields) and then select out the fields form the
criteria?
thanks
bobh.

Jun 27 '08 #2

P: n/a
On Apr 28, 4:04*pm, "Larry Linson" <boun...@localhost.notwrote:
If you are talking about a Jet or ACE database engine DB... the defaults
with Access 2003 and earlier, and Access 2007, respectively, they are
file-server databases, with all the processing and manipulation done on the
user's machine, so both will bring across the LAN all the selected Records
(if the Records are selected on the basis of the Primary Key, or other
indexed Field, only the index will be brought across first, then only the
selected Records... but you are asking about _Fields_, so that is not an
issue here).

There is no Jet or ACE "server" running on the shared folder or "server" to
do any extraction or manipulation.

If you have linked tables in a server DB: MS or Sybase SQL Server, Oracle,
Informix, etc., then the extraction may be done at the server, thus reducing
traffic on the network. *But, you will pay a price in complexity of
installation and maintenance of the server DB -- all of them need
"administration", which is to say "ongoing tender loving care". *That isa
tradeoff only the developers and users can make -- do the benefits justify
the costs?

*Larry Linson
*Microsoft Office Access MVP

"bobh" <vulca...@yahoo.comwrote in message

news:c1**********************************@y38g2000 hsy.googlegroups.com...
Hi All,
Is there a difference in preformance between the two;
TblNme has 36 fields across a record
a query that selects all fields and the report only uses 75% of the
fields, ie; Select TblNme.* where OffCde='123'
or
a query that selects the 27 fields used in the report
ie; Select TblNme.FieldNme, TblNme.FieldNme, etc.... where
OffCde='123'
In the first senerio does the query first bring down all the
data(including all the fields) and then select out the fields form the
criteria?
thanks
bobh.- Hide quoted text -

- Show quoted text -
I'm talking about a JET backend............... so, are you saying
that if I only select 5 fields(of a 35 field long record in a JET back-
end table) in my query that it will bring back all fields of the
records or will it only bring back the selected fields of the
records??
bobh.
Jun 27 '08 #3

P: n/a
"bobh" <vu******@yahoo.comwrote
I'm talking about a JET backend............... so, are you
saying that if I only select 5 fields(of a 35 field long
record in a JET back-end table) in my query that it will
bring back all fields of the records or will it only bring
back the selected fields of the records??
I thought I was clear in saying that the Jet database engine resides and
executes on the user's machine, and that is where all extraction and
manipulation is done. Yes, you are correct: it will bring back all fields
of the entire record (and nearby records in the disk "page") and then,
locally, extract the 5 fields you requested. It uses the remote backend
just as it would an .MDB file on the local hard drive, only a lot slower.

Even if you have Jet installed on the back-end, it is not built to operate
as a server, so loading it there would not help.

Please note that I did NOT say, it brings back the entire database over the
network.

If you want the field extraction to be done on the server, so only the
requested fields will be returned across the network, you need to be using a
server DB for the back end: MS SQL Server, MySQL, PostgreSQL, Informix, one
of the Sybase products, Oracle, or one of many others. In that case, you
request the data from the server DB, it does the retrieval, manipulation,
and extraction, and then (mostly) sends you just what you asked for (there
are some minor exceptions if you are linked via Jet and ODBC).

Larry Linson
Microsoft Office Access MVP
Jun 27 '08 #4

P: n/a
On Apr 29, 3:18 pm, "Larry Linson" <boun...@localhost.notwrote:
"bobh" <vulca...@yahoo.comwrote
I'm talking about a JET backend............... so, are you
saying that if I only select 5 fields(of a 35 field long
record in a JET back-end table) in my query that it will
bring back all fields of the records or will it only bring
back the selected fields of the records??

I thought I was clear in saying that the Jet database engine resides and
executes on the user's machine, and that is where all extraction and
manipulation is done. Yes, you are correct: it will bring back all fields
of the entire record (and nearby records in the disk "page") and then,
locally, extract the 5 fields you requested. It uses the remote backend
just as it would an .MDB file on the local hard drive, only a lot slower.

Even if you have Jet installed on the back-end, it is not built to operate
as a server, so loading it there would not help.

Please note that I did NOT say, it brings back the entire database over the
network.

If you want the field extraction to be done on the server, so only the
requested fields will be returned across the network, you need to be using a
server DB for the back end: MS SQL Server, MySQL, PostgreSQL, Informix, one
of the Sybase products, Oracle, or one of many others. In that case, you
request the data from the server DB, it does the retrieval, manipulation,
and extraction, and then (mostly) sends you just what you asked for (there
are some minor exceptions if you are linked via Jet and ODBC).

Larry Linson
Microsoft Office Access MVP
That being said, regardless of what is serving up the data, you are
better off selecting only the fields you need rather than using a
wildcard (*) to pull all of the fields from the table.

Bruce
Jun 27 '08 #5

P: n/a
"Bruce" <de***************@gmail.comwrote
That being said, regardless of what is serving up the data, you are
better off selecting only the fields you need rather than using a
wildcard (*) to pull all of the fields from the table.
I typically do that, if I need fewer than all fields, but just out of an
innate need to write "neat, orderly" code or SQL -- if your concern is
network performance, however, it will make no difference in a split Access -
Jet/ACE environment, so "better" may just be, like "beauty", in the eye of
the beholder.

Larry Linson
Microsoft Office Access MVP
Jun 27 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.