Alternatives to ADP?

[el zorro]

I am currently experimenting with upsizing a front end/back end Access 2000
database (via the upsizing Wizard) and have found that keeping the front end
an mdb with the back end converted to SQL Server 2000 results in
excruciatingly SLOW execution speeds for the queries. As I understand it,
this is because the queries (about 50 of them) are being executed on each
workstation, not the server. Some of these queries are pretty complex, but
they are essential for most, if not all, of the reports and forms to function
correctly.

So I am now testing the other upsizing option that creates an adp file. Only
about half of the queries made the migration successfully, but preliminary
testing of the partsthat do work indicates that the adp approach will result
in much faster
response times for the user.

HOWEVER, I am concerned about the posts that suggest Microsoft is
dropping support for adp. One of the reasons I have been asked by the Powers
That Be to upgrade the database is to allow for future growth. I'm not sure I
can claim that adp is the future.

Are there any long term alternatives to adp that will allow me to migrate to
a more robust platform (with the queries on the server) without having to
completly reprogram my current Access database?
Thanks!

 

[Sylvain Lafontaine]

With MDB and ODBC linked tables, simple queries should be done on the
server, not locally. Simplify them or use Views and make sure that you
don't use VBA function in any JOIN or WHERE statements. Take a look with
the SQL-Server Profiler to make sure that your queries are executed on the
server, not locally.

For ADP, don't expect any new features in the future but I will be surprised
if MS is ever dropping the support for ADP without giving a replacement
solution: don't forget that a lot of big companies are relying on ADP at
this moment.

As alternatives, you can either use unbound forms in Access or go with .NET.

Finally, don't forget that even if you decide to go with ADP, you are not
really blocking the future growth because you will be able to develop in
parallel with both ADP and .NET and that a lot of T-SQL code will be
reusable with .NET because your queries will be already on the server.

 

[Robert Morley]

Another thing to look at, which Sylvain touched on, but didn't quite highlight is to write your queries on the SQL Server side, and
then attach them in Access as either Tables or Pass-through Queries. This should result in speeds comparable to ADP, though you
have the disadvantage that you have to worry about linking them in...not so much a problem if your server is reliably in one place,
but definitely an issue if you ever change servers or you have some kind of server fail-over topology going on. (If that last
sentence was Greek to you, you almost certainly aren't in either of those situations, so don't worry about it <g>.)


Rob

 

[Tom van Stiphout]

On Sat, 20 Oct 2007 21:45:19 -0400, "Sylvain Lafontaine" <sylvain aei
ca (fill the blanks, no spam please)> wrote:

If MSFT is still serious about ADP they have a funny way showing it.
See the lack of support for ADP in the Access 2007 runtime. One
problem was acknowledged by MSFT at the time of the release (was that
July - I forget) and there would be a QFE to fix the problem that you
couldn't run reports in ADP. I think they are past due to release that
QUICK fix. I reported two other glaring problems with the runtime
which so far remain unacknowledged and unfixed.
The full version of A2007 so far seems to be OK.

-Tom.

 

[Sylvain Lafontaine]

Where did I say that MS is still serious about ADP? There is only one place
where they are serious about support and (new) features and this place is
..NET.

One could argue that they are also serious about the future of JET with ODBC
linked tables as a frontend to SQL-Server; however, I find hard to see the
idea of taking ODBC linked tables as a front end to SQL-Server as anything
else as some kind of kludge. There are a good starting point if you don't
need any strong security but even when this is the case, their performance
will start to fail miserably when the complexity of the database will reach
a certain level of size and complexity.

When this fail start to appear, you can delay the ineluctable by using Views
and passthrough queries but passthrough queries are read-only and Views will
only give you a brief relief. The only long-term solution would then be to
use unbound forms but then, you will loose most of the simplicity and
advantages of using Access.

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

With MDB and ODBC linked tables, simple queries should be done on
the server, not locally.

Many of the stored queries will automatically be handed off by Jet
for processing by the SQL Server -- SHOWPLAN and SQLTRACE
demonstrate this quite clearly. It's only the queries on which Jet
guesses wrong, or that depend on Access features that will require
local processing, and even then, that processing may be shared with
the server, particular when there are joins and WHERE clauses that
can be used to limit the result that is retrieved to the
workstation.

Simplify them or use Views and make sure that you
don't use VBA function in any JOIN or WHERE statements.

Well, those can be used without huge performance penalties *if*
there is filtering on the tables beyond the expression-based JOIN or
WHERE criteria. I recently upsized an app that used a complex
function to join and I was very afraid it would need to be
completely redesigned. But with SQL Server it runs orders of
magnitude faster (and I really mean orderS, plural -- it's almost
instantaneous whereas it used to take up to a minute), without any
changes. That is because the join is between a field that is a value
returned from a function that takes values from a table that is
filtered on other criteria, and the other side of the join is a
small, 6-row local table. Jet is very smart, and tells the SQL
Server to do everything that can be done on the server, and then
takes the small set of rows that result and then processes them
locally with what needs to be done locally.

I was surprised, but in retrospect, it makes perfect sense to me.

Take a look with
the SQL-Server Profiler to make sure that your queries are
executed on the server, not locally.

Use Jet SHOWPLAN and SQLTRACE on the workstations along with
Profiler in order to get the whole picture. You might be surprised
at how much Jet gets right without any alteration (assuming you've
compacted the database after upsizing so that the saved queries will
all get recompiled for the SQL Server the next time they are
executed). And those queries that Jet guesses wrong on can easily be
converted to passthroughs or views or stored procedures.

 

[mcnews]

I am currently experimenting with upsizing a front end/back end Access 2000
database (via the upsizing Wizard) and have found that keeping the front end
an mdb with the back end converted to SQL Server 2000 results in
excruciatingly SLOW execution speeds for the queries. As I understand it,
this is because the queries (about 50 of them) are being executed on each
workstation, not the server. Some of these queries are pretty complex, but
they are essential for most, if not all, of the reports and forms to function
correctly.

So I am now testing the other upsizing option that creates an adp file. Only
about half of the queries made the migration successfully, but preliminary
testing of the partsthat do work indicates that the adp approach will result
in much faster
response times for the user.

HOWEVER, I am concerned about the posts that suggest Microsoft is
dropping support for adp. One of the reasons I have been asked by the Powers
That Be to upgrade the database is to allow for future growth. I'm not sure I
can claim that adp is the future.

Are there any long term alternatives to adp that will allow me to migrate to
a more robust platform (with the queries on the server) without having to
completly reprogram my current Access database?

watch out for nested queries (queries that call other queries).
these will definitely cause round trips and bog things down if the
nest goes too deep.

 

[David W. Fenton]

watch out for nested queries (queries that call other queries).
these will definitely cause round trips and bog things down if the
nest goes too deep.

What? That makes no sense to me. Can you provide a Jet SHOWPLAN,
SQLTRACE and Profiler output that demonstrates this?

 

[Tom van Stiphout]

On Sun, 21 Oct 2007 14:35:06 -0400, "Sylvain Lafontaine" <sylvain aei
ca (fill the blanks, no spam please)> wrote:

Points well taken. We're on the same page.
-Tom.