How can I speed up an Access database with SQL backend.

Here is the scenario. We have a database with patient case information.
This database was previously solely used in Access. The problem is our
users connect to this database (located in California) from locations
across a large network. California to Virginia. Naturally the east
coast locations were running very slow. We decided to migrate to an
Access front end with an SQL backend. The database contains several
forms with multiple sub-forms in each. In some instances the sub-form
has a fairly large Query as its recordset. As is, we are strictly using
SELECT statements in the recordset property value in Access. This seems
to be the bottleneck of the database. I have tried Store Procedure
calls using Pass Through queries and set them as the recordset but
these forms need to be editable and the return recordset is not
editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern

Matt.W.Stephens@gmail.com wrote:

Quote:

Here is the scenario. We have a database with patient case
information. This database was previously solely used in Access. The
problem is our users connect to this database (located in California)
from locations across a large network. California to Virginia.
Naturally the east coast locations were running very slow. We decided
to migrate to an Access front end with an SQL backend. The database
contains several forms with multiple sub-forms in each. In some
instances the sub-form has a fairly large Query as its recordset. As
is, we are strictly using SELECT statements in the recordset property
value in Access. This seems to be the bottleneck of the database. I
have tried Store Procedure calls using Pass Through queries and set
them as the recordset but these forms need to be editable and the
return recordset is not editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern

You need to change to a system where the user indicates what ONE RECORD they
need and then retrieve that one record for editing. If ONE is not possible
then you use criteria that will pull the absolute minimum required to do the
job. Pull the records for the subforms one at a time and only when
required.

Standard Access forms with "Record 1 of SomeBigNumber" in the navigation bar
are just not the way to go over a WAN connection.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Matt.W.Stephens@gmail.com wrote:

Quote:

Here is the scenario. We have a database with patient case information.
This database was previously solely used in Access. The problem is our
users connect to this database (located in California) from locations
across a large network. California to Virginia. Naturally the east
coast locations were running very slow. We decided to migrate to an
Access front end with an SQL backend. The database contains several
forms with multiple sub-forms in each. In some instances the sub-form
has a fairly large Query as its recordset. As is, we are strictly using
SELECT statements in the recordset property value in Access. This seems
to be the bottleneck of the database. I have tried Store Procedure
calls using Pass Through queries and set them as the recordset but
these forms need to be editable and the return recordset is not
editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern
>

Have you already split your access database into a front end and back
end. If not do that first.

Second, the way to make things run faster across any network is to
reduce the amount of network traffic. In other words, use data
selection to reduce the quantity of data being queried in main and/or
sub queries.

Thirdly, Access databases do not work well over WANS because of the
potential for corruption if the link fails.

I wonder if database replication could work for you? The key would be
the speed in which updates must be available to all users and the
potential for concurrent updating of the same data.

More info on what you are doing would be helpful.

Bob

For purely speed, I would ditch Access and go with ASP/ASP.NET hosted
on a server. That would be your fastest solution, but your setup costs
will be a bit higher. If the database is only a couple of forms, that
should keep costs down.

Chris Nebinger

Matt.W.Stephens@gmail.com wrote:

Quote:

Here is the scenario. We have a database with patient case information.
This database was previously solely used in Access. The problem is our
users connect to this database (located in California) from locations
across a large network. California to Virginia. Naturally the east
coast locations were running very slow. We decided to migrate to an
Access front end with an SQL backend. The database contains several
forms with multiple sub-forms in each. In some instances the sub-form
has a fairly large Query as its recordset. As is, we are strictly using
SELECT statements in the recordset property value in Access. This seems
to be the bottleneck of the database. I have tried Store Procedure
calls using Pass Through queries and set them as the recordset but
these forms need to be editable and the return recordset is not
editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern

"Bob Alston" <bobalston9@yahoo.comwrote

Quote:

>Access front end with an SQL backend.

Quote:

Thirdly, Access databases do not work well over
WANS because of the potential for corruption
if the link fails.

But, according to the original poster's comment above, it is not an Access
database, but client-server with an Access frontend. Your caution it does
not apply to client-server with an SQL Server back-end, in any case.

Rick's advice about redesigning the interface so as to minimize network
traffic is the single-most-helpful thing I know to do with C/S databases.
It is really amazing, if you really look at the _requirements_, how often
you need just ONE record, if it exists, or NONE, if the record doesn't
exist.

Larry Linson
Microsoft Access MVP

As quoted by Bob: I wonder if database replication could work for you

This would be the avanue I wouuld be looking at.
Setup each site with MSDE and create replication to the Parent Server.
Then each site would have a local query to a SQL server, and speed
would be greatly improved.

Larry Linson wrote:

Quote:

"Bob Alston" <bobalston9@yahoo.comwrote
>

Quote:

>Access front end with an SQL backend.

>

Quote:

Thirdly, Access databases do not work well over
WANS because of the potential for corruption
if the link fails.

>
But, according to the original poster's comment above, it is not an Access
database, but client-server with an Access frontend. Your caution it does
not apply to client-server with an SQL Server back-end, in any case.
>
Rick's advice about redesigning the interface so as to minimize network
traffic is the single-most-helpful thing I know to do with C/S databases.
It is really amazing, if you really look at the _requirements_, how often
you need just ONE record, if it exists, or NONE, if the record doesn't
exist.
>
Larry Linson
Microsoft Access MVP
>
>

Good point Larry. I read too fast. I thought while they had decided to
switch to SQL, they were still using Jet. My error.

So perhaps my second point, is in agreement:
"Second, the way to make things run faster across any network is to
reduce the amount of network traffic. In other words, use data
selection to reduce the quantity of data being queried in main and/or
sub queries. "

Bob

If you retrieve the info using a pass-through query and the performance
is good, then you have the bandwidth you need.

To update the data, you will need to write some code.

You can create a button on the form to save the data. You will need to
update the record via SQL commands.

Do a search for unbound forms.

Matt.W.Stephens@gmail.com wrote:

Quote:

Here is the scenario. We have a database with patient case information.
This database was previously solely used in Access. The problem is our
users connect to this database (located in California) from locations
across a large network. California to Virginia. Naturally the east
coast locations were running very slow. We decided to migrate to an
Access front end with an SQL backend. The database contains several
forms with multiple sub-forms in each. In some instances the sub-form
has a fairly large Query as its recordset. As is, we are strictly using
SELECT statements in the recordset property value in Access. This seems
to be the bottleneck of the database. I have tried Store Procedure
calls using Pass Through queries and set them as the recordset but
these forms need to be editable and the return recordset is not
editable.
My question is, Is there a more efficient way to get an editable
recordset into these forms. Speed is our main concern

We are currently using Replication. Each location has a local file that
an admin synchronizes every night with the back end. This process is
very time consuming as the material is pretty time sensitive.

I have created a form with search criteria that reduces the amount of
records significantly. Usually searching by Social Security Number so
at most 3 or 4 records would be available. I am doing this using a
dynamic Select Statement changing my WHERE clause based on the criteria
selected. But it is my understanding that by using a Select and not a
Stored Procedure the whole recordset set is being filtered on the user
side and not on the SQL server. Is this correct?

Anthos wrote:

Quote:

As quoted by Bob: I wonder if database replication could work for you
>
This would be the avanue I wouuld be looking at.
Setup each site with MSDE and create replication to the Parent Server.
Then each site would have a local query to a SQL server, and speed
would be greatly improved.

Depends.

In my testing, which I'm sure is not as extensive as some others, this was
the fastest way to access data over a WAN:

Assuming a local table and a matching server table, using Access here:
---------------------
Dim f As Integer
Dim cnxn As ADODB.Connection
Dim rS As ADODB.Recordset
Dim rL As ADODB.Recordset
Set cnxn = New ADODB.Connection
Set rS = New ADODB.Recordset ' Server
Set rL = New ADODB.Recordset ' Local

rL.Open "table1", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable

cnxn.Provider = "Microsoft.Jet.OLEDB.4.0" ' Access
cnxn.Open "Data Source=" & ' Server

rS.Open "SELECT * FROM table1 WHERE table1.ssn=' & Me.txtSSN & " ' ", _
cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText

Do While Not rS.EOF
rL.AddNew
For f = 0 To rL.Fields.count - 1
rL.Fields(f) = rS.Fields(f)
Next f
rL.Update
rS.MoveNext
Loop
rS.Close
rL.Close
cnxn.Close
----------------

Of course, if just retrieving a value, not storing it for further use, then
a simple SELECT works faster, but for most of my use, I was copying a client
record to the local tables to manipulate, then saving back to the server at
the end.

Passing parameters to a query/stored procedure on the backend .mdb sped up
large queries; I assume that it would be even faster with SQL Server.

Of course, I made every effort to not download text fields, using every
locally stored lookup table I could.

--
Darryl Kerkeslager

<Matt.W.Stephens@gmail.comwrote

Quote:

I have created a form with search criteria that reduces the amount of
records significantly. Usually searching by Social Security Number so
at most 3 or 4 records would be available. I am doing this using a
dynamic Select Statement changing my WHERE clause based on the criteria
selected. But it is my understanding that by using a Select and not a
Stored Procedure the whole recordset set is being filtered on the user
side and not on the SQL server. Is this correct?

Thanks I think that may work. I have also noticed an great increas in
performance by Deselecting the "Track Name AutoCorrect" options. As
well as setting the subdatasheet view to [None]. A couple simple things
that I wasn't aware of to increase performance.

Darryl Kerkeslager wrote:

Quote:

Depends.
>
In my testing, which I'm sure is not as extensive as some others, this was
the fastest way to access data over a WAN:
>
Assuming a local table and a matching server table, using Access here:
---------------------
Dim f As Integer
Dim cnxn As ADODB.Connection
Dim rS As ADODB.Recordset
Dim rL As ADODB.Recordset
Set cnxn = New ADODB.Connection
Set rS = New ADODB.Recordset ' Server
Set rL = New ADODB.Recordset ' Local
>
rL.Open "table1", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable
>
cnxn.Provider = "Microsoft.Jet.OLEDB.4.0" ' Access
cnxn.Open "Data Source=" & ' Server
>
rS.Open "SELECT * FROM table1 WHERE table1.ssn=' & Me.txtSSN & " ' ", _
cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText
>
Do While Not rS.EOF
rL.AddNew
For f = 0 To rL.Fields.count - 1
rL.Fields(f) = rS.Fields(f)
Next f
rL.Update
rS.MoveNext
Loop
rS.Close
rL.Close
cnxn.Close
----------------
>
Of course, if just retrieving a value, not storing it for further use, then
a simple SELECT works faster, but for most of my use, I was copying a client
record to the local tables to manipulate, then saving back to the server at
the end.
>
Passing parameters to a query/stored procedure on the backend .mdb sped up
large queries; I assume that it would be even faster with SQL Server.
>
Of course, I made every effort to not download text fields, using every
locally stored lookup table I could.
>
--
Darryl Kerkeslager
>
<Matt.W.Stephens@gmail.comwrote
>

Quote:

I have created a form with search criteria that reduces the amount of
records significantly. Usually searching by Social Security Number so
at most 3 or 4 records would be available. I am doing this using a
dynamic Select Statement changing my WHERE clause based on the criteria
selected. But it is my understanding that by using a Select and not a
Stored Procedure the whole recordset set is being filtered on the user
side and not on the SQL server. Is this correct?

Thanks I think that may work. I have also noticed an great increase in
performance by Deselecting the "Track Name AutoCorrect" options. As
well as setting the subdatasheet view to [None]. A couple simple things
that I wasn't aware of to increase performance.

Darryl Kerkeslager wrote:

Quote:

Depends.
>
In my testing, which I'm sure is not as extensive as some others, this was
the fastest way to access data over a WAN:
>
Assuming a local table and a matching server table, using Access here:
---------------------
Dim f As Integer
Dim cnxn As ADODB.Connection
Dim rS As ADODB.Recordset
Dim rL As ADODB.Recordset
Set cnxn = New ADODB.Connection
Set rS = New ADODB.Recordset ' Server
Set rL = New ADODB.Recordset ' Local
>
rL.Open "table1", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable
>
cnxn.Provider = "Microsoft.Jet.OLEDB.4.0" ' Access
cnxn.Open "Data Source=" & ' Server
>
rS.Open "SELECT * FROM table1 WHERE table1.ssn=' & Me.txtSSN & " ' ", _
cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText
>
Do While Not rS.EOF
rL.AddNew
For f = 0 To rL.Fields.count - 1
rL.Fields(f) = rS.Fields(f)
Next f
rL.Update
rS.MoveNext
Loop
rS.Close
rL.Close
cnxn.Close
----------------
>
Of course, if just retrieving a value, not storing it for further use, then
a simple SELECT works faster, but for most of my use, I was copying a client
record to the local tables to manipulate, then saving back to the server at
the end.
>
Passing parameters to a query/stored procedure on the backend .mdb sped up
large queries; I assume that it would be even faster with SQL Server.
>
Of course, I made every effort to not download text fields, using every
locally stored lookup table I could.
>
--
Darryl Kerkeslager
>
<Matt.W.Stephens@gmail.comwrote
>

Quote:

I have created a form with search criteria that reduces the amount of
records significantly. Usually searching by Social Security Number so
at most 3 or 4 records would be available. I am doing this using a
dynamic Select Statement changing my WHERE clause based on the criteria
selected. But it is my understanding that by using a Select and not a
Stored Procedure the whole recordset set is being filtered on the user
side and not on the SQL server. Is this correct?

Efficient use of indexes also greatly increases speed.

Always using transactions may also speed up this code.

Also, if you time this code at key points, you will likely find that the
actual opening of the connection can be the biggest bottleneck, so try to
open, do lots of SQL at once, then close.

--
Darryl Kerkeslager

<Matt.W.Stephens@gmail.comwrote

Quote:

Thanks I think that may work. I have also noticed an great increas in
performance by Deselecting the "Track Name AutoCorrect" options. As
well as setting the subdatasheet view to [None]. A couple simple things
that I wasn't aware of to increase performance.