File Server Architecture Question
Brett J. Valjalo
a similar question from me in the past, thanks to everyone who's
sticking with me. REALLY trying to minimize network traffic here. Say
I've got a form, its a continuous form with only a few fields from a
larger table, but all the records. Call it frmList, bound to
qryJobOrders (only a few fields from tblJobOrders, and linked to a few
other tables, like tblClientCompany and tblClientBranches). A user then
clicks on a particular field, the on click event then brings up a
frmJobOrderDetails, which shows the person all the details of the
particular Job Order clicked. The details form is bound to
qryJobOrders2, IDENTICAL to qryJobOrders, except it uses
tblJobOrders(*). Now, if I understand file service operations properly
(and the empirical evidence would SEEM to bear this out, but I'm no
network engineer), When I load frmList, although only a few fields from
tblJobOrders are specified in the query, the entire table is sent to the
client over the network for the query processing. This being the
primary difference from client/server. Fine. So at that point
qryJobOrders2 (with the entire tblJobOrders) is on the client machine in
some sense. BUT, when frmJobOrderDetails is opened by the onclick, the
entire qryJobOrders2 is again reloaded across the network. What I'm
trying to make happen in this system is that the query/table is only
pulled across the line ONE TIME though the info is actually on shown on
two separate forms. How do I do it? If I just base frmList on
qryJobOrders2 instead (rather than trying to limit the fields to only
those which are necessary in qryJobOrders- pointless in File Server,
Yes?), will this solve the problem? If not, is there a way to force
Access to use the recordsource for the list form, thats already loaded
into memory, as the recordsource for the details form? There must be a
way other than temporary tables, which will add so much maintenance and
coding that I'm not that knowledgeable about...
Or am I S.O.L.?
TIA
brett
Here's the question:
--
Brett Valjalo * Assistant to the President
Corporate Alliances * Career Agents
300 Brannan Street * Suite 212 San Francisco, Ca 94107
Work/415-597-5509 Fax/415-597-5501 PCS/510-331-5555
Email/br...@careeragents.com
http://www.careeragents.com
Larry Linson
indexed fields, so that only the referenced indexes for the whole table
need be brought across to select the single record you'll display?
Access will not need to bring across the entire table to pull out that
record once it is located via the index. Certainly this should speed
things up if you can select that record by its Primary Key.
--
L. M. (Larry) Linson
Access Database Examples at http://homestead.deja.com/user.accdevel
(script execution must be enabled in your browser to see it)
Earlybird Bargains: Y3K Remediation Planning
Sent via Deja.com http://www.deja.com/
Before you buy.
Yoee
identical (fieldwise), I don't see any reason to have two separate
forms (and record sources) to begin with. One could simply base a form
on one query and make the controls revealing the "details" invisible,
until such time as the user clicks to show the details. No need for
extensive coding, either. If, however, there is something I have
missed, the recordset being returned on the "reveal details" portion of
the query, would only be returning one record, not the entire
recordset, as it should be delimited by the indexed value in your first
form.
In article <85ist9$m5t$1...@nnrp1.deja.com>,
--
Free Silkworm Zone
Brett J. Valjalo
how "file server" works. I thought that queries had to be processed on the
local machine, meaning that the entire tables have to be pulled across the
wire from the file server to the client before processing, sorting,
limiting, whatever manipulation you need to do to them.
Can someone describe in what cases/circumstances EXACTLY that I can avoid
this occurence, because I've done a LOT of dicking around with indexing
fields, opening forms using the docmd openform Where Clauses, Where clauses
in saved queries (both hardcoded and criteria pulled from forms), etc? And
nothing seems to help.
Can someone just state with certainty whether what I'm asking is possible,
or am I just being unclear as to what I want? Basically, its this:
How can I open form1 bound to tbl or qryA, choose a record from the list on
the form1, then jump to form2 which lists the details of the record, without
incurring two times of pulling the entire qry or tbl across the wire. And
details on what procedure to use would be awesome.
The whole "just put it on one form" answer is not what I'm looking for, I
know I probably "could" do this using a bound, tabbed form that has the
continuous "brief list of table contents" form on one tab, and the single
record "full list of fields detail" form on another tab, but thats not what
I want if I can avoid it. Among many reasons is that the table and records
are large enough that I want the full screen to display either the brief
list or full details. If that makes sense. I really Appreciate all help on
this... I'd like not to resort to a workaround if their is indeed a simple
solution that does what I want... It will have widespread application to my
db as well.
TIA
brett
Brett J. Valjalo
Thanks for the help. It's getting clearer. A couple more ?'s, if you happen to
revisit the thread?
1. I'm curious as to whether the Left () function you give an example of is
essentially analogous to ANYTHING you try compare with a field other than just
make a simple, verbatim comparison to it's exact value, based on the field type
(like fldDateField<#01/01/2000# would pull partially, but year(fldDateField)<2000
would pull fully)?
2. In your example if MyField was indexed, would you still have the full pull?
I'm guessing you would, if the above was true.
3. How do form references affect things? Should I try to make sure that any
references to controls have the same field type set as the field does in the
tabledef?
4. How do links to other tables affect the scenario? I.E. would:
SELECT JobOrders.OrderTaker, Companies.ClientCompany, JobOrders.DateTaken,
JobOrders.Position, JobOrders.Priority
FROM Companies INNER JOIN (JobOrders INNER JOIN Clients ON JobOrders.BranchID =
Clients.BranchID) ON Companies.CompanyID = Clients.CompanyID;
which is designed to pull all records from job orders, but also needs a
ClientCompany field thats two tables removed, also pull across the entire Client
and Company tables (of course the ID's are indexed) as well as the joborders
table? If so, if I stipulated a simple Where clause on an indexed field in the
JobOrders table (like JobOrderID=1), would that avoid that occurence?
5. If one only chooses certain fields, are you saying those ARE the only one's
brought over? Always?
TIA for your help
Brett
Chuck Grimsby wrote:
> Larry or Michka could probably answer better then I, but I'll take a
> stab at it.
>
> A =lot= depends upon the set up of your query. For all intents and
> purposes, you can "consider" that forms bound directly to tables,
> bring across the whole table (though, actually, it's just the portion
> of the table being accessed). If you're doing any ordering on the
> table in your form, the whole table has to come across to be ordered.
>
> For a form based on a Query, it'll depends upon what Indexes are being
> used, and if any additional processing of the record before selection
> is used. For example, if you're selecting on a date and that date
> field isn't indexed, the whole table comes across. If you're using a
> indexed date field =and= you're using something like WHERE
> LEFT$([MyField], 6) = "Barney"... Then the records that match the
> date, =all= the records that match the date, have to come across so
> that the Left$ operator can work on the record to see if selection
> should occur.
>
> Is that any help?
>
> On Thu, 13 Jan 2000 08:36:33 -0800, "Brett J. Valjalo"
> ------
> Age Is A Hell Of A Price To Pay For Maturity.
Pete B
The entire processing of the query is done on the machine where Access
resides and where the query is running. Regardless of table indexing,
criteria, whatever, Access must pull the entire data set across the wire in
order to select the proper subset of records that match those specs. The
data are retrieved in a certain order based on the indexes (indexes do not
restrict the data set in Access, like they do in, say, FoxPro, they just
order it), but the entire set of data are still transmitted, the query
cannot run until the data are present on the system where the query is
processing information.
The methods you are suggesting could only be done if Access sent the query
request to the server, the server file ran the query on the server side, and
then transmitted the resulting data set. How else could it pick and choose
which records to transmit? How would the server know what to do in the
first place? I do not know of any way in Access to run a remote query in
that fashion. SQL Server or any other client server can do that, that is
what client/server operation entails, but not file servers like Access.
That is the nature of file server operations, to only use local data.
But...
Load the data once, it remains in the local machine until you explicitly
remove it. So, if you run all your vital queries at startup of your app,
the initial startup will be slowed but the app should run faster thereafter.
Alternatively, copy the data to local tables at startup, run all queries
against that local set, and update/purge when closing the app.
One last hope, though. I seem to recall Arvin showed us a technique at one
time for linking directly to querydefs in a server file. I'm not sure if
that would resolve this, but perhaps, although I think the entire process
would then involve transmitting the querydef to the local machine and then
running it the same way as always. Could be wrong, though.
--
Pete B
Chuck Grimsby <cdotg...@worldnetdotattdot.net> wrote in message
news:v60t7scm4n04ddsvr...@4ax.com...
>
> Larry or Michka could probably answer better then I, but I'll take a
> stab at it.
>
> A =lot= depends upon the set up of your query. For all intents and
> purposes, you can "consider" that forms bound directly to tables,
> bring across the whole table (though, actually, it's just the portion
> of the table being accessed). If you're doing any ordering on the
> table in your form, the whole table has to come across to be ordered.
>
> For a form based on a Query, it'll depends upon what Indexes are being
> used, and if any additional processing of the record before selection
> is used. For example, if you're selecting on a date and that date
> field isn't indexed, the whole table comes across. If you're using a
> indexed date field =and= you're using something like WHERE
> LEFT$([MyField], 6) = "Barney"... Then the records that match the
> date, =all= the records that match the date, have to come across so
> that the Left$ operator can work on the record to see if selection
> should occur.
>
> Is that any help?
>
> On Thu, 13 Jan 2000 08:36:33 -0800, "Brett J. Valjalo"
Michael (michka) Kaplan
1) Jet takes the query to compiles it (if it is already compiled skip to
step #3)
2) Jet pulls down column info on all the tables and uses it to construct a
query plan
3) The QPE (query processing engine) runs the compiled query, which WILL
take advantage of indexes if the compiled query allows it by ONLY bringing
down index pages instead of all data, whenever it can.
4) If a unqiue bookmark can be obtained for each row, then once #3 is done,
only the key fields needed will be pulled down, plus the records that need
to be shown. Extra data is only retreived as you get to that row.
--
?MichKa
(insensitive fruitarian)
random junk of dubious value, a multilingual website, the
48-language TSI Form/Report to Data Access Page Wizard,
and lots of replication "stuff" at http://www.trigeminal.com/
?
"Pete B" <bar...@datatek.com> wrote in message
news:s81a865...@news.supernews.com...
Arvin Meyer
Pete B wrote in message ...
>Not so sure that is correct, Chuck. I could be mistaken here, but...
You are <g>
Think of it this way. If you index the table, and use those indices properly
in the query, Access will get the page that the record resides on. The
server need do nothing except return those pages, in the same way it returns
file requests. The index is a pointer to where the page lives on the
server's drive. Only if there are no discernable indices, or you haven't
restricted the data set with a Where clause, will the entire table be piped
in. That's exactly what the Query Optimizer does. If you download the file
v35perf.doc and hunt down the ISAMStats and ShowPlan functions at the end
you should be able to see much of this for yourself. Here's the file:
http://download.microsoft.com/download/access97/whitep6/1/WIN98/EN-US/V35per
f.exe
---
Arvin Meyer
Pete B
info. and I did not necessarily mean it had to load the actual bytes oif
data, I meant it had to load the entire set of _whatever it uses_ to process
the query and produce a result. To be very explicit, I am saying it
specifically does NOT just send a request for info to that remote file and
get a resultant recordset back.
And what happens if this is the very first time ever, or it is a dynamic
SQL, or the table is not indexed, or if the query must fetch info not on
indexed columns? How would a query determine the unique bookmarks for, say,
all records, never before processed by the local app, where one column must
match criteria A and another must match criteria B, neither of which are on
indexed fields?
The real question is not what kind of data you obtain from the remote file,
rather it is where do you process that data. Anything limiting the process
would apply equally as well to local data as to remote data, whether it is
internal Jet processes or application-driven factors.
I could even see this if there were a remote system which also has Access
installed, IOW you could then tell the other Access system what to do, but
what if it is just an mdb file stored there? Everything that happens to
that remote file has to be transmitted over the network, 100%. But that is
not the case for a client server at all.
Are you saying that Jet goes in and looks for/obtains that info by using the
resources of the other machine to do the selection? How does it manage to
do that? In other words, what is the difference between running the query
on local data vs remote data? In either case, the query requires the same
amount of information, regardless of what specific _kind or amount_ of info,
in order to perform its' task. So where does it get that information to
form that plan at the outset, and once it has it, where does it then do its'
work, does it do it on the server or the local system?
Does it "form that plan" while it is doing something on the local system or
while it is doing something on the remote system? How could it possibly do
anything on a remote system? What if it updates tables stored remotely,
where are the updates for the indexes processed, remotely or locally? Still
seems like it would have to get the entire set of indexes or whatever in
order to devise that plan or edit/change data, meaning it would have to load
as much info over the wire from the remote tables as it would if it were
doing the identical thing to a local data set.
I certainly am not trying to disagree with you, you know the Jet internals
far better than me, but seems to me that is not the issue. And be patient,
I'm trying to learn here. It just seems to me that Jet or whatever has to
obtain as much information from a remote file as it would from it's own file
in order to do it's tasks, IOW it has to "load the whole thing" over the
wire. But a client server does NOT require that at all.
Somehow, I do not feel like I am expressing this very well, I hope you can
discern what I am getting at.
--
Pete B
Michael (michka) Kaplan <forme...@spamless.trigeminal.spamless.com> wrote
in message news:uPKGxo4X$GA.266@cpmsnbbsa04...
Pete B
stored in the tables, but that is not the point. What I meant was the
information required by the Jet query processor to do its' work, whatever
constitutes that, be it indexes or whatever. Jet must obtain that entire
set of info from the remote file just like it must do so from the local
file.
And of course, you are assuming most of the indexes exist, which in real
life they frequently do not.
In fact, I would certainly hope to hell it never has to load the entire
actual set of records, that would _really_ be a bottleneck.
--
Pete B
Arvin Meyer <a...@m.com> wrote in message
news:85qqo6$d4p$1...@nntp9.atl.mindspring.net...
Arvin Meyer
too. <vbg>
You are correct in that it must request the entire index for a table, in
order to use it, but just imagine a table where the average record size it 5
to 6 hundred bytes, and the average byte size for each index for that row
might be 8 bytes or less. Think how much less data must be brought across
the pipe in order to retrieve say 2 or 3 hundred rows, out of a set of
tables with with 5 thousand records in one and 8 thousand in the joined
table.
---
Arvin Meyer
Pete B wrote in message <0M9g4.483$sO5....@news.rdc1.tn.home.com>...
Pete B
--
Pete B
Chuck Grimsby <cdotg...@worldnetdotattdot.net> wrote in message
news:c4l48sokr74gvtm01...@4ax.com...
> On Sun, 16 Jan 2000 02:10:04 GMT, "Pete B" <bar...@datatek.com>
> wrote:
>
> >And of course, you are assuming most of the indexes exist, which in real
> >life they frequently do not.
>
> They don't?!? They darn well better! What good are they if they
> =don't= exist until they're needed?
>
You are probably correct about an app that you design as an Access mdb,
Chuck. But consider, there are many other types of remote data you can be
linked to, and if it is such an Access app, maybe you may not have been the
person who set it up. For example, if you link to FoxPro tables as an
external data source, you are pretty much restricted to using whatever is
provided by the FP app for indexing.
> >In fact, I would certainly hope to hell it never has to load the entire
> >actual set of records, that would _really_ be a bottleneck.
>
Actually, I think if it was any table that had no indexing, the only way
Access could do stuff is via a sequential scan of every record. So that
would be _way_ slow.
> Why do think DSUMs are so slow? Ta-da! Luckily, they only pull
> across 1 or 2 fields.
>
> ------
> Thou Shall Not Kill, Unless It's For Dinner!
Pete B
retrieved every time you run in order to do such stuff, and one cannot do
much to change _that_ aspect of the slowdown.
But you and Michael have convinced me that I overstated my case here.
After reviewing my good ol' JDEPG, it appears that it is a _very, very_
complex issue. The things I see in the manual about optimization appear to
indicate that, as Michael said, the actual data tables and their associated
index tables may be only a small part of the overall situation, lots of
other things involved in the query processing, and you probably can, after
all, optimize an attached file-server data file processing almost as much
as local table structure. So I guess I was wrong about that.
--
Pete B
Arvin Meyer <a...@m.com> wrote in message
news:85tied$898$1...@nntp4.atl.mindspring.net...
> The indices DO exist in the databases, I write and they're used properly
> too. <vbg>
>
> You are correct in that it must request the entire index for a table, in
> order to use it, but just imagine a table where the average record size
it 5
> to 6 hundred bytes, and the average byte size for each index for that row
> might be 8 bytes or less. Think how much less data must be brought across
> the pipe in order to retrieve say 2 or 3 hundred rows, out of a set of
> tables with with 5 thousand records in one and 8 thousand in the joined
> table.
> ---
> Arvin Meyer
>
> Pete B wrote in message <0M9g4.483$sO5....@news.rdc1.tn.home.com>...
Brett J. Valjalo
Thanks for the intelligent discourse on the subject. Sounds like the thing to
do is to use indexes, but ONLY on the fields in the table that one is likely to
search on, as each additional index you put on increases the "overhead", if you
will. Correct, more or less? Well, perhaps someone could explain this one to
me? The following is a SQL for a combo on my splashscreen:
SELECT DISTINCTROW Candidates.CandidateId, Candidates.[Candidate Name],
Payments.InvoiceID, Candidates.CandidateTitle, Candidates.Interviewer
FROM Candidates LEFT JOIN Payments ON Candidates.[Candidate Name] =
Payments.[Candidate Name]
WHERE (((Candidates.Status)<[forms]![splashscreen]![Active]))
ORDER BY Candidates.[Candidate Name];
Now, candidate.status is indexed, and so is payments.candidate name. I know
that this query pulls ALL rows from Candidates, although the value of [active]
(which is 2, via an option group) would stipulate only about 25% of the rows.
So, is the full pull the result of:
A) the DistinctRow, or
B) the Order By, or
C) the Left Join, or
D) one or more of the above?
TIA
Brett
Pete B wrote:
> Comments below
>
> --
> Pete B
> Chuck Grimsby <cdotg...@worldnetdotattdot.net> wrote in message
> news:c4l48sokr74gvtm01...@4ax.com...
> > On Sun, 16 Jan 2000 02:10:04 GMT, "Pete B" <bar...@datatek.com>
> > wrote:
> >
> > >And of course, you are assuming most of the indexes exist, which in real
> > >life they frequently do not.
> >
> > They don't?!? They darn well better! What good are they if they
> > =don't= exist until they're needed?
> >
>
> You are probably correct about an app that you design as an Access mdb,
> Chuck. But consider, there are many other types of remote data you can be
> linked to, and if it is such an Access app, maybe you may not have been the
> person who set it up. For example, if you link to FoxPro tables as an
> external data source, you are pretty much restricted to using whatever is
> provided by the FP app for indexing.
>
> > >In fact, I would certainly hope to hell it never has to load the entire
> > >actual set of records, that would _really_ be a bottleneck.
> >
>
> Actually, I think if it was any table that had no indexing, the only way
> Access could do stuff is via a sequential scan of every record. So that
> would be _way_ slow.
>
> > Why do think DSUMs are so slow? Ta-da! Luckily, they only pull
> > across 1 or 2 fields.
> >
> > ------
> > Thou Shall Not Kill, Unless It's For Dinner!
--
Pete B
> the above lines.
>
Well, actually, I just wasn't paying attention, that should have followed
the first lines I typed. Consequence of trying to do six things at once.
It's that damned keyboard bug again, entering what I typed instead of what
I meant....
:=)
--
Pete B
Chuck Grimsby <cdotg...@worldnetdotattdot.net> wrote in message
news:be478so0jse9ugufj...@4ax.com...
> On Mon, 17 Jan 2000 08:57:43 -0600, "Pete B" <bar...@datatek.com>
> wrote:
>
> >> >And of course, you are assuming most of the indexes exist, which in
real
> >> >life they frequently do not.
>
> >> They don't?!? They darn well better! What good are they if they
> >> =don't= exist until they're needed?
>
> >You are probably correct about an app that you design as an Access mdb,
> >Chuck. But consider, there are many other types of remote data you can
be
> >linked to, and if it is such an Access app, maybe you may not have been
the
> >person who set it up. For example, if you link to FoxPro tables as an
> >external data source, you are pretty much restricted to using whatever
is
> >provided by the FP app for indexing.
>
> Unindexed tables, regardless of source, are handled the same way as
> the Domain functions. I assume that's true for FoxPro as well, but
> I've no experience with FoxPro.
>
> >> >In fact, I would certainly hope to hell it never has to load the
entire
> >> >actual set of records, that would _really_ be a bottleneck.
>
> >Actually, I think if it was any table that had no indexing, the only way
> >Access could do stuff is via a sequential scan of every record. So that
> >would be _way_ slow.
>
>
> Uhh... Pete... You're replying to yourself. You wrote both sets of
> the above lines.
>
> ------
> Blondes Are Intellagint... Intell... Oh Forget It.
>