Slow (1 Viewer)

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

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.

Thanks for the reply,

Could you possibly go into a bit more detail with

so trying to allow the workstation to do the majority of the work is the best practice

Click to expand...

. 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

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!

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.

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

What you would pull over is only the records that are needed, not the entire table.