query difference

[bobh]

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.

[Larry Linson]

Re: query difference

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" <vulcaned@yahoo .comwrote in message
news:c12a4f6f-b615-4763-ab7f-a4fc3e7de6e1@y3 8g2000hsy.googl egroups.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.

[bobh]

Re: query difference

On Apr 28, 4:04 pm, "Larry Linson" <boun...@localh ost.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:c12a4f6f-b615-4763-ab7f-a4fc3e7de6e1@y3 8g2000hsy.googl egroups.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.

[Larry Linson]

Re: query difference

"bobh" <vulcaned@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

[Bruce]

Re: query difference

On Apr 29, 3:18 pm, "Larry Linson" <boun...@localh ost.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

[Larry Linson]

Re: query difference

"Bruce" <deluxeinformat ion@gmail.comwr ote

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