"Pachydermitis" <pr*******@gmail.comwrote in
news:11**********************@m73g2000cwd.googlegr oups.com:
>>so my apps only operate on small sets of records
That is what I am saying; Access does a lousy job of managing the
records - forcing developers to do it.
If you send stupid SQL to SQL Server you're going to get a huge
dataset, too, so I don't see how the developer is *not* responsible.
The only difference between Jet and a server is that Jet has to get
the index pages first (though it may not need to get all of them if
there's a condition on the index that limits how much of it is
needed). That's a pretty small amount of data (how much space do
think it takes to store the index for an Autonumber PK for a table
with 300K records?). Multiple joins would, of course, up the number
of indexes to be retrieved, and criteria on non-PK fields in the
joined tables could also add to it, but we're talking about less
than 20K for the whole index for each PK table (assuming Autonumber
and assuming the whole index is needed), and something of comparable
magnitude for any other non-primary indexes (though they'd be longer
if they were dates (8 bytes) or text (variable)), so, certainly the
amount of data retrieved in indexes alone is a difference, and the
more indexes used, the more of a difference it will be.
But other than that, there's not going to be much difference except
if you compare filtering/joining on non-indexed fields, which, of
course, a server database will do more efficiently than Jet, without
the application designer needing to do anything.
Yes, choose the reductio ad absurdam case and, yes, you're going to
be CORRECT.
In all other instances, not so much.
But back to what you said:
Access does a lousy job of managing the
records - forcing developers to do it.
Access does *not* "do a lousy job of managing the records" at all.
It's extremely efficient. But the management that a developer does
is forced on the developer by *human* considerations. A person can't
deal with a set of records that is created by joining two
300K-record tables -- no human being can make any sense out of that.
A human being would only ever work iwth subsets of that data, so
providing a user interface that retrieves subsets of the data is not
only going to be efficient (for both Jet and a server database
engine), but it's going to good application design. A list of 300K
records is of no use whatsoever to a human being without navigation
tools, and if you're going to provide navigation tools, why not make
them data retrieval tools and remove the inefficiency?
And I'm not convinced a db server is going to be any more efficient
-- the only difference in the amount of data sent is, again, the
indexes for the two tables that are used in the join. If those are
long integer or Autonumbers, that's 4 bytes per record. We're
talking less than 50K of data difference for Jet to retrieve that
wouldn't be sent by the database server. Who's going to notice
*that*?
Secondly, Access may be able to start presenting data sooner when
retrieved from Jet than from the database, since Access can then use
its Rushmore technology to start displaying the beginning of the
recordset before the whole thing is retrieved (assuming no sorting
on the results)
>>Who in their right mind would populate. . .
I guess this means that you are in your right mind and would never
trust Access, in its legendary efficiency, to manage the data for
you?
Eh? What I wrote was:
Who in their right mind would populate a form with a recordsource
populated from a join of two 300K-record tables?
This would be inefficient and COMPLETELY USELESS regardless of
whether you're retrieving the data from Jet or from a server
database.
I wouldn't do it.
It doesn't make any sense in a real-world application.
>>I'm only disputing overly broad statements that claim
that Jet is pulling scads of data cross the network.
Reread the post, no one said that. I said Access does a miserable
job and further clarified in my second post. Based on your above
statement, you agree - at least about the interface.
No, I don't agree to any such thing.
Jet does nearly as well as a server database would in terms of the
amount of data pulled across the wire. When you're joining two
tables and displaying the whole resultset, the difference between
Jet and a server database is going to be tiny in terms of the amount
of data retrieved, because in both cases, you're pulling 100s of
thousands of rows, and that's going to be most of the data.
>>even with a very large data set
because Jet is efficient in optimizing SQL.
Try this: Limit a table's recordset in a query then join that
query to another table. Say table1 has 200k records and its
limited qry now has 10 records. Table2 has 200k records. Joining
10 to 200k is much faster than joining 200k to 200k.
Well, d'oh.
Of course it is, because an index join will vastly reduce the amount
of data pages that need to be retrieved.
Put a function in the second query so you can watch the records.
Eh, what? I don't have a clue what the hell you mean here.
Rather than limiting the compared records to the first query,
Access optimizes the join for us so that it has to join every
record in both tables. More robust engines allow this type of
optimization - often a must when your recordsets are in the
millions.
Bullshit.
If your join fields are indexed and you aren't filtering on an
expression, Jet will do an index merge and then retrieve only the
data pages needed.
Haven't you ever worked with SHOWPLAN to see how Jet optimizes
queries? If you haven't, then you don't have a clue what you are
talking about.
Or are you talking about badly designed tables that are not
appropriately indexed?
>but I'd never use anything but a SQL rowsource.
Guess what, Access compiles its query's and using sql as a
rowsource is generally frowned upon as performance poor.
http://support.microsoft.com/kb/209126
Eh? I don't see that in there. It does say to save your SQL as
queries, but I don't see where there's any performance benefit to
that, except that forms based on the same query would only need to
have the saved query compiled once, but two forms based on the
corresponding SQL recordsource would need to be saved twice, once
for each form. But the SQL gets compiled whether its in a saved
query or in a form's recordsource (or a combo/listbox rowsource).
Ever looked at MSysObjects and noticed all those ~sql_... objects?
That's the compiled SQL for rowsources and recordsources. They go
away when you compact.
Of course, this is all moot as a performance issue, because a
properly-designed front end doesn't ever need to be compacted. This
means that once all forms have been opened once, their recordsources
and rowsources are all compiled and they stay that way.
This is one of those "performance tips" like "use DBEngine(0)(0)
instead of CurrentDB() because it's 1000 times FASTER" that don'e
hold up under scrutiny because in real life, they don't amount to
anything at all.
Any experieinced developer would know not to trust the
recommendations in Microosft's Knowledge Base without outside
confirmation.
>>You can design a scenario where Jet will look poor.
If you ever have to build an application that functions at the
enterprise level, you will find out that I did not design a
scenario for jet to look poor, that is life. Jet is not that
robust and probably not designed to be.
Well, now you've completely changed the subject. You seem to think
I'm arguing that Jet is appropriate for all situations. It obviously
IS NOT. No one but a moron would think so.
But no one but a moron would think that was what I was suggesting.
Well, what a disappointment, who would have ever thought that you
would be such a pompous jerk. I take back what I said earlier
about respecting you. You don't even have the decency to be
polite.
You're a complete idiot. You change the subject when you feel like
it. You deliberately misread my comments to mean something that they
don't. You mischaracterise what I've written. You take quotations
out of context. You chop the full quotation in order to make your
point.
You're intellectually dishonest.
And you haven't actually refuted a single one of the technical
points I've made about exactly how Jet actually works.
As you say, you only work in jet. Maybe you should get more
experience before you talk about things you don't know about. You
are like a guy on a moped shouting to everyone that he's the
fastest thing on the road. So keep shouting - to anyone foolish
enough to listen - or (like me) foolish enough to answer you.
I know Jet inside and out.
I know what it does well, and the scenarios you've described are
ones that Jet either handles extremely efficiently, or that no
real-world application would never include.
<PLONK>
--
David W. Fenton
http://www.dfenton.com/
usenet at dfenton dot com
http://www.dfenton.com/DFA/