Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-11-2003, 09:50 AM   #1
CALV
Registered User
 
Join Date: Apr 2001
Location: UK
Posts: 74
Thanks: 0
Thanked 0 Times in 0 Posts
CALV
Slow

Hi all,

I have just about finished my db, the back end sits on an nt4 server, the fe's on nt4 workstations, now when I test this at home, its perfect, even with 200k records in it, its pretty fast, but at work, its SLOW (even with only about 10000 records), the basic setup is that the user enters a customer postcode/house number and then it (the db) will bring up previous history on a subform, and allow entry of new info on the main form, which is then updated with a cmd button, but its very slow, it even takes like ages to open the db, I also noticed, that going from design view to form view, or vice versa, theres a lot of netwrok activity, I assume I went wrong somewhere?

TIA

CALV

CALV is offline   Reply With Quote
Old 08-11-2003, 11:06 AM   #2
jeremie_ingram
Registered User
 
jeremie_ingram's Avatar
 
Join Date: Jan 2003
Location: Chicago
Posts: 437
Thanks: 0
Thanked 0 Times in 0 Posts
jeremie_ingram
Are you basing the forms and such directly from the tables or from queries? When you are in the network environment traffic and network speed are the two biggest hurdles to overcome, so trying to allow the workstation to do the majority of the work is the best practice. If you limit the communication between the FE and BE to simple queries, the response should increase greatly.
I encountered this situation when dealing with an extremely large BE, and it was pointed out to me that I should redesign the interface (FE) to cut down on the BE dependency.
jeremie_ingram is offline   Reply With Quote
Old 08-11-2003, 11:52 AM   #3
CALV
Registered User
 
Join Date: Apr 2001
Location: UK
Posts: 74
Thanks: 0
Thanked 0 Times in 0 Posts
CALV
Thanks for the reply,

Could you possibly go into a bit more detail with
Quote:
so trying to allow the workstation to do the majority of the work is the best practice
. The reports that are pulled off were REALLY slow, so what I did was run a basic select query, then using DAO I copied the data that the query produced into a local table, and ran all other queries etc from this table which speeded things up somewhat. In what way did you need to redesign the front end? this is an option for me at the oment, but I have no clue as to what I need to change!

CALV

CALV is offline   Reply With Quote
Old 08-11-2003, 11:53 AM   #4
Newman
Québécois
 
Newman's Avatar
 
Join Date: Aug 2002
Location: Montréal
Posts: 766
Thanks: 0
Thanked 1 Time in 1 Post
Newman is on a distinguished road
I could use some exemples, if you don't mind. I had such problems before and I am now looking towards MSDE.
Is your way better or easier to use?
How can I improve my FE?
I have a table with ~70k row and ~30 fields.
My reports and forms all use queries, but it is a little bit to slow.
Thanks for your help!
__________________
Last night, I was in bed looking up at the stars in the sky and thought to myself, “Where the heck is the ceiling?”
Newman is offline   Reply With Quote
Old 08-11-2003, 01:15 PM   #5
jeremie_ingram
Registered User
 
jeremie_ingram's Avatar
 
Join Date: Jan 2003
Location: Chicago
Posts: 437
Thanks: 0
Thanked 0 Times in 0 Posts
jeremie_ingram
I had once created a FE BE situation, and even thought the FE contained the reports, the queries were still targeting the BE. When you ran a report, it would take forever to print. When you run a simple query from FE to BE, the data transfer is quick. However, when you get into the logistics of running forms and reports based off of queries linked to the BE, the get bogged/slowed down.
I resolved this by triggering the query to pull ALL of the record data from the BE to the FE when they entered the client #. It was placed into a temp tbl that was cleared out on print and on close.

For the situation defined in the original question. What if you were to create a tbl to temporarily hold the data? Have the users enter a customer postcode/house number, run a query to the BE that appends the necessary data to a temp tbl in the FE. When they update it via the form, use the update query to send it back.

In basics "trying to allow the workstation to do the majority of the work” basically means to bring the data over as needed in increments. When you have it locally, manipulate/alter/use the data as needed. If changes need to be saved, use the update query to send it to the BE. I have found that this works out allot better in the cases I have dealt with.

There is another plus point. I had a situation that required me to track changes in data to help keep statistical data. I brought the new data into the FE and compared it to the BE. Once I had defined the differences, I ran queries to 1) move the data from the main BE tbl to the BE history tbl 2) change only those records fields that were necessary.
jeremie_ingram is offline   Reply With Quote
Old 08-11-2003, 01:50 PM   #6
CALV
Registered User
 
Join Date: Apr 2001
Location: UK
Posts: 74
Thanks: 0
Thanked 0 Times in 0 Posts
CALV
Thanks for the reply, it sort of makes sense, only I have no clue on how to implement it!, you mention pulling all the data to the front end, then running an append query, wont this slow things down more if I drag the whole table accross the netwrok everytime details are entered?

Thanks

CALV
CALV is offline   Reply With Quote
Old 08-11-2003, 04:01 PM   #7
jeremie_ingram
Registered User
 
jeremie_ingram's Avatar
 
Join Date: Jan 2003
Location: Chicago
Posts: 437
Thanks: 0
Thanked 0 Times in 0 Posts
jeremie_ingram
What you would pull over is only the records that are needed, not the entire table.


jeremie_ingram is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 04:54 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World