UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Perplexed By Slow Database ... Please Help!, Access 2010    
 
   
treymarie
post Jun 14 2016, 03:13 PM
Post#1



Posts: 149
Joined: 21-June 02
From: Maryland


I have been doing Access development for 20 years and this is the most frustrating problem yet. I have a new database I have created from scratch. I have the Access backend on the server and Access frontends are distributed to users desktops. When I test it, it's lightning fast. As soon as only one other person opens the database, even if they are just on the main switchboard and not opening any other forms, it immediately slows down to about half speed! We have tried this with various people over various machines and it's the same result. There is a noticeable delay moving from record to record, clicking on tabs, etc. I admit the main form is somewhat complex ... lots of tabs with subforms. This is where the slowness mostly occurs. However, I have done just about everything the experts have recommended as far as improving performance and it has helped a little, but not enough. Still a very noticeable delay. I have done the following:

• Only loading one record at a time instead of all records (only loading the record the user wants to look at).
• Create a permanent connection to backend by having main switchboard based on small table with one record.
• Only loading subforms or combobox sources if the user clicks on the tab with that subform or combobox.
• Changed primary keys from Descending to Ascending (only had this on one table and a few related child tables).
• Replacing all domain aggregate functions (DCount / DLookup) to recordset counts or "ELookup" function in VBA and queries.
• Do more functions up front only once when the form loads, rather than on Form_Current.
• Add primary keys to all tables (I only had 3 tables without primary keys, but all have unique indexes)
• Replace Nz functions with IIf in queries
• Analyze Group By queries and make sure they are efficient
• Adding Option Explicit to all modules
• Any forms based on tables ... use queries instead if I don’t need all fields.
• Uncheck “Use Windows-Themed Controls in Forms” on database options.
• Turning on unicode compression for all text and memo fields (table property).

I have always had the basic performance enhancements in place ... turning off subdatasheets, turning off autocorrect, etc.

The other odd thing is ... if I test it only on my machine, but open three separate occurrences (three separate front-end copies on my machine, pretending to be three different users), it is lightning fast on all three, even when all instances are in the same large form and doing various things on the tabs.

I am puzzled and frustrated and not sure what to do at this point. I thought about possibly moving the backend to SQL Server, but some say that may not improve the speed. Could there be a server or network issue? The IT people said the bandwidth is 100MB, although one guy said in this day and age, it should really be 1G. Don't know if that is an issue or not.

Please help!
Go to the top of the page
 
BitBangerUSA
post Jun 14 2016, 07:03 PM
Post#2



Posts: 45
Joined: 20-March 16



i would ask IT if the people/machines slowing things down are on a different network switch/router than you.
Go to the top of the page
 
treymarie
post Jun 15 2016, 07:50 AM
Post#3



Posts: 149
Joined: 21-June 02
From: Maryland


Thanks. I have asked IT to help with this. They are looking into it, but so far, no answers. I assume we're all on the same network switch / router as we all sit pretty close, but I will ask. It's a large corporation with many locations, but we only have 100 people at this location. We're all on one floor of this building. I would assume we would be on same network here. Then again, I am not a network guru.
Go to the top of the page
 
RAZMaddaz
post Jun 15 2016, 08:12 AM
Post#4


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


QUOTE
I have the Access backend on the server and Access frontends are distributed to users desktops. When I test it, it's lightning fast. As soon as only one other person opens the database, even if they are just on the main switchboard and not opening any other forms,


Hi Traci!!!!

I have a few suggestions and hopefully some others can assist with this too!!!

  • Have you run "Compact and Repair" on the backend where all the data is saved?
  • I know you said you have Frontends and Backends, so just to confirm, therefore the Queries, Forms and Reports are all on the each User's Frontend/Computer?
  • Do you have List Boxes or Combo Boxes in the Fields of the Tables?

Thanks!

RAZMaddaz
Go to the top of the page
 
treymarie
post Jun 15 2016, 08:37 AM
Post#5



Posts: 149
Joined: 21-June 02
From: Maryland


Hey RAZ,

Yes, backend has been compacted multiple times. And this last time we tested it ... I started with a brand new backend file and imported all objects into it, along with relationships. So, it's a very fresh file.

Yes ... only tables are in the backend file located on the server. All other objects ... queries, forms, reports, macros, modules are in the frontend file (along with linked tables from backend), which is copied down to a folder on everyone's C drive via a batch file.

Funny you should ask ... I originally had list boxes and combo boxes as part of the table design. But once I had my forms all built, I went back and removed all those from the backend tables and relinked them from scratch. I was thinking it would improve performance. frown.gif So now, those fields are just simply numeric ID fields / long integers that just show the ID and not the text if you look at the table (nothing on the 'Lookup' tab). I only have the combo boxes defined on the forms.
Go to the top of the page
 
treymarie
post Jun 15 2016, 08:43 AM
Post#6



Posts: 149
Joined: 21-June 02
From: Maryland


BitBanger ... Looks like I got an answer to your question back from IT. This is what they said when I asked if we were on different networks / routers (copied and pasted from the email to me) ...

"I believe there are 3 user subnets in [your location] and servers are on a different subnet from those, can’t speak much more to it though."

Then he went on to say that I could contact the group in charge of the network to confirm / find out more. Could this be the problem??

They are also going to have me test the database on a different server here. I am going to try it, but I'm not optimistic. frown.gif
Go to the top of the page
 
RAZMaddaz
post Jun 15 2016, 08:53 AM
Post#7


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


Traci,

I don't know much about subnetworks, but the IT said that the database was separated on them, then maybe that is the answer.

Do you have a place on the server where you can place files, where only you have access to the data? If so, then I would make a small copy of the database, that simply does not have all the data of all the Tables, and then try the backend/frontend there and see what happens?

RAZMaddaz
Go to the top of the page
 
BitBangerUSA
post Jun 15 2016, 12:10 PM
Post#8



Posts: 45
Joined: 20-March 16



this is looking more and more like a networking issue, rather than an Access issue.

hopefully, IT will be able to assist. if you are on a different subnet than the users/machines with problems, that can explain it.
you might be able to check by copying a sizable file down from whatever server your Access back-end is on to both your machine and the problematic user(s) and see (eyeball) how that goes....
Go to the top of the page
 
treymarie
post Jun 15 2016, 02:21 PM
Post#9



Posts: 149
Joined: 21-June 02
From: Maryland


Thanks for your help everyone. IT said they have tried something on the server and are going to reboot it tonight to hopefully help performance. I guess they changed something. I have also gotten a few suggestions from other boards on thing to try on the front end. I will test again Friday and keep you posted.
Go to the top of the page
 
gemmathehusky
post Jun 15 2016, 05:39 PM
Post#10


UtterAccess VIP
Posts: 4,432
Joined: 5-June 07
From: UK


Slowness with multi user apps is often caused by the absence of a persistent connection. Do you have a persistent connection?
Go to the top of the page
 
tina t
post Jun 15 2016, 10:25 PM
Post#11



Posts: 5,197
Joined: 11-November 10
From: SoCal, USA


hi Dave, fyi from the op's first post:

I have done the following:

• Create a permanent connection to backend by having main switchboard based on small table with one record.


hth
tina
Go to the top of the page
 
treymarie
post Jun 20 2016, 12:28 PM
Post#12



Posts: 149
Joined: 21-June 02
From: Maryland


Update on this. The server changes IT made did not work. We’ve done a lot of testing Friday and today with the database, trying to pinpoint the problem. The network slowness is especially noticeable when moving from record to record. So, we decided to do some speed testing based on that. Here is the testing we have done and some interesting results:

Test 1
1) User 1 opens database and opens record on main form. User 1 navigates to another record. Time to navigate between records: 1-2 seconds.
2) While User 1 is still in database, User 2 opens database and opens record on main form. User 2 navigates to another record. Time to navigate between records: 1-2 seconds.
3) While both users are in database, User 1 then navigates to a different record. Time to navigate between records: 6-8 seconds.
4) User 2 continues to have 1-2 second navigation time, while User 1 is slow.
5) User 2 then exits the database. User 1 navigation time is still 6-8 seconds. User 1 exits the system and gets back in. Navigation speed for User 1 improves to 1-2 seconds.

Test 2
Repeat steps 1-4 above, but step 5 is ... User 1 exits the system and immediately gets back in. Navigation speed for User 1 improves to 1-2 seconds. Navigation speed for User 2 immediately slows to 6-8 seconds.

Test 3
We repeated Test 1 above, except in step 2 … User 2 opens database, but stays on main menu (which is based on a small table) and does not open any forms or reports. Same exact results.

Test 4
We repeated Test 1 one more time, except in step 2 … User 2 opens the backend file manually on the server, but does not open any tables. Same exact results.

So ... the users don’t even have to be on the same form or even asking for data, as Tests 3 and 4 show. Just opening the backend file will cause the first user to slow immediately.

We also these tests with three users. Whoever opens the database last is fast the first two users immediately slow down. Any user that closes and reopens (therefore becoming the last user) will be fast and slow everyone else down. And when the very first user get slowed down, even if all other users get out of the system, it is still slow for that first user. That implies it’s still holding or locking something, even though those users have exited. Only after that first user closes and re-opens will it become fast again.

I also tried doing these tests logged on as me, but in two different locations (my desk and a conference room), just to see if the Windows login had anything to do with it. The results were the same. The machine that opened the database first immediately slowed down as soon as the second machine opened the database.

I also did a multi-instance test on my machine, where I opened two separate frontend files on my machine, connecting to the same backend. Interestingly, both instances are very fast no matter what I do. I confirmed it was running two separate instances of Access thru the task manager. So, it seems it’s slower with different machine locations. The login makes no difference.

I have never seen behavior like this. Any ideas as to what is causing this?? Is this a server or network setting? Someone here mentioned it may be a 32-bit / 64-bit issue (Office is 32-bit but Server is 64-bit). Any thoughts on that?
Go to the top of the page
 
BitBangerUSA
post Jun 20 2016, 07:53 PM
Post#13



Posts: 45
Joined: 20-March 16



the two tests you did with you own login (desk and conference room) and (2 instances from your own machine) seem to point toward something with network sub-nets.
that assumes that your machine and the conference room are in fact on different sub-nets.

could IT help with info about where the test/conference room/your desk machine are on the network?

you can get this info yourself (if not stepping on IT's toes) with a command prompt using ipconfig.

if that can be determined, perhaps test again with 'same sub-net' machines vs 'non same sub-net' machines.

i surely hope this is helpful and not leading you down the wrong path... and others with more experience might chime in.
Go to the top of the page
 
treymarie
post Jun 21 2016, 08:41 AM
Post#14



Posts: 149
Joined: 21-June 02
From: Maryland


BitBanger ... yep, we talked about that earlier on the post. Thinking the multiple subnets could be the issue. I plan to get in touch with someone from the networking group today. I'll let you know. Thanks.
Go to the top of the page
 
treymarie
post Jul 1 2016, 08:50 AM
Post#15



Posts: 149
Joined: 21-June 02
From: Maryland


Finally found the problem!! I have four unbound boxes on that tab that have their control sources all set to complex DLookup functions. I forgot I had these as DLookups, which from what I read, can really slow the system down! I changed them everywhere but here. If I set all four to null, it is lightning fast with two users. Can't believe it. They refresh each time the user navigates to a different record, as the data in those boxes changes, so that's obviously what is slowing it down. I guess it had a harder time pulling this data when someone else has the table open??

So ... what I ended up doing is I moved those four boxes into a subform and then put that subform on the main form and is is linked by the main ID. I originally just based that subform on a query (which was based on another query), but that still seemed somewhat slow. So, now I have it where I do a make-table query (from those queries to get the data) and it created temporary / local table. It only runs the make-table when the user clicks on the tab the subform is on (or when they go to another main record and the tab is still active). You would think that would be slow, having to run the make-table all the time, but it is lightning fast! So for now, that's what we're going with. I did multi-user testing yesterday with four users and everyone was fast! I was very happy with the performance ... finally!

I am thinking the reason it is slow with doing queries directly on the table is because that table is somewhat large ... over 80k records and I have some expressions in the queries. It only needs to analyze about 100 records (those related to the main table) to get what it needs for the four boxes. So, when I do the make-tables, it only pulls those 100 records down and then does the expressions / functions on that small local table.

But I'm open to suggestions on doing it cleaner / better.

Thanks to everyone for their help!
Go to the top of the page
 
BitBangerUSA
post Jul 1 2016, 12:24 PM
Post#16



Posts: 45
Joined: 20-March 16



well... so much for it being a network issue.

glad you nailed the problem and hope you still have some hair left.
Go to the top of the page
 
tina t
post Jul 1 2016, 10:51 PM
Post#17



Posts: 5,197
Joined: 11-November 10
From: SoCal, USA


QUOTE
But I'm open to suggestions on doing it cleaner / better.

you need to be aware that repeatedly creating, populating, and deleting a table object in your database is going to lead to bloat. there are a number of ways to handle that but, assuming that your database is properly split - tables in a backend db on the server, all other objects in a frontend db with the linked in from the server backend and a copy of the frontend on each user's hard drive - the easiest way might be to just replace the user copies periodically. how often depends on how quickly the size of the frontend dbs gets out of hand.

as for your query issues, well, i'm not a query sage. but, given your description of the issue in your last post, i'm wondering if you tried writing a query to pull the target records, and nothing else. then use that query as the base for a second query, where you perform the calculations and etc that you mentioned. a subquery should do the same thing, but those tend to confuse me when i try to implement them, so i won't try to explain that one.

hth
tina
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 08:38 PM