Nested queries AKA Subqueries

[J]

Hello All:

In creating some graphs for the users in a FE/BE database, I end up
needing about 10 subqueries in series (with some in parallel) to do all
the calculations. Using the aggregate functions available in access
it's the best I could do (seriously, I did the math and checked it many
many times over).

How does Access handle subqueries? When does the crunching switch
from back end to front end? Would doing it in VB with recordsets have
any advantages to stored querydefs? Does anyone have any tips for this
kind of thing?

Thanks in advance,

~J

 

[Van T. Dinh]

If you are talking about a JET BE (assumption for the rest) then the
crunching is always in the Front-End anyway since a JET database is a
file-sharing database, not a server database.

If you are talking about Access Domain Aggregate functions such as DSum,
DCount then it may be more efficient to use JET Aggreagate functions, e.g.
Sum, Count in the SQL. The SQL will be probably more complex using JET
Aggreagate functions and will most likely necessitate the use of SubQueries.

My guess is the saved Queries would be slightly faster since the SQL has
been compiled but I doubt the difference (between saved Queries and using VB
Recordsets) would be observable.

 

[Bill Mosca, MS Access MVP]

J

All the crunching is done on the front end in Access.

 

[J]

Thank you for your response, Van.

It is a Jet database, in the form of an MDB (soon to be MDE). Let
me make sure I understand this right:

Using my chosen system, the user opens the front end and initiates the
query (a SELECT query, no updates or anything)... Access copies over
all of the data BEFORE starting to crunch the data? As in: the only
serverside work is during updates and sending that data over? This is
the ideal situation if I understand this correctly.

Much obliged,
~J

PS. I thought I should post in Queries too so I deleted the post after
you replied! My apologies for the (now) double post.

 

[Van T. Dinh]

I don't think the "whole" set of data is actually sent from the BE to the FE
for processing in most cases. JET engine (with the Rushmore Query
optimization inbuilt) will most likely attempt to use the indices first to
filter records and only retrieve the data for these selected records.
That's why it is important to create indices in the Table Design and to
construct SQL to take advantage of the indices.

For example, let's say that in the Table that has the Field DateReceived
which stores data and non-zero time values which is indexed. A Query is to
select records for a particular date, says, 2006-10-26. Some users will use
the criteria:

.... WHERE (DateValue([ReceivedDate]) = #2006-10-26#)

some others will use:

.... WHERE ([ReceivedDate] >= #2006-10-26#)
And ([ReceivedDate] < #2006-10-27#)

AFAIK, the first one is very inefficient since every ReceivedDate value need
to be retrieved and DateValue() processed while with the second one, JET
will be able to use the index to select the required records.

 

[Guest]

To add a little to Van's answer, try using the JETShowPlan registry setting.
Examine the resulting ShowPlan.Out text file for the word "Scan". There is a
good tutorial available on this subject. See the topic subtitled "Use
JETSHOWPLAN":

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

I'm providing a round-about link, since the original sample includes an
error. There is a downloadable .zip file in my article that includes a
corrected SQL statement for testing.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I don't think the "whole" set of data is actually sent from the BE to the FE
for processing in most cases. JET engine (with the Rushmore Query
optimization inbuilt) will most likely attempt to use the indices first to
filter records and only retrieve the data for these selected records.
That's why it is important to create indices in the Table Design and to
construct SQL to take advantage of the indices.

For example, let's say that in the Table that has the Field DateReceived
which stores data and non-zero time values which is indexed. A Query is to
select records for a particular date, says, 2006-10-26. Some users will use
the criteria:

.... WHERE (DateValue([ReceivedDate]) = #2006-10-26#)

some others will use:

.... WHERE ([ReceivedDate] >= #2006-10-26#)
And ([ReceivedDate] < #2006-10-27#)

AFAIK, the first one is very inefficient since every ReceivedDate value need
to be retrieved and DateValue() processed while with the second one, JET
will be able to use the index to select the required records.

--
HTH
Van T. Dinh
MVP (Access)

Thank you for your response, Van.

It is a Jet database, in the form of an MDB (soon to be MDE). Let
me make sure I understand this right:

Using my chosen system, the user opens the front end and initiates the
query (a SELECT query, no updates or anything)... Access copies over
all of the data BEFORE starting to crunch the data? As in: the only
serverside work is during updates and sending that data over? This is
the ideal situation if I understand this correctly.

Much obliged,
~J

PS. I thought I should post in Queries too so I deleted the post after
you replied! My apologies for the (now) double post.