Database running over network runs extremely slow

[Nicholas Scarpinato]

Hello. I have an Access 2007 database that is currently used by three to five
people. I have an unbound entry form that is used to enter data, and on this
form a lot of the data that is entered is parsed via code. The code takes the
entered data, looks to see if that data is contained in various tables
(depending on what piece of data is entered), and then displays the data in
those tables. For example, when a barcode of a SKU is scanned, the database
finds the SKU in the Item Numbers table and displays the description and the
part number. The problem I'm having is that this searching process can take
up to 45 seconds to search a 3000 record table with four fields. Most of the
searching is done with Dlookups, but I'm converting some of it back to looped
recordset searches because they seem to be running a lot faster. So I guess
my question is, does anyone have some tips to help me improve the performance
of this database? Are looped recordset searches going to be faster than
DLookup searches over a linked database on a network?

 

[Dale Fye]

45 seconds is a long time to search 3000 records.

The first question I would ask is: do you have indexes setup on the fields
you are searching (or using in your DLOOKUPs criteria parameter)? I would
guess not based on the speed of your processing. This is a necessity if you
really want to make your application scream.

The second thing I would ask is: do you have your database split into
Front-end (Forms, queries, reports) and Back-end (data only)? If not, you
need to do this, and put the front-end on each users machine.

The third question I would ask is: are you users running the application on
a LAN or a WAN? If over a WAN, I suggest you read this paper by Albert
Kallal (http://members.shaw.ca/AlbertKallal/Wan/Wans.html)

Another question (I've run into this lately): Are your users hard wired to
the network, or are they running over a wireless lan? This will also
contribute to slow processing in Access.

In your example, you indicate that your form is unbound, and that when a
particular type of data is read, it searches the database for data and
returns that info, using DLOOKUPs. When working with a split database over a
LAN, I prefer to use bound forms, but I give the users unbound controls in
the forms header or provide seperate search/filter forms to allow the user to
search for specific data. I'm sure that this is faster than retreiving a
bunch of data using DLOOKUP statements.

HTH
Dale

 

[Nicholas Scarpinato]

Comments inline:

45 seconds is a long time to search 3000 records.

You're tellin' me... my boss flipped out when he saw how long this process
was taking. Our end users were complaning that it was taking them up to ten
minutes to process a single item... obviously that was a bit of an
exaggeration. I have reports built into the database that track the entry
times, so I know how long it was really taking... but it was still entirely
too long. The new changes I've made seem to have done the trick though, I
haven't heard any complaints since I switched my end users to the new front
end.

The first question I would ask is: do you have indexes setup on the fields
you are searching (or using in your DLOOKUPs criteria parameter)? I would
guess not based on the speed of your processing. This is a necessity if you
really want to make your application scream.

I didn't have them before, but I do now... in fact I had just done so today
before you replied. That, along with some minor code changes to convert some
of the DLookups to looped recordset searches, has made a dramatic improvement
in speed.

The second thing I would ask is: do you have your database split into
Front-end (Forms, queries, reports) and Back-end (data only)? If not, you
need to do this, and put the front-end on each users machine.

Yes, that was one of the first things I did after designing the basic table
structure.

The third question I would ask is: are you users running the application on
a LAN or a WAN? If over a WAN, I suggest you read this paper by Albert
Kallal (http://members.shaw.ca/AlbertKallal/Wan/Wans.html)

We're on a WAN, technically... I'm in Dallas and our network servers are
accessible to people in our San Francisco office, but we have our own network
share specificially for this database that nobody else uses, and that share
is local to our office.

Another question (I've run into this lately): Are your users hard wired to
the network, or are they running over a wireless lan? This will also
contribute to slow processing in Access.

Four of the computers that are being used in the system are all hooked up to
the same router via cable, although my machine is currently on a wireless
connection to that same router (it's not the ideal situation, but I just
moved to a new desk and they haven't set run a cable over to my machine yet).

In your example, you indicate that your form is unbound, and that when a
particular type of data is read, it searches the database for data and
returns that info, using DLOOKUPs. When working with a split database over a
LAN, I prefer to use bound forms, but I give the users unbound controls in
the forms header or provide seperate search/filter forms to allow the user to
search for specific data. I'm sure that this is faster than retreiving a
bunch of data using DLOOKUP statements.

I originally tried using a bound form, but it created some rather strange
issues due to the way this database is used. It just worked better to have an
unbound form that updated the table via an append query ran after each entry
is completed. As I mentioned previously, most of the DLookups on this form
have been converted to a looped recordset search, which seems to be working
much faster. (I'm sure putting indexes on all the fields helped a lot as
well.)

 

[Klatuu]

Dale gave some very good advice; however, I would like to offer one
suggestion you try to see how it improves performance. I am assuing your
SKU, description, and part number are all in the same table or could be
joined to create a query that would return those values. As you are having
performance issues with the DLookup, and I am not sure what you mean by
"looped recordset searches", but I would offer a suggestion.

In your unbound form, create a module level recordset that would return the
SKU, description and part number. The when the barcode is scanned, try using
a findfirst on your recordset:

With rstBarCodes
.FindFirst "[SKU] = """ & Me.txtSKU & """"
If .NoMatch Then
MsgBox "Item Not Found"
Else
'Do what you do with the data here
End If
End With

I would enstanciate the recordset in the form's Load event and use a
movelast, move first to fully populate the recordset.

I would really be interested in seeing if this would help.

 

[Dale Fye]

I totally agree with Dave about the "looped recordset searches", I wanted
to see if these other issues resolved some of your problems, first.

I still recommend if you have not read Alberts notes on running Access over
a WAN, you really need to. Also, you've gotta realize that your wireless
connection is a huge bottleneck in the application data flow.

Dale

 

[Nicholas Scarpinato]

Comments inline:

Dale gave some very good advice; however, I would like to offer one
suggestion you try to see how it improves performance. I am assuing your
SKU, description, and part number are all in the same table or could be
joined to create a query that would return those values. As you are having
performance issues with the DLookup, and I am not sure what you mean by
"looped recordset searches"...

Basically what I'm doing is a Do Until .EOF loop of the table until I find
what I'm looking for...

but I would offer a suggestion.

In your unbound form, create a module level recordset that would return the
SKU, description and part number. The when the barcode is scanned, try using
a findfirst on your recordset:

With rstBarCodes
.FindFirst "[SKU] = """ & Me.txtSKU & """"
If .NoMatch Then
MsgBox "Item Not Found"
Else
'Do what you do with the data here
End If
End With

I would enstanciate the recordset in the form's Load event and use a
movelast, move first to fully populate the recordset.

I would really be interested in seeing if this would help.

Will this FindFirst be faster than this:

Do Until .EOF
If .Fields("Fieldname") = Searchstring Then
~Do this~
Else
~Do nothing~
End if
..MoveNext
Loop

Or will it end up being pretty much the same? I think I'll try this method
tomorrow at work though, it seems cleaner than what I have now. Thank you for
the suggestion!

 

[John W. Vinson]

I still recommend if you have not read Alberts notes on running Access over
a WAN, you really need to. Also, you've gotta realize that your wireless
connection is a huge bottleneck in the application data flow.

.... and a huge risk of database corruption.

The need to use either a WAN or a wireless link is an excellent argument for
moving the backend to SQL/Server. Aaron's right about that.

 

[Nicholas Scarpinato]

I've read the aforementioned article, and yes, I agree with both of you on
this... I hate it, but for the time being I'm stuck on a wireless connection
while trying to admin this database, and it's already caused a couple of
crashes due to network connectivity losses. Our IT department is supposed to
be running CAT5 to my new desk soon, but they have to order more cable. (Of
course, the fact that I've also lost network connectivity when I WAS hooked
up to ethernet shows the kind of shape our network is in right now.)

Considering the scope of this database, moving it to an SQL server backend
would be in everybody's best interests... unfortunately, my company won't let
me do that because they say this is a "temporary solution until this process
can be built into the Web database structure". Essentially, my database is
the guinea pig for the web developers to follow behind and model their system
after. (I have no earthly idea how they plan to do 95% of what I've designed
via the Web format, but that's for them to figure out.)

 

[Klatuu]

The FindFirst would be faster.

Here is some code you can use for timing tests.

Option Compare Database
Option Explicit

Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Private lngStartTime As Long

Public Sub StartTimer()
lngStartTime = timeGetTime()
End Sub

Public Function StopTimer() As Long
StopTimer = timeGetTime - lngStartTime
End Function

Paste the code into a standard module.

Just before you execute the routine try
Debug.Print StartTimer

and just after
Debug.Print StopTimer

Calculate the difference.

I would be interested in the results between your loop method and the
FindFirst.
--
Dave Hargis, Microsoft Access MVP

Comments inline:

Dale gave some very good advice; however, I would like to offer one
suggestion you try to see how it improves performance. I am assuing your
SKU, description, and part number are all in the same table or could be
joined to create a query that would return those values. As you are having
performance issues with the DLookup, and I am not sure what you mean by
"looped recordset searches"...

Basically what I'm doing is a Do Until .EOF loop of the table until I find
what I'm looking for...

but I would offer a suggestion.

In your unbound form, create a module level recordset that would return the
SKU, description and part number. The when the barcode is scanned, try using
a findfirst on your recordset:

With rstBarCodes
.FindFirst "[SKU] = """ & Me.txtSKU & """"
If .NoMatch Then
MsgBox "Item Not Found"
Else
'Do what you do with the data here
End If
End With

I would enstanciate the recordset in the form's Load event and use a
movelast, move first to fully populate the recordset.

I would really be interested in seeing if this would help.

Will this FindFirst be faster than this:

Do Until .EOF
If .Fields("Fieldname") = Searchstring Then
~Do this~
Else
~Do nothing~
End if
.MoveNext
Loop

Or will it end up being pretty much the same? I think I'll try this method
tomorrow at work though, it seems cleaner than what I have now. Thank you for
the suggestion!

 

[Nicholas Scarpinato]

I thought that FindFirst might be faster, since it's a built in search
function. I knew there had to be a faster way to do this. Per your request, I
ran some tests comparing my current code to the code you posted, and here are
the results (Test 1 is my code, Test 2 is using FindFirst):

142760265 - Test 1 Start Timer
142760358 - Test 1 End Timer
93 - Test 1 Run Time
142760358 - Test 2 Start Timer
142760358 - Test 2 End Timer
0 - Test 2 Run Time

So, using FindFirst yielded the result immediately after it was called,
which is what I was hoping would be the case. I'm going to modify my code to
use this new method and make my end users happy.

The FindFirst would be faster.

Here is some code you can use for timing tests.

Option Compare Database
Option Explicit

Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Private lngStartTime As Long

Public Sub StartTimer()
lngStartTime = timeGetTime()
End Sub

Public Function StopTimer() As Long
StopTimer = timeGetTime - lngStartTime
End Function

Paste the code into a standard module.

Just before you execute the routine try
Debug.Print StartTimer

and just after
Debug.Print StopTimer

Calculate the difference.

I would be interested in the results between your loop method and the
FindFirst.
--
Dave Hargis, Microsoft Access MVP

Comments inline:

Dale gave some very good advice; however, I would like to offer one
suggestion you try to see how it improves performance. I am assuing your
SKU, description, and part number are all in the same table or could be
joined to create a query that would return those values. As you are having
performance issues with the DLookup, and I am not sure what you mean by
"looped recordset searches"...

Basically what I'm doing is a Do Until .EOF loop of the table until I find
what I'm looking for...

but I would offer a suggestion.

In your unbound form, create a module level recordset that would return the
SKU, description and part number. The when the barcode is scanned, try using
a findfirst on your recordset:

With rstBarCodes
.FindFirst "[SKU] = """ & Me.txtSKU & """"
If .NoMatch Then
MsgBox "Item Not Found"
Else
'Do what you do with the data here
End If
End With

I would enstanciate the recordset in the form's Load event and use a
movelast, move first to fully populate the recordset.

I would really be interested in seeing if this would help.

Will this FindFirst be faster than this:

Do Until .EOF
If .Fields("Fieldname") = Searchstring Then
~Do this~
Else
~Do nothing~
End if
.MoveNext
Loop

Or will it end up being pretty much the same? I think I'll try this method
tomorrow at work though, it seems cleaner than what I have now. Thank you for
the suggestion!

:

Hello. I have an Access 2007 database that is currently used by three to five
people. I have an unbound entry form that is used to enter data, and on this
form a lot of the data that is entered is parsed via code. The code takes the
entered data, looks to see if that data is contained in various tables
(depending on what piece of data is entered), and then displays the data in
those tables. For example, when a barcode of a SKU is scanned, the database
finds the SKU in the Item Numbers table and displays the description and the
part number. The problem I'm having is that this searching process can take
up to 45 seconds to search a 3000 record table with four fields. Most of the
searching is done with Dlookups, but I'm converting some of it back to looped
recordset searches because they seem to be running a lot faster. So I guess
my question is, does anyone have some tips to help me improve the performance
of this database? Are looped recordset searches going to be faster than
DLookup searches over a linked database on a network?

 

[Klatuu]

Glad I could help.
Thanks for sharing the results.
--
Dave Hargis, Microsoft Access MVP

I thought that FindFirst might be faster, since it's a built in search
function. I knew there had to be a faster way to do this. Per your request, I
ran some tests comparing my current code to the code you posted, and here are
the results (Test 1 is my code, Test 2 is using FindFirst):

142760265 - Test 1 Start Timer
142760358 - Test 1 End Timer
93 - Test 1 Run Time
142760358 - Test 2 Start Timer
142760358 - Test 2 End Timer
0 - Test 2 Run Time

So, using FindFirst yielded the result immediately after it was called,
which is what I was hoping would be the case. I'm going to modify my code to
use this new method and make my end users happy.

The FindFirst would be faster.

Here is some code you can use for timing tests.

Option Compare Database
Option Explicit

Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Private lngStartTime As Long

Public Sub StartTimer()
lngStartTime = timeGetTime()
End Sub

Public Function StopTimer() As Long
StopTimer = timeGetTime - lngStartTime
End Function

Paste the code into a standard module.

Just before you execute the routine try
Debug.Print StartTimer

and just after
Debug.Print StopTimer

Calculate the difference.

I would be interested in the results between your loop method and the
FindFirst.
--
Dave Hargis, Microsoft Access MVP

Comments inline:

:

Dale gave some very good advice; however, I would like to offer one
suggestion you try to see how it improves performance. I am assuing your
SKU, description, and part number are all in the same table or could be
joined to create a query that would return those values. As you are having
performance issues with the DLookup, and I am not sure what you mean by
"looped recordset searches"...

Basically what I'm doing is a Do Until .EOF loop of the table until I find
what I'm looking for...

but I would offer a suggestion.

In your unbound form, create a module level recordset that would return the
SKU, description and part number. The when the barcode is scanned, try using
a findfirst on your recordset:

With rstBarCodes
.FindFirst "[SKU] = """ & Me.txtSKU & """"
If .NoMatch Then
MsgBox "Item Not Found"
Else
'Do what you do with the data here
End If
End With

I would enstanciate the recordset in the form's Load event and use a
movelast, move first to fully populate the recordset.

I would really be interested in seeing if this would help.
--
Dave Hargis, Microsoft Access MVP

Will this FindFirst be faster than this:

Do Until .EOF
If .Fields("Fieldname") = Searchstring Then
~Do this~
Else
~Do nothing~
End if
.MoveNext
Loop

Or will it end up being pretty much the same? I think I'll try this method
tomorrow at work though, it seems cleaner than what I have now. Thank you for
the suggestion!




:

Hello. I have an Access 2007 database that is currently used by three to five
people. I have an unbound entry form that is used to enter data, and on this
form a lot of the data that is entered is parsed via code. The code takes the
entered data, looks to see if that data is contained in various tables
(depending on what piece of data is entered), and then displays the data in
those tables. For example, when a barcode of a SKU is scanned, the database
finds the SKU in the Item Numbers table and displays the description and the
part number. The problem I'm having is that this searching process can take
up to 45 seconds to search a 3000 record table with four fields. Most of the
searching is done with Dlookups, but I'm converting some of it back to looped
recordset searches because they seem to be running a lot faster. So I guess
my question is, does anyone have some tips to help me improve the performance
of this database? Are looped recordset searches going to be faster than
DLookup searches over a linked database on a network?

 

[Dale Fye]

Nick,

You can move to SQL Server Express for free, and it is a no-brainer to
migrate the data from Acces be to SQL Server using the Upsizing wizard.
Should also require very few changes to your code as well.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Klatuu]

I learned the hard way that when you use the upsizing wizard, do the upsize
on the BE database, then change your links in the FE. It really works much
better.

 

[David W. Fenton]

=?Utf-8?B?TmljaG9sYXMgU2NhcnBpbmF0bw==?=

I've read the aforementioned article, and yes, I agree with both
of you on this... I hate it, but for the time being I'm stuck on a
wireless connection while trying to admin this database, and it's
already caused a couple of crashes due to network connectivity
losses. Our IT department is supposed to be running CAT5 to my new
desk soon, but they have to order more cable. (Of course, the fact
that I've also lost network connectivity when I WAS hooked up to
ethernet shows the kind of shape our network is in right now.)

Can they set you up with a Windows Terminal Server logon that you
can run remotely across the wireless connection? That way you'd not
be pulling the actual database across the wire.