network performance 2

[Zorro1265]

I have an access database which runs acceptably on my local pc. When I put the database on our network which our IS guys says us 100baseT ethernet and fast according to them. The database is a slug. I have it split front end and backend tables and its still sloooooow. The total size of the database is less than 40megs, no images all text. Does this sound like a database or a network issue? I have a query that fills a listbox with strings of text and it may take 30 seconds for the listbox to close and free the mouse up to click on something else.

Help!

 

[petermeachem]

A database is much much slower over a network irrespective of the network speed. How many lines are you putting in the list box and how are you doing it.

I did a database with loads of subforms on each form (not my idea, the customers) which ran ok locally, but was taking over a minute to load the form on a network.

Worked ok after a bit of fiddling around.

 

[Zorro1265]

My list box calls a query and the lines range from 4 or 5 up to maybe 25. I have had this database on the network for quite sometime but before instead of the listbox I had comboboxes with value lists in the combobox. This combo version of the forms worked much much quicker but wasnt anywhere near as flexible as the listbox version. I am hoping its a network thing since I noticed as the day progresses the speed gets slower. This morning at 7 am it was a bit slower than off my hard drive, but not bad.

 

[DaveShmave]

Hi Zorro,

The problem, if you will, is that Access is essentially a file server (as opposed to SQL Server which is a database server) and therefore every single field and record is pulled across the network when you query a particular table. And the more complex the query the longer it takes.

So Peter is right that your situation probably has little to do with the speed of the network and more to do with Access itself and database design, which is sometimes unavoidable as he pointed out.

Network traffic, network speed are considerations but the way Access is designed there's little you can do short of some redesign. That assuming, the server is sound, et al.

Dave

 

[JimConrad]

If the data for the list box doesn't change frequently, consider putting that table into the front-end database.

Jim Conrad
JimConrad@Consultant.com

 

[petermeachem]

You will find, if you can look at network traffic, that the entire access table gets copied across the network and processing is done locally. Try to make sure that data is only read when you want it to be. It is very common in Access to have a form recordsource as a table name as it makes it easy to add fields, and then to just show a subset. For instance, the form recordsource may be Orders, and you then, as soon as the form is loaded have code that restricts it like recordsource = "SELECT this, that FROM Orders WHERE InputDate = " & format (now ,"dd mmm yyyy " ). This is a real slower and will read the table twice. Remove form recordsources wherever you can. On my real slow one, I ended up with buttons on the subforms to fill the recordsource for the subform as the users never wnated to see them all at the same time. Waiting a few seconds for a subform to load was ok. Joins were a real killer, as in read two huge tables, do the join locally and display 10 records. I added a lot of redundancy to the tables to get rid of the joins. That helped a lot.
Take a form at a time, do different things with it and see the effect on network traffic.

 

[Zorro1265]

Thanks Gang,
You gave me lots of things to look at. I think the first thing I will try is to put the table that is the source of the list box into my front end and see if that helps. You folks area wealth of information!

 

[skfoster]

40 M mdb is not too big. Check your network. Is there a switch between server and hub? With or without switch will make big difference. Does NIC set to auto or full/half duplex in both server and workstation? As my experience, auto is fast. Also, try use pass through query in your application. Finally, you can upsize all data table to SQL. At that time, you can fly.

 

[mdavis1]

hey sherman..can u help me with the "upsize all data table to SQL".
thanx
mdavis1

 

[nrjhano]

There is an ongoing discussion where I was suggested to run queries like a stored procedure in the back-end. Search on my handle and you can take part. It can be done and as you will see the remaining question is to organise update of queries in a multi-user back-end environment, I.

The advantages are that joins are powered by back-end server and only the essential data is transfered to the front-end, ;-).

It's an interesting solution and I am convinced it can be made to work, however, at this very moment I have little time to put together the bits and pieces required for a final solution, .

Good luck,
Hakan
Shin-Yokohama 2001-04-24