network traffic

[Sam]

I have a MS Access 2003 application connecting to a SQl 2008 backend. Some
tables have 50,000 to 100,000 records but not larger, with about 15 users.

When I upgraded to SQL 2008, I immediately began having network traffic
issues, such as complete stalling of some users if more than 2 or 3 computers
were logged in. The app was very fast with just one person. I have now
disconnected all linked tables from within MS Access, and all data is
delivered via passthrough queries.

The stalling issues are clearly improved but not solved. I use a couple
hundred passthroughs. Could they be causing overload of my network? My next
option would
be to import some data into local tables, but before doing that I wondered
how likely this would be to help.
Thanks,
Sam

[Sylvain Lafontaine]

Even when using passthrough queries, you should always make sure to retrieve
on the network only the necessary records. For example, when a form open
and until the user make some sort of selection about what he want, no record
should be shown or if you chose to show the last records, only the very last
one or a few of them should be shown.

Usually, if you can program your form so that only one record is shown at a
time, your application should be quite snappy.

If you want your user to be able to navigate between the records without you
having to requery the form each time for a new form, you should consider the
possibility of retrieving only a few tens or a few hundreds at most because
it's not likely that the user will navigate through 50,000 records one by
one.

In your case, as you have hundreds of passthrough queries, you should have
used an ADP project instead of MDB/ACCDB with ODBC Linked Tables and
passthrough queries. From the moment that you have disconnected all the
linked tables, keeping using MDB instead of an ADP project is totally
pointless and counter-productive.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Sam
I have a MS Access 2003 application connecting to a SQl 2008 backend. Some
tables have 50,000 to 100,000 records but not larger, with about 15 users.
When I upgraded to SQL 2008, I immediately began having network traffic
issues, such as complete stalling of some users if more than 2 or 3 computers
were logged in. The app was very fast with just one person. I have now
disconnected all linked tables from within MS Access, and all data is
delivered via passthrough queries.
The stalling issues are clearly improved but not solved. I use a couple
hundred passthroughs. Could they be causing overload of my network? My next
option would
be to import some data into local tables, but before doing that I wondered
how likely this would be to help.
Thanks,
Sam

[Sam]

Thanks. This is helpful.
This application has grown over the years and thus the original .mdb
I agree that it is now pointless to be using the .mdb app but the last time
I tried to convert to .adp it looked like considerable work. Would there be
some advantage? Would this help speed things up?

Sam

Post by Sylvain Lafontaine
Even when using passthrough queries, you should always make sure to retrieve
on the network only the necessary records. For example, when a form open
and until the user make some sort of selection about what he want, no record
should be shown or if you chose to show the last records, only the very last
one or a few of them should be shown.
Usually, if you can program your form so that only one record is shown at a
time, your application should be quite snappy.
If you want your user to be able to navigate between the records without you
having to requery the form each time for a new form, you should consider the
possibility of retrieving only a few tens or a few hundreds at most because
it's not likely that the user will navigate through 50,000 records one by
one.
In your case, as you have hundreds of passthrough queries, you should have
used an ADP project instead of MDB/ACCDB with ODBC Linked Tables and
passthrough queries. From the moment that you have disconnected all the
linked tables, keeping using MDB instead of an ADP project is totally
pointless and counter-productive.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Sam
I have a MS Access 2003 application connecting to a SQl 2008 backend. Some
tables have 50,000 to 100,000 records but not larger, with about 15 users.
When I upgraded to SQL 2008, I immediately began having network traffic
issues, such as complete stalling of some users if more than 2 or 3 computers
were logged in. The app was very fast with just one person. I have now
disconnected all linked tables from within MS Access, and all data is
delivered via passthrough queries.
The stalling issues are clearly improved but not solved. I use a couple
hundred passthroughs. Could they be causing overload of my network? My next
option would
be to import some data into local tables, but before doing that I wondered
how likely this would be to help.
Thanks,
Sam

.

[Sylvain Lafontaine]

The fastest mode is to use totally unbound forms and controls because you
have 100% control over what's transmitted over the network but it also
require a lot more of work.

Even with bound forms and controls, the .NET Framework is a close call to
that because you control what and when anything is going over the network
but programming with the .NET framework is so different from VBA that
switching to .NET will require an even greater amount of work than going
with totally unbound forms and controls under Access.

ADP is not as fast as .NET or unbound forms and controls but it is
considerably faster than ODBC Linked Tables and at the same speed as
passthrough queries. However, passthrough queries are read-only while ADP
is read-write.

Think of ADP as using read-write passthrough queries instead of read-only
passthrough queries as it is with a MDB or ACCDB database file.

These two (ADP vs passthrough queries) are not exactly the same because ADP
use ADO while passthrough queries use DAO but the fact of using either ADO
or DAO is insignificant by itself under these circumstances.

It's a certainty that converting a project to ADP requires some work to do;
like anything else but especially if you don't know much about ADP in the
first place.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Sam
Thanks. This is helpful.
This application has grown over the years and thus the original .mdb
I agree that it is now pointless to be using the .mdb app but the last time
I tried to convert to .adp it looked like considerable work. Would there be
some advantage? Would this help speed things up?
Sam

Post by Sylvain Lafontaine
Even when using passthrough queries, you should always make sure to retrieve
on the network only the necessary records. For example, when a form open
and until the user make some sort of selection about what he want, no record
should be shown or if you chose to show the last records, only the very last
one or a few of them should be shown.
Usually, if you can program your form so that only one record is shown at a
time, your application should be quite snappy.
If you want your user to be able to navigate between the records without you
having to requery the form each time for a new form, you should consider the
possibility of retrieving only a few tens or a few hundreds at most because
it's not likely that the user will navigate through 50,000 records one by
one.
In your case, as you have hundreds of passthrough queries, you should have
used an ADP project instead of MDB/ACCDB with ODBC Linked Tables and
passthrough queries. From the moment that you have disconnected all the
linked tables, keeping using MDB instead of an ADP project is totally
pointless and counter-productive.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Sam
I have a MS Access 2003 application connecting to a SQl 2008 backend. Some
tables have 50,000 to 100,000 records but not larger, with about 15 users.
When I upgraded to SQL 2008, I immediately began having network traffic
issues, such as complete stalling of some users if more than 2 or 3 computers
were logged in. The app was very fast with just one person. I have now
disconnected all linked tables from within MS Access, and all data is
delivered via passthrough queries.
The stalling issues are clearly improved but not solved. I use a couple
hundred passthroughs. Could they be causing overload of my network? My next
option would
be to import some data into local tables, but before doing that I wondered
how likely this would be to help.
Thanks,
Sam

.

[Max Right]

Sylvain, I'm experiencing the same issue, even with just a few user
connections to sql

is it possible(either in Access(vba) or Sql) to limit the number of
records being retreived by forms/controls?

what I'm trying to avoid is rewritting all my forms/controls to be
unbound.

[Armen Stein]

On Tue, 23 Mar 2010 11:08:57 -0700 (PDT), Max Right

Post by Max Right
Sylvain, I'm experiencing the same issue, even with just a few user
connections to sql
is it possible(either in Access(vba) or Sql) to limit the number of
records being retreived by forms/controls?

Access naturally limits the number of records retrieved all at once,
but you can help by making sure your bound forms (especially updatable
ones) are opened to the smallest recordsets possible.

Post by Max Right
what I'm trying to avoid is rewritting all my forms/controls to be
unbound.

There are a lot of things to try before taking the drastic step of
moving to unbound forms. We've built lots of large, complex systems
with Access MDB + SQL Server without needing unbound forms.

I've written a PowerPoint presentation on techniques for using Access
as a client-server front-end to SQL Server databases. It's called
"Best of Both Worlds" at our free J Street Downloads page:
http://ow.ly/M2WI. It includes some thoughts on when to use SQL
Server, performance and security considerations, concurrency
approaches, and techniques to help everything run smoothly.

A shout-out to Sylvain who helped me with the slide on RowVersion
impacts on concurrency.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Max Right]

Armen,

Thank you for your responce, I have upsized my tables successfuly and
the forms are running fine, however, the reports are taking 20-30
minutes, when usually they take 10-60sec in access.

I have 4 tables in SQL they each have 20k-200k rows.

I believe that the reason for my slowness is my queries. I have about
20 complex queries that run prior to the report being built. correct
me if I'm wrong here, the queries are requesting all those records
from SQL tables, then performing the calculations localy, which is why
this is taking so long.

Is there a way for me to export my queries to SQL, so that all the
calculations can be done on the server?
do they go into "views", "stored procedures" or "functions"?
My queries use iif, <>, sin(), cos() and other similar functions... so
does this mean I would have to split them up?

I downloaded and read your ppt, that helped a lot in getting my forms
to display quicker, hopefully I can do the same thing for my
queries...

[Armen Stein]

On Thu, 25 Mar 2010 11:51:10 -0700 (PDT), Max Right

Post by Max Right
I believe that the reason for my slowness is my queries. I have about
20 complex queries that run prior to the report being built. correct
me if I'm wrong here, the queries are requesting all those records
from SQL tables, then performing the calculations localy, which is why
this is taking so long.
Is there a way for me to export my queries to SQL, so that all the
calculations can be done on the server?
do they go into "views", "stored procedures" or "functions"?
My queries use iif, <>, sin(), cos() and other similar functions... so
does this mean I would have to split them up?

Complex Access queries, especially if they have local Access
functions, are certainly running on the client.

The quickest thing to try is moving the Select statement with all the
basic fields and joins to a SQL Server View. Run that from Management
Studio - it should be nice and quick.

Then base your Access queries on that View (hooked up as a linked
table). Add any calculations or functions you need to the Access
query there. Overall it might run faster by splitting some of that
processing to the back-end.

If that isn't enough, you may need to go to the next step of
recreating your logic in SQL Views, or if it's too complex, Stored
Procedures. This will mean rewriting all your logic in T-SQL, but it
probably can be done.

You can use passthrough queries to use SQL tables, views and sprocs
from Access, but be aware that they are read-only, and you can't use
Master/Child (for subreports) when a passthrough is involved.

Another approach is to base your complex reports on local work tables,
which are merely Access tables that are emptied, reloaded with
selected records and recalculated when the report runs. This can be a
very high performance approach too.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com