RE: Database running over network runs extremely slow

Comments inline:

"Dale Fye" wrote:

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.)

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

email address is invalid
Please reply to newsgroup only.



"Nicholas Scarpinato" wrote:

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?
.

Relevant Pages

  • Re: Database running over network runs extremely slow
    ... I totally agree with Dave about the "looped recordset searches", ... I have reports built into the database that track the entry ... you are searching (or using in your DLOOKUPs criteria parameter)? ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How many names and addresses can you have for ID card
    ... large amount of database space, ... Searching CLOBs is thus memory and CPU-intensive, ... A directory system a la LDAP or X.500 would be a much more natural ... An entry may have any number of atrributes which are ...
    (uk.legal)
  • Re: Exporting Text field line entries to individual records
    ... Been doing alot of searching on this group, and have not come up with ... Stuff that is entered in the field, followed by a carriage return: ... manually, except that are thousands of SP's in this database, and some ... calculation field that selects text between carriage returns. ...
    (comp.databases.filemaker)
  • Re: Mysql Search
    ... Try using explodeto put the data into an array, then searching them with ... > I'm not sure if I understand how your database looks like does - does it ... The user inputs a search word to an HTML form which then ... >> has that single letter anywhere in it. ...
    (alt.php)
  • Re: Is there anynone who knows a chatting site or someting like this? - Face Matching
    ... and then you would search your database to find matching ... advance by searching the internet retrieving ... URL and the feature vector to your database. ... You would compare the ...
    (sci.image.processing)