On 16 Feb 2004 05:41:10 GMT,
dcmfan@aol.comSPNOAM (DCM Fan) wrote:
[color=blue]
>Steve, we've been through all this before (pro/con of ADP) in the Access
>forums.
>
>Even if everything you say is true about ADPs, and the "common knowledge"
>becomes "don't use ADPs, " I still stand by my mantra of "minimize use of JET
>if at all possible."
>
>I've never had corruption in 8 years of SQL Server development (It has happened
>to others.) I have JET corruption at least once every 6 months. Granted, most
>of the time it's caused by faulty hardware, but it scares me that JET is so
>"nimble" that way.[/color]
I've had Access corruption, too, but the only times I've had data corruption
were when the back-end was an MDB. That has nothing to do with whether
queries are stored as Access objects or server-side objects when the data is
on the SQL Server.
[color=blue]
>Also, I don't know where you get your information on JET query performance, but
>in NO WAY is it EVER equal to pass-through performance, except on the smallest
>of SELECTS.[/color]
That's simpy wrong. I've tested it, and I've used the moitoring tools to
watch the SQL. Unless you do something on the Access side that forces it to
do processing locally like joining or filtering on a function, or your query
is just too complex for Access to translate (rare) or if you are missing
indexes on joined fields (and why would you do that on purpose),
Access/DAO/Jet quite happily builds server-side SQL and executes it on the
server.
In the rare cases when I find a query is insisting on processing locally, I
turn part or all of it into a stored procedure or view.
[color=blue]
>I can't begin to tell you how many times Access has either locked up, or I've
>gotten impatient at the wait of a mutli-table query designed in the QBE, but
>when run right from Query Analyzer returns records in less than 1 second.[/color]
Over time, I have gained an intuitive sense of what queries are dangerous that
way and which are not. It is tru that some time can be wasted with this now
and again.
[color=blue]
>Everyone of my combo boxes use Pass-through SELECTS. The performance increase
>over JET selects REQUIRES IT, especially when connecting over the internet.
>(one-to-one NAT)[/color]
I have not had the performance issues you describe. It soed seem to help to
make the query DISTINCT, though, so Access will use a snapshot, and not a
Dynaset which would make more than the necessary number of round trips for a
read-only result.
[color=blue]
>Don't get me wrong...I take FULL ADVANTAGE of JET/DAO-based front ends (local
>tables, specs, QBE), and I may even come up with an absolute must someday. I
>even prefer DAO over ADO, but that's only because I KNOW that I'm more familiar
>with it!
>
>My bottom line is this: I'll listen to criticism of ADPs any day (although my
>recent ADP projects have been successful, I have lots more exeperince with
>MDB), but I can't take it when Access-lovers (I'm one) actually say with a
>straight face that JET can perform in any way "as good as" SQL Server views,
>SPs, or dynamic SQL.[/color]
Well, I hate to be one of those people (actually, I'm and Access lover/hater,
and I'd take ADO.NET over Jet any day in a C/S project not done in Access),
but what you desccribe has not been my experience. It is true that some
queries need to be translated to run efficiently, but my experience is that
most don't, and in that case, they shouldn't.
I realize we just have to agree to disagree on much of this, but I think it's
important that both of our opinions are fully expressed for the record.
[color=blue]
>Just the fact that an extra layer is involved ought to be enough proof...[/color]
From what I can tell, ADO/OLEDB is actually a much more complex stack of
layers than DAO/JET/ODBC, and some of what the DAO layer contains is very
beneficial such as the ability to use SQL to join tables from muliple sources,
and the caching of schema data in the front-end rather than looking it up
repeatly from the back-end.
