Access <- > SQL Server

[Jegger]

Hello!

We have following situation;
network with 100 users, aplication developed in Access, user DB
deployed on SQL Server.

Is it better to create query inside aplication (with code) and then pass
it to SQL Server for execution or is it better to have all these queries
saved like stored procedures and then called from aplication?

Witch queries run faster?

Thank's!

P.S. I know that my english isn't perfect:(!!!!!

[Trevor Best]

Re: Access &lt;- &gt; SQL Server

On Sun, 15 Feb 2004 21:30:11 +0100 in comp.databases. ms-access,
"Jegger" <dzugaj@net4u.h r> wrote:
[color=blue]
>Hello!
>
> We have following situation;
> network with 100 users, aplication developed in Access, user DB
>deployed on SQL Server.
>
> Is it better to create query inside aplication (with code) and then pass
>it to SQL Server for execution or is it better to have all these queries
>saved like stored procedures and then called from aplication?
>
> Witch queries run faster?[/color]

Stored procedures are compiled with the execution plan so run faster
than dynamic SQL.

--
A)bort, R)etry, I)nfluence with large hammer.

[Steve Jorgensen]

Re: Access &lt;- &gt; SQL Server

On Sun, 15 Feb 2004 21:30:11 +0100, "Jegger" <dzugaj@net4u.h r> wrote:
[color=blue]
>Hello!
>
> We have following situation;
> network with 100 users, aplication developed in Access, user DB
>deployed on SQL Server.
>
> Is it better to create query inside aplication (with code) and then pass
>it to SQL Server for execution or is it better to have all these queries
>saved like stored procedures and then called from aplication?
>
> Witch queries run faster?
>
> Thank's!
>
>P.S. I know that my english isn't perfect:(!!!!!
>
>[/color]

Since you say this is an application developed in Access, and there are,
apparently, no other applications sharing the back-end, my recommendation is
to implement all the queries in Access except those that you can't make run
optimally that way by simply modifying the query's approach (chaning
subqueries to outer joins, etc.). Most queries of linked tables on SQL Server
will run well as Access queries.

The reason for keeping the queries in Access as much as possible is that it
makes the particular choice of server easier to change. It would be
relatively (though not totally) trivial, for instance, to switch to a
PostgreSQL or Oracle back-end.

There is actually very little speed penalty, in most cases, for executing
Access queries against linked, ODBC tables because Access can generally
convert these into prepared statements that actually act quite a bit like
stored procedures in that they are compiled once by the server when they are
first used, then reused when the same query is executed again. SQL Server is
also clever enough to reuse the same query plan when additional statements are
prepared with identical definitions.

[DCM Fan]

Re: Access &lt;- &gt; SQL Server

I make it a rule to: (well, rules)

1) Create parameterized stored procedures whenever possible, which usually mean
every read-only situation. (If you're using ADPs, however, MS has done a heluva
job making param SPs updatable.)

2) Create Pass-through queries for any must-have dynamic sql (again,
read-only). Also, PT queries must be used to call those SQL Server SPs!

3) ONLY use JET (Access) queries for those siutations that REQUIRE it, like
updatable BOUND data sources, LinkChild/Master, consumers of VBA custom
functions, etc. (And strive to make your VBA functions into processes that use
SQL Server if you can)

It really boils down to this: Don't use JET unless you ABSOLUTELY MUST. I
haven't gone as far as some JET-haters that use UNBOUND forms w/
insert/update/delete classes, but I'm getting there...

I love Access as a front-end building tool, and a reporting tool. But I can
live without JET.

All of me recent Access/SQL development has been ADPs...no JET! YAYYYY!

[Steve Jorgensen]

Re: Access &lt;- &gt; SQL Server

On 15 Feb 2004 23:41:03 GMT, dcmfan@aol.comSPNOAM (DCM Fan) wrote:
[color=blue]
>I make it a rule to: (well, rules)
>
>1) Create parameterized stored procedures whenever possible, which usually mean
>every read-only situation. (If you're using ADPs, however, MS has done a heluva
>job making param SPs updatable.)
>
>2) Create Pass-through queries for any must-have dynamic sql (again,
>read-only). Also, PT queries must be used to call those SQL Server SPs!
>
>3) ONLY use JET (Access) queries for those siutations that REQUIRE it, like
>updatable BOUND data sources, LinkChild/Master, consumers of VBA custom
>functions, etc. (And strive to make your VBA functions into processes that use
>SQL Server if you can)
>
>It really boils down to this: Don't use JET unless you ABSOLUTELY MUST. I
>haven't gone as far as some JET-haters that use UNBOUND forms w/
>insert/update/delete classes, but I'm getting there...
>
>I love Access as a front-end building tool, and a reporting tool. But I can
>live without JET.
>
>All of me recent Access/SQL development has been ADPs...no JET! YAYYYY![/color]

I respect yor opinion, but I disagree. There are cases in which I do agree,
and these are when you will have multiple front-ends to the same data, but
when SQL Server is just the data store for an Access application, the
performance benefit to implenmenting everything server-side is more than
overshadowed by the increased complexity of the front-end, and the loss of
independence from a particular server back-end.

I do favor implementing server-side views and stored procedures as an
optimization when required, but only after a performance problem has been
identified, and it doesn't respond to more minor rearrangments of the queries.

Regarding the use of ADPs fopr client-server apps, I guess that worked for
you, but after I successfully implemented several client-server apps with DAO
and MDBs, I worked on one monster of a project with ADPs, and found it an
ecercise in terrible frustration. Error message handling doesn't work right,
Access is so touchy about the actual implementations of things on the back-end
that many techniques of great use from the server perspective are not usable,
the updatability rules change with every new release of MDAC, so that I've had
to adopt a practice of never trying to bind a form to a query on a join
because I can't trust it (I use hidden combo boxes for FK lookups - how's that
for efficiency!), etc. Frankly, I would never use ADPs again for anything
other than prototypig. I'm at least 5 times more productive using MDBs and
DAO. Of course, if I was using something other than Access as a front-end, I
would nearly always us ADO.

Clearly, your experience differs, but to me ADPs are simply far too unstable
for production systems, and I don't see that they provide any performance
boost, what with having to keep asking the server for schema information
instead of caching it locally in links, constantly digging around behind
procedures and views to update individual tables. You have to jumpt through
several hoops to force Access to respect your intended security and
abstractions with views and procedures, etc. I spend most of my time trying
to get the thing to do what I say instead of guessing what it'll do when it
tries to thnk for me and do what it "knows" I must want.

[DCM Fan]

Re: Access &lt;- &gt; SQL Server

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.

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.

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.

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)

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.

Just the fact that an extra layer is involved ought to be enough proof...

[Steve Jorgensen]

Re: Access &lt;- &gt; SQL Server



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.